Re: Help with INSERT PL/SQL function with returning clause

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Sep 1999 19:51:29 GMT
Message-ID: <37cd832b.119694581@newshost.us.oracle.com>

A copy of this was sent to paulsingh_at_my-deja.com (if that email address didn't require changing) On Wed, 01 Sep 1999 19:20:23 GMT, you wrote:

>
>
>Hello everyone,
>
>Our rep from Oracle has run out of ideas with this, so I am hoping that
>you Oracle gurus out in the Usenet world can offer some help... I had
>thought that this would have been a fairly simple procedure, but it's
>taken much longer than I expeced...
>
>I am trying to write a PL/SQL function that takes the table name, column
>names, and column values as parameters and then creates and executes an
>INSERT statement. The function should return the value of the column
>specified by a fourth parameter (id_field).
>
>* TEST table definition:
>
> ID - number
> NAME - varchar2
> NAME2 - varchar2
> TYPE - number
>
>* The ID column has an insert trigger associated with it that takes the
>next value in the TEST_SEQ sequence and inserts it into the ID column.
>
>Here's the function that we wrote:
>
>-----(start)-----
>

[snip]

>which produces the following output in SQLPlus with serveroutput on:
>
>insert into TEST (NAME, NAME2, TYPE) values ('John', 'Doe', 123)
>returning ID into :bnd1
>BEGIN :newID := newnew('TEST', 'ID', 'NAME, NAME2, TYPE', '''John'',
>''Doe'', 123'); END;
>*
>ERROR at line 1:
>ORA-06503: PL/SQL: Function returned without value
>ORA-06512: at "MEDIASTATION.INSERTROW", line 24
>ORA-06512: at line 1
>

it returns with no value because of the faulty exception handler. either get rid of it or have the exception block re-raise the exception or have the exception block return something.

This is a bug, it was introduced in 8.0.4 and fixed in 8.1. Here is the bug replicated followed by the solution (execute an anonymous block instead of just an insert statement -- all you need to do is add a BEGIN before INSERT and ;end; after :bnd1 and it'll be good to go)

tkyte_at_SLACKDOG.WORLD> create or replace function f   2 (table_name IN varchar2, id_field IN varchar2, field_names IN varchar2,   3 vals IN varchar2)
  4 RETURN NUMBER
  5 IS

  6     c INTEGER;  -- holds a cursor ID
  7     dummy INTEGER;
  8     stmt VARCHAR2(200);
  9     new_id NUMBER;

 10
 11 BEGIN
 12 c := dbms_sql.open_cursor;
 13
 14 stmt := 'insert into ' || table_name || ' (' || field_names || ')' ||  15 ' values (' || vals || ') returning ' || id_field || ' into :bnd1';  16

 17     dbms_output.put_line(stmt);
 18     dbms_sql.parse(c, stmt, dbms_sql.native);
 19     dbms_sql.bind_variable(c, 'bnd1', new_id);
 20     dummy := dbms_sql.execute(c);
 21     dbms_sql.variable_value(c, 'bnd1', new_id);
 22     dbms_sql.close_cursor(c);
 23  
 24     RETURN new_id;

 25
 26 END ;
 27 /

Function created.

tkyte_at_SLACKDOG.WORLD>
tkyte_at_SLACKDOG.WORLD> drop table t;

Table dropped.

tkyte_at_SLACKDOG.WORLD> create table t ( id int );

Table created.

tkyte_at_SLACKDOG.WORLD>
tkyte_at_SLACKDOG.WORLD> exec dbms_output.put_line( f( 't', 'id', 'id', '1' ) ); insert into t (id) values (1) returning id into :bnd1 begin dbms_output.put_line( f( 't', 'id', 'id', '1' ) ); end;

*
ERROR at line 1:

ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "TKYTE.F", line 20
ORA-06512: at line 1
ORA-01427: single-row subquery returns more than one row


tkyte_at_SLACKDOG.WORLD> 

tkyte_at_SLACKDOG.WORLD>
tkyte_at_SLACKDOG.WORLD> create or replace function f   2 (table_name IN varchar2, id_field IN varchar2, field_names IN varchar2,   3 vals IN varchar2)
  4 RETURN NUMBER
  5 IS

  6     c INTEGER;  -- holds a cursor ID
  7     dummy INTEGER;
  8     stmt VARCHAR2(200);
  9     new_id NUMBER;

 10
 11 BEGIN
 12 c := dbms_sql.open_cursor;
 13
 14 stmt := 'BEGIN insert into ' || table_name || ' (' || field_names || ')' ||
 15 ' values (' || vals || ') returning ' || id_field || ' into :bnd1; end;';  16

 17     dbms_output.put_line(stmt);
 18     dbms_sql.parse(c, stmt, dbms_sql.native);
 19     dbms_sql.bind_variable(c, 'bnd1', new_id);
 20     dummy := dbms_sql.execute(c);
 21     dbms_sql.variable_value(c, 'bnd1', new_id);
 22     dbms_sql.close_cursor(c);
 23  
 24     RETURN new_id;

 25
 26 END ;
 27 /

Function created.

tkyte_at_SLACKDOG.WORLD>
tkyte_at_SLACKDOG.WORLD> exec dbms_output.put_line( f( 't', 'id', 'id', '1' ) ); BEGIN insert into t (id) values (1) returning id into :bnd1; end; 1

PL/SQL procedure successfully completed.

tkyte_at_SLACKDOG.WORLD>

>but if I take the INSERT statement that it generated and type it in
>manually through SQLPlus, the insertion works fine as long as I declare
>the bind variable first (variable :newID number;)...
>
>Any ideas? We are running Oracle 8.0.5 on a Linux box. I've been told
>that the above function works fine with Oracle8i but we are reluctant to
>move up to 8i since many people have complained that it's extremely
>buggy.
>
>Is there a way to accomplish the same thing with 8.0.5?
>
>Thanks...
>
>- Paul Singh
>
>
>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 Wed Sep 01 1999 - 14:51:29 CDT