Online Sql Trace

When you have long running processes (like payroll PUYGEN), you do not need a sql trace file to find the top sql consumers.

Some sql statements can give you the same result.

select module, sql_hash_value, s.*
from v$session s
where module = 'PUYGEN'
order by 1, 2

select sql_text, s.executions, buffer_gets, s.*
from v$sql s
where hash_value =

select *
from v$sql_plan
where hash_value =

order by id

But this set of statements will not give you the waits.

Htmldb gives pls-00103

A customer wanted to have a decentralized application, based on the positions of Oracle Hrms.

Since self-service is non-existing for positions, we had to explore other options. At the end we opted for HtmlDb.

Installation went smooth, and the workspace was created in a snap. But when creating a first test application, we had several errors, but without error messages.

At the end we were able to see the error message, together witha piece of code. pls-00103 was popping up, together with a select in a select, dynamically generated by htmldb.

We brought the problem down to:
declare
n number;
begin
select nvl( 1, (select 2 from dual))
into n
from dual;
end;

This snippet executed fine on my laptop database (9.2.0.1) ,but not on the apps database (9.2.0.5).

We were sure we had discovered a new bug in the database software, but a lucky metalink hit brought us further.

Event 10933 mimics old plsql-sql behavior, and this event was set in our upgraded oracle apps instance.

We unset the event, and voila.

Summary: oracle apps ; htmldb ; pls-00103 ; event 103999

List all request launched via a request set

This query shows all request related to a master request with timing and dependecies.

select
level,
status_code,
(
select nvl( description, concurrent_program_name)
from fnd_concurrent_programs p
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
) prog,
trunc( ( actual_completion_date - actual_start_date) * 24 * 60, 2) minuten,
request_id,
argument_text,
priority
from fnd_concurrent_requests r
where 1 = 1
start with r.request_id = :p_top_request_id
connect by prior request_id = parent_request_id
order siblings by request_id;

See more concurrent output.

It is possible to see someone other's concurrent output, when you are linked to the same responsibility the user submitted the concurrent.

You do that by setting the profile option Concurrent:Report Access Level to Responsibility instead of the default or implicit value User.

This feature can become very handy for operational people to share each others output.

the organizational payment methods api

The update api for organizational payment methods (pay_org_payment_method_api / update_org_payment_method) shows some unpredictable effects.

In order to overcome that, i had

• to pass a null value for the p_sets_of_book_id, instead of the default value.
• to pass a value for a random pmeth_information column, instead of defaulting them all (and leave them out of the api call).