Re: Dyanmic sql and "INSERT... RETURNING"

> I'd strongly suggest using stored procedures to do DML, as follows (using
> your example but with an out parameter to a procedure). I'd always use
> procedures to do work with data and functions to do calculations etc.
>
> 1 create or replace procedure STORE_TEST (p_USERID in number, p_OBJID
in
> 2 number, p_TOTQUESTIONS in number, p_CORRECTANSWERS in
number,p_new_key
> out number)
> 3 as
> 4 Begin
> 5 INSERT INTO EVENTS (ID_EVENTYPE, ID_USERID, ID_OBJ) VALUES
(15,
> 6 p_userid, p_objid) returning ID_EVENT into p_new_key;
> 7 INSERT INTO RESULTS (ID_EVENT, TOTQUESTIONS, CORRECT) VALUES
> 8 (p_new_key, P_totquestions, p_correctanswers);
> 9* end;
> SQL> /
>
> Procedure created.
>
> SQL> var new_key number;
> SQL> set autoprint on
> SQL> exec niall.STORE_TEST(5, 259, 10, 5,:new_key);
>
> PL/SQL procedure successfully completed.
>
>
> NEW_KEY
> ----------
> 1
>
> SQL> select * from events;
>
> ID_EVENTYPE ID_USERID ID_OBJ ID_EVENT
> ----------- ---------- ---------- ----------
> 15 5 259 1
>
> SQL> select * from results;
>
> TOTQUESTIONS CORRECT ID_EVENT
> ------------ ---------- ----------
> 10 5 1

Niall,

thank you very much for your assistance: it works like a treat.

Kind regards.

Alessandro Received on Mon Jul 07 2003 - 10:39:06 CDT