Problem encountered (Urgent) 1

[newdbprgr]

Hi all,

due a recent update to 10g Oracle, the application doesn't seem to be working properly.

CREATE OR REPLACE PROCEDURE INS_APPL (
P_QTY IN DECIMAL,
P_NAME IN VARCHAR2)

I've always passed in String for both the variables and it has been working fine with the previous version. i.e. the Stored Procedure is able to convert the String value ("123.1234") to decimal (123.1234).

Now, pass in String is the same, but the decimal value (123).

Any comment. Any reason why in oracle 10g, it is unable to interpret it like the previous version. I can't change the Stored Procedure as it is fully owned by another company. How to work around it?

Urgently need responses.

 

[Thargy]

To see if it really is the string to number conversion, hard-code a known good value (as a number) into a call to the sp. If it fails, try it on the old version and observe any different behaviour.

If it works, then go back to supplying a string, and see what happens. If the string fails where it previously succeeded, then you know it's due to the upgrade to 10g.

I've had a look at the decimal datatype, and it may be that if no precision is specified in the datatype, a default precision of 38 and a scale of zero is used. That would effectively reduce the converted string to an integer, which could explain your problem.

Regards

T

 

[SantaMufasa]

NewDBPrgr,

We might be able to help more if we could see the code for INS_APPL. Could you please run the following code from SQL*Plus and post the results here:

select text from all_source where name = 'INS_APPL' order by line;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[newdbprgr]

I've escalated this matter to the other company, as I can't do much on my part. The code is on another server with firewall setting, unable to request to open to view source.

Thanks SantaMufsa and thargtheslayer for your reply.

I will tell them to change the datatype over at their side, which is more logical than i try to change on my end.

 

[newdbprgr]

I need to cover at least 99999999.999999 to 0.000001.

IF I didn't specify the precision of the DECIMAL, what will the default precision?

 

[SantaMufasa]

NewDBPrgr,

I believe that we need to do further investigation. I just ran the following on an Oracle 9.2.0.1.0 instance:

SQL> create table newdb (w number, x decimal, y decimal(14,6), z number(14,6));

Table created.

SQL> desc newdb
 Name                    Null?    Type
 ----------------------- -------- ----------------
 W                                NUMBER
 X                                NUMBER(38)
 Y                                NUMBER(14,6)
 Z                                NUMBER(14,6)

SQL> insert into newdb values (12345678.123456,12345678.123456,12345678.123456,12345678.123456);

1 row created.

SQL> col w format 99999999.9999999
SQL> col x like w
SQL> col y like w
SQL> col z like w

SQL> select * from newdb;

                W                 X                 Y                 Z
----------------- ----------------- ----------------- -----------------
 12345678.1234560  12345678.0000000  12345678.1234560  12345678.1234560

1 row selected.

I then ran the same code on an Oracle 11.1.0.6.0 instance (I don't have access to an Oracle 10g instance presently.):

create table newdb (w number, x decimal, y decimal(14,6), z number(14,6));

Table created.

SQL> desc newdb
 Name                    Null?    Type
 ----------------------- -------- ------------
 W                                NUMBER
 X                                NUMBER(38)
 Y                                NUMBER(14,6)
 Z                                NUMBER(14,6)

insert into newdb values (12345678.123456,12345678.123456,12345678.123456,12345678.123456);

1 row created.

col w format 99999999.9999999
col x like w
col y like w
col z like w

select * from newdb;

                W                 X                 Y                 Z
----------------- ----------------- ----------------- -----------------
 12345678.1234560  12345678.0000000  12345678.1234560  12345678.1234560

1 row selected.

Notice that the storage behaviors of Oracle 9i and 11g are identical: "NUMBER" defaults to unbounded numeric values; "DECIMAL" defaults to integer values only. (My presumption is that Oracle 10g behaves no different from Oracle 9i and 11g.)

Therefore, I believe your best option is to pass along these findings to your vendor and ask them to troubleshoot your problem, then deliver to you a new, corrected version of code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[newdbprgr]

Thanks. Case Closed. I've requested vendor to change it to NUMBER.

 

[sem]

I suppose that the source of this issue is not new Oracle version but rather different NLS environment (NLS_TERRITORY or NLS_NUMERIC_CHARACTERS).

Regards, Dima

http://seminihin.narod.ru