Re: EXECUTE IMMEDIATE rounds up results

From: <ar123456_at_gmail.com>
Date: 3 Mar 2007 11:38:37 -0800
Message-ID: <1172950717.684440.266470@p10g2000cwp.googlegroups.com>

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 Received on Sat Mar 03 2007 - 13:38:37 CST