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;

Context and Relativity

This is a PS blog ... late but better than never ;-) I was having a conversation the other day with someone about how every difference can be measured as based on the components of context and relativity. Large/small, rich/poor and the inevitable intelligent/stupid are all measured by relativity and context. It was interesting thinking about a few instances of this, examples abound in general lifestyle measurements and "opposites", but it was more from a technology front that got me thinking about it. Thinking about it now the general progress in computer technology comes to mind. Back in the days when, say punch cards were used to program computers (yes there were those days - but I personally wasn't there to witness!) that would have been considered "complex", "advanced" and perhaps "smart", but change the context today and "modern" electronics, come up with the idea and bam - you'd probably be laughed at! In this instance the main differentiator of the context was time, and that is one "context" which we can't change for real... yet. And don't hold your breath! But there are plenty of other "contexts" that we can control that affect us hugely. The point I was making to myself was that if you can even slightly change your context, you could take advantage of the change. Be it looking at a computer program from a different perspective, or looking at whether you are truly "busy" or "tired" ... or not. In any case, its something I'll be keepin' in the back of my mind, at least in my current context!

Digital Immigrant or Digital Native?

Given my work and experience in technology, I often find that many people around me very much lack confidence when it comes to technology. I recently attended an event presented by the Unlimited Potential organization www.up.org.nz where Paul Reynolds introduced the question of people being Digital Immigrants or Digital Natives. This was an interesting concept for me to consider, especially given my 2 year old sons interest in any of my "toys" ... any time I bring out the laptop, cellphone, camera, video camera, PS2 it ain't gonna be me that ends up playing with it! Now, present a bunch of new digital gadgets for a group of adults, and I may be wrong but I'm sure you'd find a definite feeling of unease and much less compulsion to grab the stuff and have a play! This translates somewhat to what I see in business; people are very hands off, and will often asks questions and seek confirmation before trying something out. The thing I find about technology in relation to all this is that someone has to jump in and give it a go, someone has to become the expert. At the end of the day digital just means 0's and 1's and either something will work or it won't ... so give it a try! We can help our children be the digital natives by ensuring that they are immersed and confident with digital "stuff" by becoming more experienced Digital Immigrants ourselves. PS. Guys ... especially those with kids, this is an excuse to buy those toys! Just make sure you buy two ;-)

Well, this is my first blog. I thought it was about time I expanded my web presence, given my push into the Company arena and finally looking at product development. My intentions for this blog are to share some tips and tricks I find on my journeys, plus comment on events that I'm involved in that I think deserve to be shared. My company Virtuate Limited www.virtuate.com based in Wellington, New Zealand started trading in April this year, and is already gaining a reputation for delivering high quality, low cost, fast turnaround solutions. We focus on Oracle eBusiness Suite solutions - and are keen to provide best on-site and remote solutions available. If you're involved with any of the Oracle Applications / Oracle eBusiness Suite modules and have any frustrations or questions - keep an eye on our website or send us an email.