Re: DBMS_SQL package problem with cmd : inert into ... returning var into :desc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 12 Feb 1999 16:31:20 GMT
Message-ID: <36c654f0.13892286@192.86.155.100>

A copy of this was sent to "LPS LYON" <lps.lyon_at_wanadoo.fr> (if that email address didn't require changing) On Fri, 12 Feb 1999 16:30:13 +0100, you wrote:

>I would like to execute an insert returning command like this :
>
> INSERT INTO table (var1, var2, ...) VALUES (val1, val2, ...) RETURNING
>var1 INTO :desc
>
>I want to use DBMS_SQL package with BIND_VALUE and VARIABLE_VALUE to execute
>this command but oracle server answer with error n°1427.
>
>Does anyone has a solution for me.
>
>Thank you, Franck.
>

Can you post an example cut and pasted from sqlplus? 1427 is a 'subquery returns more then one row' which shouldn't happen from an insert unless a trigger or something is throwing it, for example:

  1 create or replace trigger foo
  2 after insert on t
  3 begin

  4     for x in ( select * from all_users where username = ( select
  5                  username from all_users ) ) loop
  6         null;
  7     end loop;

  8* end;
SQL> / Trigger created.

SQL> insert into t values ( 6 );
insert into t values ( 6 )

            *
ERROR at line 1:

ORA-01427: single-row subquery returns more than one row
ORA-06512: at "TKYTE.FOO", line 4
ORA-04088: error during execution of trigger 'TKYTE.FOO'

but that doesn't have anything to do with the insert returning with works fine in plsql:

SQL> create table t ( x int );
Table created.

SQL> declare

  2      iIgnoreRetCode                                integer;
  3      cPkgCursor integer;
  4          vStatement varchar2(500);
  5      l_rowid    varchar2(  200 );
  6  begin
  7      cPkgCursor := dbms_sql.open_cursor;
  8  
  8      vStatement := 'insert into t values ( 5 ) returning rowid into :x';
  9  
  9      dbms_sql.parse( cPkgCursor, vStatement, dbms_sql.native );
 10  
 10      dbms_sql.bind_variable( cPkgCursor, ':x', l_rowid,
 11                                                          out_value_size =>

200 );
 12
 12 iIgnoreRetCode := dbms_sql.execute( cPkgCursor );  13
 13 dbms_sql.variable_value( cPkgCursor, ':x', l_rowid );  14
 14 dbms_sql.close_cursor( cPkgCursor );  15
 15 dbms_output.put_line( l_rowid );  16 end;
 17 /
AAAWdsAAFAAAdPCAAA

PL/SQL procedure successfully completed.  

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 12 1999 - 10:31:20 CST