Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!proxad.net!216.239.36.134.MISMATCH!postnews.google.com!p10g2000cwp.googlegroups.com!not-for-mail
From: ar123456@gmail.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: EXECUTE IMMEDIATE rounds up results
Date: 3 Mar 2007 11:38:37 -0800
Organization: http://groups.google.com
Lines: 113
Message-ID: <1172950717.684440.266470@p10g2000cwp.googlegroups.com>
References: <1172940527.424176.63210@31g2000cwt.googlegroups.com>
   <1172942753.498202@bubbleator.drizzle.com>
NNTP-Posting-Host: 71.10.245.33
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1172950723 15141 127.0.0.1 (3 Mar 2007 19:38:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 3 Mar 2007 19:38:43 +0000 (UTC)
In-Reply-To: <1172942753.498202@bubbleator.drizzle.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.8.1.2) Gecko/20070219 Firefox/2.0.0.2,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: p10g2000cwp.googlegroups.com; posting-host=71.10.245.33;
   posting-account=XOfBjw0AAACEk3Zyth1LXWPeRRSmfiDD
Xref: news.f.de.plusline.net comp.databases.oracle.misc:78433

On Mar 3, 12:25 pm, DA Morgan <damor...@psoug.org> wrote:
> ar123...@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...@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