Re: Problem with returning clause in insert stmt

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 09 Aug 1999 20:47:51 GMT
Message-ID: <37bb3dee.31685821@newshost.us.oracle.com>

A copy of this was sent to jairamk_at_geocities.com (if that email address didn't require changing) On Mon, 09 Aug 1999 18:45:22 GMT, you wrote:

>Hi! i am trying to execute the following pl/sql block
>where t is a table with a number column.
>t( n number)
>
>variable r varchar2(20);
>declare
> r rowid;
> cid integer;
> n integer;
>begin
> cid := dbms_sql.open_cursor;
> dbms_sql.parse (cid, 'insert into t values(1) returning rowid into
>:g ',dbms_sql.native);
> dbms_sql.bind_variable_rowid(cid,'g',r);
> n:= dbms_sql.execute(cid);
> dbms_sql.variable_value_rowid(cid,'g',r);
> dbms_sql.close_cursor(cid);
> :r := r;
>end;
>/
>print r
>
>This is the error message i get :
>
>declare
>*
>ERROR at line 1:
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
>ORA-06512: at "SYS.DBMS_SQL", line 328
>ORA-06512: at line 9
>ORA-01427: single-row subquery returns more than one row
>
>if i get rid of the bind_variable call i get the error message
>not all variables bound...
>
>am i doing something wrong?.. pl help me out...
>thanks in advance
>
>jairam kuppuswamy
>
>PS : The Oracle version is 8.0.4
>

that's a bug in 8.0.4, fixed in (at least) 8.1 and perhaps earlier. The workaround is to use an anonymous block as follows:

  1 declare
  2 r rowid;
  3 cid integer;
  4 n integer;
  5 begin

  6      cid := dbms_sql.open_cursor;
  7      dbms_sql.parse (cid, 'begin insert into t values(1) returning rowid

into
  8 :g ; end;',dbms_sql.native);

  9      dbms_sql.bind_variable_rowid(cid,'g',r);
 10      n:= dbms_sql.execute(cid);
 11      dbms_sql.variable_value_rowid(cid,'g',r);
 12      dbms_sql.close_cursor(cid);
 13      :r := r;

 14* end;
SQL> / just but a BEGIN / ; END; around the insert and it'll work.

>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 09 1999 - 15:47:51 CDT