Help with INSERT PL/SQL function with returning clause

From: <paulsingh_at_my-deja.com>
Date: Wed, 01 Sep 1999 19:20:23 GMT
Message-ID: <7qju9a$212$1@nnrp1.deja.com>

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)-----

(table_name IN varchar2, id_field IN varchar2, field_names IN varchar2, vals IN varchar2)
RETURN NUMBER
IS

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

BEGIN    c := dbms_sql.open_cursor;

   stmt := 'insert into ' || table_name || ' (' || field_names || ')' || ' values (' || vals || ') returning ' || id_field || ' into :bnd1';

   dbms_output.put_line(stmt);
   dbms_sql.parse(c, stmt, dbms_sql.native);
   dbms_sql.bind_variable(c, 'bnd1', new_id);

   dummy := dbms_sql.execute(c);
   dbms_sql.variable_value(c, 'bnd1', new_id);    dbms_sql.close_cursor(c);

   RETURN new_id;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(c);

END insertrow;
------(end)------

I am calling the function with the following command line:

BEGIN
   :newID := insertrow('TEST', 'ID', 'NAME, NAME2, TYPE', '''John'', ''Doe'', 123');
END; 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

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. Received on Wed Sep 01 1999 - 14:20:23 CDT