Lucian Lazar

Tracing procedure

Does it happen to you guys to be approached by Java developers and asked to trace some session and then have to track the session and the trace file on the db server? Well, I got tired of being driven away from my things for this and wrote a simple framework that starts the tracing and displays the content of the trace file as an output parameter in the guy’s sql window.

Let’s see how it works in a practical case: the developer comes and says he has two db connections open, a Java one and a SQL Developer one, and he would want the Java one traced so he can check some parameters. Okay. You tell him to call in SQL Developer a start_tracing procedure, do his work and then a show_trace_file procedure and voilla, he’ll have the content of the trace file printed:

system.start_tracing(pi_schema_name => 'DEVELOPER_SCHEMA', pi_program => 1, po_trace_cursor => po_trace_cursor);

system.show_trace_file(pi_schema_name => 'DEVELOPER_SCHEMA', pi_program => 1, po_trace_cursor => po_trace_cursor);

I added the pi_program parameter in order to allow the developer to catch different sessions run under his user using different programs. These are the programs supported:

1 = java.exe
2 = JDBC Thin Client
3 = SQL Developer version 2.0 or above
4 = SQL Developer version 1.5.5
5 = SQL Developer version 1.5
7 = Toad
8 = PL/SQL Developer
9 = sqlplus

The code behind this framework is very simple:
- a directory mapped to the folder where Oracle keeps the trace files, in 11g this is [orahome]\base\diag\rdbms\[servicename]\[servicename]\trace
- a type and a pipeline function used to parse the trace files
- the start_tracing procedure used to track the session and start tracing
- the show_trace_file procedure used to display the content of the trace file

The script that creates the objects can be downloaded from here.

Possible bug in Oracle 11.2.0.2

We encountered today a possible Oracle bug which only occurs in a very specific situation: a subquery returning one row used further in a subcorrelated subquery. This is the statement (expected b = 1, returned b null):

SQL> with source as
2   (select a, b from (select 'a' a, 1 b from dual)),
3  result as
4   (select x.*
5      from source x
6     where not exists (select y.b
7              from source y
8             where 1 = 2
9               and x.b = y.b))
10  select * from result;

A          B
- ———-
a

The same query works as expected if we change the first input to (select ‘a’ a, 1 b from dual union all select ‘b’ a, 2 b from dual) and also there are other workarounds:
- lose the subquery (with source as (select ‘a’ a, 1 b from dual));
- use nvl on the join condition (nvl(x.b,0) = nvl(y.b,0));
- add (and x.b is not null) in the subcorrelated subquery.

However, I offer a beer for an explication to the initial result (other than declaring it an Oracle bug).

P.S. 10.2.0.4 and 10.2.0.5 both return the expected result, the bug reproduces only on 11.2.0.2.

Generic and detailed custom Oracle exceptions

How do you handle custom exceptions in your PL/SQL code? I recently observed the practice of raising an ORA-20001 code with a custom error message every single time and hated it. Why couldn’t we have a documented repository with all these exceptions? Why couldn’t we have some standardized and generic messages to fit to different scenarios? Let’s see a simple solution to obtain that.

First, a package holding all custom exceptions with codes starting from ORA-20001 and parametrized messages:

e_UserAlreadyHasRole exception;
e_UserAlreadyHasRole constant integer := -20001;
e_UserAlreadyHasRole constant varchar2(1000) := 'User %1 is currently %2 and cannot get a new role without deleting or updating the old one.';

Second, raising an exception using a custom function to display the message:

exception
when exceptions.e_UserAlreadyHasRole then
raise_application_error(exceptions.e_UserAlreadyHasRole_code,
exceptions.error_text(exceptions.e_UserAlreadyHasRole_msg, v_user_name || ';' || v_role_name));

And let’s see the final result. Instead of:

The user has currently a role and cannot get a new one without deleting or updating the old one.

we have:

User POPESCU is currently MANAGER and cannot get a new role without deleting or updating the old one.

Better, right?

Oh, and here is the function that parametrizes the error messages:

function error_text(pi_error_msg in varchar2, pi_string in varchar2) return varchar2 is
l_percents integer;
l_commas integer;
l_error_msg varchar2(1000);
l_value varchar2(1000);
l_percent varchar2(1) := '%';
l_separator varchar2(1) := ';';
l_idx pls_integer := 1;
begin
l_error_msg := pi_error_msg;
l_percents := regexp_count(pi_error_msg, l_percent);
l_commas := regexp_count(pi_string, l_separator);
if l_percents = l_commas + 1 then
if l_commas >= 0 then
for i in 1 .. l_commas + 1 loop
l_value := regexp_substr(pi_string, '[^' || l_separator || ']+', 1, l_idx);
l_error_msg := replace(l_error_msg, '%' || l_idx, l_value);
l_idx := l_idx + 1;
end loop;
end if;
else
l_error_msg := l_error_msg || ' (internal note: please set the correct exception parameters)';
end if;
return l_error_msg;
end error_text;

ID and List to rows

I had an interesting situation today needing to transform two columns – a user id and a list of roles – in rows, each containing a user id and one element from the list (one role id).

Of course, I could have made my own custom function using INSTR and SUBSTR but I wanted to use a simpler solution. This is what I came up with, using XMLTABLE:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 15 18:48:04 2011

ID         ROLE
---------- -------------------------------------------
1          role1
1          role2
1          role3
2          role1

Milano