Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!newsfeed01.sul.t-online.de!newsmm00.sul.t-online.de!t-online.de!news.t-online.com!not-for-mail From: Maxim Demenko Newsgroups: comp.databases.oracle.misc Subject: Re: EXECUTE IMMEDIATE rounds up results Date: Sat, 03 Mar 2007 21:17:10 +0100 Organization: T-Online Lines: 136 Message-ID: <45E9D7C6.8080402@gmail.com> References: <1172940527.424176.63210@31g2000cwt.googlegroups.com> <1172942753.498202@bubbleator.drizzle.com> <1172950717.684440.266470@p10g2000cwp.googlegroups.com> Reply-To: mdemenko@google.com Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Trace: news.t-online.com 1172953029 02 20006 0xrr6TVv2dMAGB7 070303 20:17:09 X-Complaints-To: usenet-abuse@t-online.de To: ar123456@gmail.com X-ID: GDi9yTZ1Qe3EoSodxYMjeW1VLkVrkzTXG3BsgkjuGfQ6C8ttQLDWZx User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) In-Reply-To: <1172950717.684440.266470@p10g2000cwp.googlegroups.com> Xref: news.f.de.plusline.net comp.databases.oracle.misc:78435 ar123456@gmail.com schrieb: > On Mar 3, 12:25 pm, DA Morgan 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 > > > > 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