If you have ever an ORA-0600, or had to read a trace file, you will have seen many references to data structures beginning with the letter 'K'. These represent Oracle Kernel processes and data structures, and the documentation
for them is very poor. Orafaq has a list of them
here
A little more information is available on Metalink and on a few blogs.
The relevance to this story is that access to all of these kernel processes is through a top-level process known as the Oracle Program Interface, or OPI. Which is not available to user programs. Instead, the OPI must be called through another undocumented API known as the User Program Interface (UPI). Which again, user programs are not supposed to ever call. Instead, user programs access the UPI through a documented API such as sql*plus, or OCI calls, or ODBC, or JDBC, or the SQLLIB library.
Now, the OCI is well documented. And I used to think that it was the only way to access the UPI. Or, rather, I am certain that I would have thought that it was the only way to access the UPI if I had known about the UPI. And, in particular, I always thought that what the precompiliers did was to translate embedded SQL statements into OCI calls.
And I was wrong.
It turns out that precompiliers translate into SQLLIB calls. And SQLLIB is not another name for the OCI. Instead, these SQLLIB functions access the UPI directly, without any OCI action at all. And this explains some strange behaviour I have been seeing. For example:
Here, I was just trying to use the CALL statement to execute a stored procedure. Of course, if I had been able to properly compile the program (for reasons too complex to list, I was unable to connect to the database for the compilation, so object validity could not be done) this would probably have had a different ending. But
I was getting this error:
ORA-00942: table or view does not exist
So, after checking my spelling, I rechecked all grants, and was mystified. Finally, I came up with a way to invoke the stored procedure using sql*plus (if it had been trivial I would not be in this spot) and found
ERROR at line 2: ORA-06550: line 2, column 6: PLS-00201: identifier 'PACKAGE.PROCEDURE' must be declared ORA-06550: line 2, column 6: PL/SQL: Statement ignoredAnd after I granted execute on the procedure to the Id doing the execution all was well.
We know that the DBA_TAB_PRIVS includes stored procedures as if they were tables. Perhaps SQLLIB does something similar. Would love to be able to be more definite, but, this is undocumented territory.
Here is another case where SQLLIB based clients have a different experience than OCI based clients. Consider stored procedures with OUT or IN/OUT parameters, and what happens when the procedure terminates with an unhandled exception. The PL/SQL documentation states for both kinds of parameters:
If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parametersIn this case, it is supposed to assign a NULL to an OUT parameter. What happens to an IN/OUT parameter is not clearly stated.
So, to see what happens with Sql*Plus, I wrote a simple(minded) stored procedure:
-----------Case 1-------------------
CREATE OR REPLACE PROCEDURE JUNK(P_EMPNO IN OUT NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
P_EMPNO := 42;
P_ENAME := 'DODAH';
RAISE TOO_MANY_ROWS;
EXCEPTION
WHEN OTHERS THEN
P_EMPNO := 4242;
P_ENAME := 'DDT';
END;
/
In other words, do something to those parameters, raise an exception, and handle it. I wrote three variations on this theme: --------Case Two-------
CREATE OR REPLACE PROCEDURE JUNK
(P_EMPNO IN OUT NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
P_EMPNO := 42;
P_ENAME := 'DODAH';
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS THEN
P_EMPNO := 4242;
P_ENAME := 'DDT';
END;
/
--------Case Three-------
CREATE OR REPLACE PROCEDURE JUNK
(P_EMPNO IN OUT NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
P_EMPNO := 42;
P_ENAME := 'DODAH';
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS THEN
P_EMPNO := 4242;
P_ENAME := 'DDT';
RAISE;
END;
/
--------Case Four-------
CREATE OR REPLACE PROCEDURE JUNK
(P_EMPNO IN OUT NUMBER,
P_ENAME OUT VARCHAR2)
IS
BEGIN
P_EMPNO := 42;
P_ENAME := 'DODAH';
RAISE TOO_MANY_ROWS;
EXCEPTION
WHEN OTHERS THEN
P_EMPNO := 4242;
P_ENAME := 'DDT';
RAISE;
END;
/
Two different exceptions, handled and unhandled. I tested them with this bit of clever (?) code: declare
v_ename varchar2(10);
v_empno number;
begin
v_empno := 4;
v_ename := 'FOUR';
junk(v_empno, v_ename);
dbms_output.put_line('For '||v_empno||' the name is
'||v_ename);
exception
when others then
dbms_output.put_line(sqlerrm);
dbms_output.put_line('For '||v_empno||' the name is
'||v_ename);
end;
/
[blockquote]Yes, normally it is crazy to store anything in a variable which is going to be passed as an actual OUT parameter. But, here I am trying to distinguish between Oracle returning a NULL and Oracle leaving the parameter unchanged.
[/blockquote]
I'm going to interject here that the NO_DATA_FOUND exception was deliberate. This was a routine which was going to be called to scroll up and down a screen. The program it was going to replace was looking for NO_DATA_FOUND. Consequently, I wanted to continue to return this exception at end-of-screen.
To continue, I found that for cases 1 and 2, the values of p_empno and p_ename were 4242 and DDT respectively, and there was no exception reported (the pl/sql block completed without error). Which is just what I had anticipated; upon return from the stored procedure, these parameters contained the values which the procedure had assigned to them, and the exception was not reraised. In cases 3 and 4, the re-raised exception was returned to the calling block and was reported back to the command line. p_empno had a value of 4 (which was what the calling block had put there) and p_ename was 'FOUR'. In other words, even though the stored procedure had assigned values to those parameters, raising the exception wiped out those values. To further confirm this, I reran the test with my pl/sql block not assigning anything to ename and empno. When I did this, the values returned were null.
Still, just what I had anticipated after I RFTM'd. Oracle only populates variables passed as OUT parameters if the subroutine completes without raising any exception, and whatever was there in the first place is still there. In other words, if you insist on using an OUT parameter as if it were an IN/OUT parameter, you can't conclude that a non-null return means the procedure completed without error.
Next, I ran those cases through a program with embedded sql and null indicators. Everyone always includes null indicators for their Pro* programs, right?. The results for cases 1, 2, and 4 were identical to those with the pl/sql test harness. In case 3, where no_data_found is being raised, p_empno was returned with a value of 42, p_empname had a value of 'FOUR', and no exception was reported. Which was most unexpected, as I had clearly raised an exception, and the return from the parameters suggested that p_empno had never seen the exception handler.
But wait, there's more!. I re-ran these cases without using any null indicators. Cases 1,2, and 4 were unchanged. Case 3 returned the same parameter values, but this
time the error number was -1405: fetched column value is NULL.
Wild Guess: OCI and SQLLIB have some control as to what goes back to the client. In the case of precompilier applications, NO_DATA_FOUND is not an error in the same way as other errors. Hence, SQLLIB wants to return (at least to IN/OUT parameters) the state when the error was raised. Moreover (and this is the surprising part) it still wants to inform that a null is being returned. A null with no null indicator raises a -1405. So ... two errors need to be returned: NO_DATA_FOUND and -1405. And the -1405 takes priority.
No comments:
Post a Comment