Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

If you are working with Oracle Applications, here's how you can initialize your session in whatever tool you are using to mimic the login process and pick up profile option values. The key profile option here is usually org_id so you can select from organization aware views, but it applied equally to other profile options, i.e. you can then use

fnd_profile.value('PROFILE_OPTION_NAME');

to get values from profile options. You need to be logged into the database as the APPS user. The examples set up the session for SYSADMIN user, System Administrator responsibility. e.g. SQL*Plus

exec fnd_global.apps_initialize(0,20420,1);

e.g. for PL/SQL, TOAD, SQLDeveloper, SQL Navigator etc.:

begin fnd_global.apps_initialize(0,20420,1); end;

The parameters used here are:

1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID

To get these you have a couple of choices a) SQL - Replace SYSADMIN and System Administrator with your user and responsibility:

select 'begin fnd_global.apps_initialize(' ||
       fu.user_id || ',' ||
       fr.responsibility_id || ',' ||
       fr.application_id || '); end;' || chr(10) || '/'
from   fnd_user fu
,      fnd_responsibility_tl fr
where  fu.user_name = 'SYSADMIN'
and    fr.responsibility_name = 'System Administrator';

b) In your Oracle Applications forms session. Login as your user and navigate to the required responsibility. Open a function that uses Oracle forms Go to Help > Diagnostics > Examine In the Block enter $PROFILES$ In the field enter the appropriate field name for the parameter:

1. User_ID = USER_ID
2. Responsibility_ID = RESP_ID
3. Responsibility_Application_ID = RESP_APPL_ID

Voila!

Chronological Tracing/Debugging with minimal intrusion in Oracle

Here is a simple way to provide chronological tracing, debugging or auditing of Oracle PL/SQL code, by inserting a minimally intrusive, no affect to session concurrency, non erroring code. You put calls to the procedure in your code and output will be written to file /usr/tmp/SID_debug.log if the code below is used exactly as is, where SID is your instance name. It works on Oracle 9i or higher, including 10g and Oracle XE database on Unix/Linux - change the directory as required for Windows, this directory must be included in database parameter utl_file_dir and the database needs to have been restarted if utl_file_dir is changed. To get the value utl_file_dir run the query:

select value from v$parameter where name = 'utl_file_dir';

You can call the procedure like this

mods_log('Hello, world!'); 

in PL/SQL. e.g. for database trigger or PL/SQL code

begin mods_log('Hello, world!'); end;

e.g. for SQL*Plus

exec mods_log('Hello, world!');

Here is the code to create the procedure:

create or replace procedure mods_log (pText varchar2) is
  pragma autonomous_transaction;
  vFP       utl_file.file_type;
  vFileName varchar2(20);
  vTime     varchar2(11);
begin
  select lower(instance_name) || '_debug.log'
  ,      to_char(sysdate,'MMDDHH24MISS ')
  into   vFileName
  ,      vTime
  from   v$instance;
  vFP := utl_file.fopen('/usr/tmp',vFileName,'a',6000);
  utl_file.put(vFP,vTime || pText);
  utl_file.fclose(vFP);
exception
when others then
  null; -- Do not report any errors for this procedure call
end;
/

Profile Option controlled Database SQL Trace for Oracle Applications User / Responsibility

For Oracle Applications tracing User/forms to database trace files in user_dump_dest (udump): System Admin > Profile > System At User level set profile option: Initialization SQL Statement - Custom Value:

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

Or if you also want to have the user name in the file name (replace OPERATIONS with your username):

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

Voila!

Discover what SQL a database user is running - Logon Trigger

Okay, first of the "handy techie" posts. TRACING Oracle users activity: (This is handy if you are trying to see what a third party app is doing and have no access to the code!) NB: This will log all future sessions for that user, so make sure you're the only one on the system for the schema:

sqlplus /nolog
connect / as sysdba;
create or replace trigger APPSTRACE
after logon on  apps.schema
begin   
  execute immediate 'ALTER SESSION SET SQL_TRACE TRUE';
end;
/

REM To disable once finshed:
alter trigger appstrace disable;