Stuff about Oracle

Tag: parameters

Often when you’re analysing a concurrent request via SQL, you might want to see the parameters which were used when that request was submitted. You can see basic information via the argument_text on the fnd_concurrent_requests table – for example, for a run of Gather Schema Statistics you might see something like ALL, 10, 3, NOBACKUP, , LASTRUN, GATHER, , Y.

You can see the same values in the argument1, argument2, argument3 fields on the same fnd_concurrent_requests table:

What this doesn’t tell you is the name of the parameter which the argument text relates to.

That’s useful in that we can see the parameters linked to the job definition.

The Answer

Ideally we want to be able to see the list of parameters on a job, and the values entered against them for any given request_id. The SQL below allows you to do that.

This is sample output:

There are drawbacks:

The SQL only gives the first 25 parameter values because the fnd_concurrent_requests table only holds 25 argument fields. If your request has more than 25 parameters, you might need to do something fancy by hacking up the data in the fnd_concurrent_requests.argument_text field, which contains a comma separated list of arguments, instead of getting the parameter values from the argumentn fields.

Where the parameter is linked to a record from another table, e.g. AP Invoice Number, PA Project Number, the parameter value will return the Invoice ID / Project ID etc, and not the actual Invoice Number / Project Number – unless you specifically build in joins to the related tables.

Therefore it’s a bit rough and ready, but can still be useful nonetheless.