Using Profile Option 'Initialization SQL Statement - Custom'

 'Initialization SQL Statement - Custom' (FND_INIT_SQL) profile option

---------------------------------------------------------------------

Using the profile option 'Initialization SQL Statement - Custom', one can add site-specific initialization code, such as optimizer settings. This profile value must be a valid SQL statement, or a PL/SQL block for more than one statement, that is to be executed once at the startup of every database session.The code is executed by FND_GLOBAL.INITIALIZE and APPS_INITIALIZE immediately after initializing global variables, profiles, and the contents of client_infoon session startup.

Query this to check the location of Trace file:

select * from V$PARAMETER

WHERE NAME LIKE 'user_dump_dest'

The order of execution is:

- FND_GLOBAL values initialized

- Profiles initialized

- CLIENT_INFO contents initialized

- FND_APPS_INIT_SQL initialization code called (if a value is defined)

- FND_INIT_SQL initialization code called (if a value is defined)

This means that in most cases (eg. tracing a form) the profile option setting
will be effective after signing-on again.

The profile option can be set at different levels like user/responsibility/
/application or site.

You can specify any valid SQL statement or a PL/SQL block for this profile
value, but the best way is using the fnd_sess_ctl procedure of fnd_ctl package.

The value of this profile option can be set using fnd_sess_ctl as follows:

 

BEGIN
FND_CTL.FND_SESS_CTL(,,,,,);
END;

 

The different parameters are (all within single quotes):


-> This sets the Optimizer_mode for online users. The valid values
are RULE/CHOOSE/FIRST_ROWS/ALL_ROWS.
It is recommended for CBO you set it to FIRST_ROWS.


-> This sets the Optimizer_mode for conc. jobs. The valid values
are RULE/CHOOSE/FIRST_ROWS/ALL_ROWS .
It is recommeneded for CBO you set it to ALL_ROWS.


-> This sets the trace option to true or false. Valid values
are TRUE/FALSE. If set to true then you will be able to
generate the trace files for the session.


-> This sets the times_statistics for tracing to true or false.
Valid values are TRUE/FALSE.


-> Reserved for logging. Logs session information in FND_TRACE_LOG.
Recommended value is '', other possible value is LOG.


-> This can be set for different events. An example could be to set
an event point in order to get the values of the bind values in
the trace file. In that case the example setting is as follows.
'ALTER SESSION SET EVENTS =
'||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4 '||'''' .

Please make sure all the quotes are correct.

Examples:

1) To enable SQL trace for the session, set the profile option as follows:

BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','',''); END;

Result of the above setting will be the execution of the following two alter session
commands at database session startup:

ALTER SESSION SET SQL_TRACE = 'TRUE'
ALTER SESSION SET TIMED_STATISTICS = 'TRUE'


2) To put an event statement for getting bind variables in the trace file set the profile option as follows

BEGIN FND_CTL.FND_SESS_CTL('','','','','','ALTER SESSION SET EVENTS
='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;

3) To set the logging on, set the profile options (Initialization SQL statement - custom) as follows:

BEGIN FND_CTL.FND_SESS_CTL('','','','','LOG',''); END;

For this option the log table is FND_TRACE_LOG, This table should be purged periodically.

If this LOG mode is set then then following information will be inserted in
FND_TRACE_LOG table (every time when fnd_global.initialize procedure is called):

user_name,
sysdate,
conc_request_id,
oltp_opt_mode, (optimizer mode for online transactions)
conc_opt_mode, (optimizer mode for concurrent programs)
conc_program_name,
user_conc_program_name,
resp_name,
application_short_name


4) Trace can be enabled with trace identifier in order to specify a file name
(Here: IDENTIFIER)
Example:

BEGIN FND_CTL.FND_SESS_CTL ('','','TRUE','TRUE','LOG',
'ALTER SESSION SET TRACEFILE_IDENTIFIER =''IDENTIFIER'
' EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12''');
END;

<span style="font-family: Courier New,Courier,mono;" data-mce-style="font-family: Courier New,Courier,mono;">WARNINGS</span>

1) Do not ever set profile option 'Initialization SQL Statement - Oracle' instead of 'Initialization SQL Statement - Custom'.'Initialization SQL Statement - Oracle' (FND_APPS_INIT_SQL) profile option issued by Oracle Applications to add application-specific initialization code.This profile option is set at the application level only, and will only be executed for responsibilities owned by that application. This profile option and its value settings are delivered as seed data, and must not be modified.2) Setting of profile option 'Initialization SQL Statement - Custom' can effect on the operation of the whole Applications. As far as possible set this profile option only on USER level.Syntax error can cause that users can not login to the Applications or cannot run any concurrent programs. Please make sure syntax is correct.If you set this profile option on site level and a syntax error is in the profile value then it would cause that nobody can login to the Applications.In this situation the error message is something similar:ORA-20001: Oracle error -20001: ORA-20001: -: While executing SQL in profile FND_INIT_SQL:BEGIN FND_CTL.FND_SESS_CTL('RUL','','','','LOG','')has been detected in FND_GLOBAL.INITIALIZE.If you cannot login to Applications with any apps user then you can correct this in SQL*Plus by selecting and updating the profile_option_value:

SELECT val.level_id, val.level_value, val.profile_option_id,
val.profile_option_value
FROM fnd_profile_options opt, fnd_profile_option_values val
WHERE opt.profile_option_name = 'FND_INIT_SQL' AND
opt.profile_option_id = val.profile_option_id;

Either correct the syntax error or set the profile_option_value
to NULL and then define it in the Applications again (preferred way):

UPDATE fnd_profile_option_values
SET profile_option_value = NULL
WHERE profile_option_id = AND
level_id = AND
level_value = ;

Diagnostics & Utilities Community:

How to enable and retrieve FND debug log messages

 

SOLUTION

1) Set up profiles for the User / Responsibility to be used to reproduce the issue

Responsibility: System Administrator
Navigation: Profile > System

Query up the Application, Responsibility and User you will use to reproduce the issue you want to debug.
For example:
Application = Receivables
Responsibility = Receivables Manager
User = MYUSER1
Profile = FNDÞbug%

then set the profiles as shown below:

Profile Name	Suggested value	Comments
FND: Debug Log Enabled	YES	This turns the debugging feature on
FND: Debug Log Filename  or FND: Debug Log Filename for Middle-Tier	leave this blank/null at all levels	Use when you want debug messages to get stored to a file. Note however, that the preferred method of downloading debug messages to a .xls file is provided in step 5 below.
FND: Debug Log Level	Statement	Following are options listed from least to most detailed debugging : Unexpected, Error, Exception, Event, Procedure, Statement
FND: Debug Log Module	%	Indicate what modules to debug. You can use something like 'ar%' or even  '%arp_rounding%' to limit modules debugged

Following are examples on how you would set the above profiles depending on what you want to debug :

sample setting to debug everything :

FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module %

sample setting to debug ONLY Receivables :

FND: Debug Log Enabled YES
FND: Debug Log Filename NULL
FND: Debug Log Level STATEMENT
FND: Debug Log Module ar%

2) Since the debugging routine will start writing messages to the table, we want to know which messages pertain to our test.
    a) If you are tracking the debug messages for a concurrent request, take note of the Concurrent Request id

    b) Otherwise, retrieve the current max value of log sequence as follows:

SELECT MAX(LOG_SEQUENCE) before_seq
FROM FND_LOG_MESSAGES;

3) Run your test case, try to stay on track in reproducing the issue and leave out extraneous steps so that you don't end up with debug messages that are not relevant to your issue. It is ideal to not have anyone else using the Responsibility you have enabled debug for, so that only messages pertaining to your testcase are picked up.

4) If you ran the script above to get the log_sequence, do it again now, after you have completed the steps to reproduce the issue:

SELECT MAX(LOG_SEQUENCE) after_seq
FROM FND_LOG_MESSAGES;

5) For ease of review by Development, spool the output of the following to a .xls spreadsheet :

You have the option to retrieve the columns you would like to see from FND_LOG_MESSAGES.

If you want to retrieve only a few columns to keep the spreadsheet small, you can do the following:

a) Retrieve the debug messages for a concurrent request ID:

SELECT log.module, log.message_text message
FROM fnd_log_messages log,
     fnd_log_transaction_context con
WHERE con.transaction_id = &request_id
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id
ORDER BY log.log_sequence; 

b) Otherwise, retrieve debug messages using a log_sequence range:

SELECT module, message_text
FROM fnd_log_messages
WHERE log_sequence between &before_seq and &after_seq
ORDER BY log_sequence;

If you want to retrieve all the columns for detailed debugging and troubleshooting, you can do the following:

a) Retrieve the debug messages for a concurrent request ID:

SELECT log.*
FROM fnd_log_messages log,
     fnd_log_transaction_context con
WHERE con.transaction_id = &request_id
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id
ORDER BY log.log_sequence;

b) Otherwise, retrieve debug messages using a log_sequence range:

SELECT *
FROM fnd_log_messages
WHERE log_sequence between &before_seq and &after_seq
ORDER BY log_sequence;

 

6) Don't forget to turn OFF debugging, otherwise all your actions will be logged and this could impact performance of the application.

FND: Debug Log Enabled = NO 

FNG Debug messages does not write in to FND_LOG_MESSAGES

 

CAUSE

The issue addressed in the following Bug:

Bug 20632815 SETTINGS FND: DEBUG OPTIONS DOES NOT WRITE TO FND_LOG_MESSAGES

When the sequence "FND_LOG_MESSAGES_S" reaches its maximum, no message is logged in FND_LOG_MESSAGES.

When "FND_LOG_MESSAGES_S" reaches its maximum, you need to cycle through the sequence to remove rows from FND_LOG_MESSAGES.

SOLUTION

To resolve this issue, test the following steps on your development instance and move to appropriate environment if necessary (Note: for 11i, go directly to step 4):

1. Download Patch 20632815 along with any required prerequisite patches.

2. Ensure a valid backup exists before applying the recommended patch.

3. Apply the patch for your version along with any required prerequisite patches per the readme file instructions.

4. Follow the steps below to purge FND_LOG_MESSAGES:

   A. verify that CYCLE_FLAG is Y for the sequence FND_LOG_MESSAGES_S.

   select CYCLE_FLAG from dba_sequences where SEQUENCE_NAME = 'FND_LOG_MESSAGES_S';

   B. verify that there is a new executable defined 'FNDLOGPUR'

   Navigation: System Administrator -> Concurrent -> Program -> Executable  and Query for 'FNDLOGPUR'

   C. Verify that there is a new Concurrent Program defined 'Purge FND Logs' (short name FNDLOGPUR)

   Navigation: System Administrator -> Concurrent -> Program -> Define and Query for 'Purge FND Logs'

   D. Verify that newly created CP is available for SYSADMIN user to be run.

   Navigation: System Administrator -> Requests -> Run . Run 'Purge FND Logs'.

   Concurrent Program 'Purge FND Logs' is used to purge rows from FND_LOG_MESSAGES. It takes one optional parameter - 'Till Date'.
   This program deletes all the rows from FND_LOG_MESSAGES with TIMESTAMP value < 'Till Date'. If Till date is not entered, it is defaulted to sysdate-365.

5. Retest the Fnd Debug log functionality and confirm the messages now appear as expected.

Dr Edgar F Codd Computer scientist

What is a RDBMS (Relational Database Management System)?

RDBMS is stands for Relational Database Management System. RDBMS is the foundation for SQL and all current database management systems, including MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) based on E. F. Codd's relational model.

Relational Database Management System

In the jargon of Database Management Systems, there is a rule that is unspoken. Since there aren't many databases that follow all of E.F. Codd's rules, the unspoken rule has been getting more attention.

• A management system or piece of software is considered to be a Database Management System if it complies with any one of the five or six rules set by E.F. Codd (DBMS).
• If a management system or programme adheres to any of E.F. Codd's seven to nine recommended guidelines, it qualifies as a semi-relational database management system (semi- RDBMS).
• In order to be considered a full Relational Database Management System, a management system or software must comply with all 9–12 of E.F. Codd's suggested rules (RDBMS).

Here is the list of Codd's Rules.

Rule 0 − Foundation rule

Any relational database management system (RDBMS) that is proposed or argued to be an RDBMS should be able to handle all of the stored data using its relational features.

Rule 1 − Rule of Information

Relational databases should store data as relationships. In Relational Database Management Systems, tables are what are called "relations." It is important to store the value as an entity in the table cells, whether it is user-defined data or meta-data.

Rule 2 − Rule of Guaranteed Access

It is against the rules to use pointers to get to data logically. Every atomic data entity should be accessed in a logical way by using the table name, the primary key represented by a specific row value, and the column name represented by an attribute value.

Rule 3 − Rule of Systematic Null Value Support

Relational databases do not have any problems with null values. They should always be thought of as "missing information." Null values don't depend on the type of data. You shouldn't mix them up with blanks, 0s, or empty strings. Null values can also be seen as "data that doesn't apply" or "information that isn't known."

Rule 4 − Rule of Active and online relational Catalog

Metadata is the data about the database or the data about the database in the language of Database Management Systems. "Data dictionary" is the name of the live online catalogue that stores the metadata. The so-called "data dictionary" can only be accessed by authored users with the right permissions, and the same query languages used to get to the database should be used to get to the data in the "data dictionary."

Rule 5 − Rule of Comprehensive Data Sub-language

Integrity constraints, views, data manipulations, transactions, and authorizations should all be able to be written in a single, strong language.

Rule 6 − Rule of Updating Views

Views should reflect the updates of their respective base tables and vice versa. A view is a logical table which shows restricted data. Views generally make the data readable but not modifiable. Views help in data abstraction.

Rule 7 − Rule of Set level insertion, update and deletion

The data should be able to be retrieved, added, changed, and deleted with just one operation.

Rule 8 − Rule of Physical Data Independence

Batch and end-user operations are separated in terms of how they are stored and how they are accessed.

Rule 9 − Rule of Logical Data Independence

Batch users and end users can change the database schema without having to recreate it or the applications that use it.

Rule 10 − Rule of Integrity Independence

Integrity constraints should be kept in the data dictionary as metadata, not in the application programmes.

Rule 11 − Rule of Distribution Independence

The Manipulation of Data The language of the relational system shouldn't care about where the physical data is stored, and it shouldn't matter if the physical data is stored in one place or in many places.

Rule 12 − Rule of Non Subversion

Any row should follow the rules for security and integrity. No special privileges are applicable.

Almost all full scale DBMSs are RDMSs. Oracle implements 11+ rules and so does Sybase. FoxPro only uses 7+ rules, while SQL Server uses 11+ rules.