Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dyanmic sql and "INSERT... RETURNING"

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

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 7 Jul 2003 13:15:28 +0100
Message-ID: <3f096461$0$18489$ed9e5944@reading.news.pipex.net>


"Alessandro Nazzani" <alexnsb_at_tin.it> wrote in message news:fxcOa.27262$xK6.735187_at_news1.tin.it...
> Anyway, I wanted to be sure I'm not missing something very basic here. So
I
> tried re-building everything from scratch:
>
> SQL> create table events (ID_EVENTYPE number, ID_USERID number, ID_OBJ
> number, ID_EVENT number);
>
> Table created.
>
> SQL> create table results (TOTQUESTIONS number, CORRECT number, ID_EVENT
> number);
>
> Table created.
>
> SQL> CREATE SEQUENCE EVENTS_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE
1.0E28
> MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
>
> Sequence created.
>
> SQL> CREATE OR REPLACE TRIGGER EVENTS_TRIG BEFORE INSERT ON EVENTS FOR
EACH
> ROW
> 2 DECLARE NEXT_KEY NUMBER;
> 3 BEGIN
> 4 SELECT EVENTS_SEQ.NEXTVAL INTO NEXT_KEY FROM DUAL;
> 5 :NEW.ID_EVENT := NEXT_KEY;
> 6 END;
> 7 /
>
> Trigger created.
>
> SQL> create or replace function STORE_TEST (p_USERID in number, p_OBJID
in
> number, p_TOTQUESTIONS in number, p_CORRECTANSWERS in number) return
NUMBER
> as
> 2 l_new_key number;
> 3 Begin
> 4 INSERT INTO EVENTS (ID_EVENTYPE, ID_USERID, ID_OBJ) VALUES (15,
> p_userid, p_objid) returning ID_EVENT into l_new_key;
> 5 INSERT INTO RESULTS (ID_EVENT, TOTQUESTIONS, CORRECT) VALUES
> (l_new_key, P_totquestions, p_correctanswers);
> 6 return l_new_key;
> 7 end;
> 8 /
>
> Function created.
>
> SQL> select STORE_TEST(5, 259, 10, 5) from dual;
> select STORE_TEST(5, 259, 10, 5) from dual
> *
> ERROR at line 1:
> ORA-14551: cannot perform a DML operation inside a query
> ORA-06512: at "PLURIWEB.STORE_TEST", line 4
> ORA-06512: at line 1
>
> To work around this problem I modified my function adding "pragma
> autonomous_transaction" (is this correct?).
> I then tried invoking the function again and let it run for 5 minutes or
so:
>
> SQL> select STORE_TEST(5, 259, 10, 5) from dual;
> select STORE_TEST(5, 259, 10, 5) from dual
> *
> ERROR at line 1:
> ORA-00600: internal error code, arguments: [17281], [600], [69134088], [],
> [],
> [], [], []
> ORA-00600: internal error code, arguments: [17034], [55836944], [0], [],
[],
> [], [], []
>
> Please help.

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 Litchfield
Oracle DBA
Audit Commission UK
Received on Mon Jul 07 2003 - 07:15:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US