| "returning" in a dbms_sql.parse statement [message #39361] |
Thu, 11 July 2002 08:03  |
jerome
Messages: 13
Registered: May 2000
|
Junior Member |
|
|
Hi,
I've try this simple procedure :
******************************************
procedure ps_test2
(c1 number, c2 number, r out number) is
c number;
n number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'insert into jdtab values (:bnd1, :bnd2) returning col1*col2 into :bnd3', dbms_sql.native);
dbms_sql.bind_variable(c, 'bnd1', c1);
dbms_sql.bind_variable(c, 'bnd2', c2);
dbms_sql.bind_variable(c, 'bnd3', r);
n := dbms_sql.execute(c);
dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind variable
dbms_Sql.close_Cursor(c);
end;
************************************************
Here is my problem :
SQL> desc jdtab
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER(2)
COL2 NUMBER(2)
SQL> variable z number;
SQL> execute ps_test2(2,3,:z);
begin ps_test2(2,3,:z); end;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "TNT.PS_TEST2", line 12
ORA-06512: at line 1
Could someone explain me why?
In my mind this procedure should do this:
insert into jdtab values(2,3);
and put 6 in the z variable...
Thanks for help
|
|
|
|
| Re: "returning" in a dbms_sql.parse statement [message #39363 is a reply to message #39361] |
Thu, 11 July 2002 09:29  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Jerome, there must be something else going on here, because your code was fine on 8.1.7.4:
sql>declare
2 c1 number := 2;
3 c2 number := 3;
4 c number;
5 n number;
6 r number;
7 begin
8 c := dbms_sql.open_cursor;
9 dbms_sql.parse(c,
10 'insert into jdtab values (:bnd1, :bnd2) ' ||
11 ' returning col1*col2 into :bnd3', dbms_sql.native);
12 dbms_sql.bind_variable(c, 'bnd1', c1);
13 dbms_sql.bind_variable(c, 'bnd2', c2);
14 dbms_sql.bind_variable(c, 'bnd3', r);
15 n := dbms_sql.execute(c);
16 dbms_sql.variable_value(c, 'bnd3', r);
17 dbms_sql.close_cursor(c);
18 dbms_output.put_line( r );
19 end;
20 /
6
PL/SQL procedure successfully completed. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
