Help with INSERT PL/SQL function with returning clause
paul...@my-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.
Thomas Kyte
(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)
tk...@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.
tk...@SLACKDOG.WORLD>
tk...@SLACKDOG.WORLD> drop table t;
Table dropped.
tk...@SLACKDOG.WORLD> create table t ( id int );
Table created.
tk...@SLACKDOG.WORLD>
tk...@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
tk...@SLACKDOG.WORLD>
tk...@SLACKDOG.WORLD>
tk...@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.
tk...@SLACKDOG.WORLD>
tk...@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.
tk...@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 tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation