Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Wednesday, March 07, 2007

The storage representation for number zero

Yesterday I found out something interesting in an Oracle 9205 database. One column called is_mandatory is defined as NUMBER(1,0). Based on the business rule, only 0 or 1 can be stored in the column. The strange things happen to 0. There are 2 different storage representations for the number zero!
09:18:50 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory != 1;

IS_MANDATORY STORAGE
------------ ------------------------------
0 Typ=2 Len=1: 128
0 Typ=2 Len=1: 128
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1
0 Typ=2 Len=2: 193,1

9 rows selected.

Elapsed: 00:00:00.01
09:19:00 > select is_mandatory, dump(is_mandatory) storage from t where is_mandatory = 0;

IS_MANDATORY STORAGE
------------ ------------------------------
0 Typ=2 Len=1: 128
0 Typ=2 Len=1: 128

2 rows selected.

Elapsed: 00:00:00.01
In a normal case, the 0 should be always stored in Oracle like:

11:14:16 > select dump(0) from dual;

DUMP(0)
------------------------------------------------
Typ=2 Len=1: 128

Elapsed: 00:00:00.08


However, there is another storage scheme for 0 in the table
Typ=2 Len=2: 193,1
This value should be zero as well based on Oracle storage internal. However, I couldn't fetch these rows using is_mandatory = 0 since 0 will be always interpreted as 'Typ=2 Len=1: 128'. Even I couldn't reproduce this scenario simply using SQL. Originally, these values are inserted/updated by a database stored procedure which takes a set of values as parameters from a front-end Java program via JDBC thin client. I am wondering under what kind of condition 0 would be stored this strange way. It seems like 'Typ=2 Len=2: 193,1' is something between 0 and 1.

11:42:02 > select is_mandatory from t where is_mandatory > 0 and is_mandatory <>
0
0
0
0
0
0
0
7 rows selected.
Elapsed: 00:00:00.00

I raised a question about this to ASKTOM. So this post will be updated once I got the answer from Tom.

The problem is caused by the incompatible JDBC and RDBMS version. We are running an Oracle 9205 database as we are using JDBC 8174 in the middle tier to access the database. After I point to JDBC 9205 driver and re-run the program, number zero can be stored correctly with 'Len=1: 128'. According to what one said:

'Len=2: 193,1' is definitely an invalid NUMBER; in 10.2.0.3 :
SQL> select dump ( utl_raw.cast_to_number ('C101') ) from dual;

DUMP(UTL_RAW.CAST_TO_NUMBER('C101'))
--------------------------------------------------------------------------------
Typ=2 Len=2: 193,1

SQL> select 1 / utl_raw.cast_to_number ('C101') from dual;
select 1 / utl_raw.cast_to_number ('C101') from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Just an illegal bit representation that doesn't map to any NUMBER, and so produces unpredictable results (including process termination) if used.


I also did some tests by myself in Oracle SQL Developer:

select utl_raw.cast_to_number('c101') from dual

UTL_RAW.CAST_TO_NUMBER('C101')
------------------------------
0

1 rows selected

select utl_raw.cast_to_number('C101')/1 from dual

UTL_RAW.CAST_TO_NUMBER('C101')/1
--------------------------------
146150163.7229892817193583822615272918645831532875

1 rows selected


If the 2nd SQL is executed in SQL*Plus, seems like it will never end. A different result! Completely unpredictable, indeed!

Thursday, February 22, 2007

How to catch an user defined error message

Recently I am working on a project. In this project, there is a database package in which I put all the related functions and procedures. I want a function to generate user defined errors while calling it directly in the hosting environment; meanwhile, I want the calling stored procedure to ignore some of the user generated errors if the function is being called by a stored procedure. The point here is to catch some user defined errors but not the oracle predefined errors.

There is a RAISE_APPLICATION_ERROR statement in the function to generate the user defined error. e.g.

function test
return number
is
e_no_rate_found exception;
begin
raise e_no_rate_found;
exception
when e_no_rate_found then
raise_application_error(-20306, 'No rate found!');
end;

If this function is called directly from sqlplus, the user defined error -20306 will be raised. I want to ignore this error in a procedure which calls this function. Actually, it's same to catch an user defined error as to catch an oracle predefined error. Here we go. In the procedure, we could write the code block below:

declare
e_no_rate_found exception;
pragma exception_init(e_no_rate_found, -20306);
begin
call test;
exception
when e_no_rate_found then
NULL;
end;

The user defined error -20306 is associated with the user defined exception e_no_rate_found using exception_init pragma in the calling procedure.