Re: EXECUTE IMMEDIATE rounds up results

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 03 Mar 2007 21:17:10 +0100
Message-ID: <45E9D7C6.8080402@gmail.com>

ar123456_at_gmail.com schrieb:
> On Mar 3, 12:25 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> ar123..._at_gmail.com wrote:
>>> 1. I have a very simple stored procedure
>>> PROCEDURE test_proc(
>>>   invValue IN NUMBER,
>>>   outValue OUT NUMBER)
>>> IS
>>> ...
>>> 2. It works OK when called from another procedure.
>>> 3. However when called as dynmic sql, it rounds returned value to 2
>>> decimals
>>>   ...
>>>    v_sql := 'BEGIN test_proc(:inValue, :outValue); END;'
>>>    EXECUTE IMMEDIATE v_sql, using v_inValue, out v_outValue;
>>>    v_outValue is always rounded to two decimal values, e.g. if real
>>> value is 187, it will be 190, if the real value is 123456, it will be
>>> 120000.
>>> It is Oracle 9i and I could not find any information on the
>>> topic??????
>>> Tnanks,
>> Post a working example that demonstrates the issue.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> See details below. While preparing this example I found that the real
> culprit is TIMESTAMP parameter, if it is not there everything works
> fine, if it is there we have this funny effect.
>
> ============================================================================================
>
> 1. Create a table
>
> CREATE TABLE TEST1
> (
> ID NUMBER(10) NOT NULL,
> START_DATE TIMESTAMP(6) WITH TIME ZONE
> )
>
> 2. Create procedures
>
> CREATE OR REPLACE PROCEDURE gen_number(
> p_id IN NUMBER,
> p_effective_date IN test1.start_date%TYPE,
> p_number OUT NUMBER)
> IS
>
> BEGIN
>
> p_number := p_id;
>
>
> END gen_number;
> /
>
> CREATE OR REPLACE PROCEDURE test_number(
> p_id IN NUMBER,
> p_effective_date IN test1.start_date%TYPE,
> p_number OUT NUMBER)
> IS
>
> v_sql VARCHAR2(3000);
>
> BEGIN
>
> v_sql := 'BEGIN gen_number(:p_id, :p_effective_date, :p_number);
> END;';
> dbms_output.put_line(v_sql);
>
> EXECUTE IMMEDIATE v_sql using p_id, p_effective_date, OUT p_number;
>
> END test_number;
> /
>
> 3. Execute
> declare x number;
> begin
> test_number(77, sysdate, x);
> dbms_output.put_line(x);
>
> test_number(101, sysdate, x);
> dbms_output.put_line(x);
>
> test_number(109, sysdate, x);
> dbms_output.put_line(x);
>
> end;
>
> 4. Results
>
> BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
> 77
> BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
> 100
> BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
> 110
>
>
>
>

Can't reproduce ( just a shout in the dark - are you using sqlplus for your tests?)

SQL> declare x number;

   2 begin

   3          test_number(77, sysdate, x);
   4          dbms_output.put_line(x);
   5
   6          test_number(101, sysdate, x);
   7          dbms_output.put_line(x);
   8
   9          test_number(109, sysdate, x);
  10          dbms_output.put_line(x);

  11
  12 end;
  13 /
BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
77
BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
101
BEGIN gen_number(:p_id, :p_effective_date, :p_number); END;
109

PL/SQL procedure successfully completed.

Best regards

Maxim Received on Sat Mar 03 2007 - 14:17:10 CST