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

From: Alessandro Nazzani <alexnsb_at_tin.it>
Date: Mon, 07 Jul 2003 11:08:59 GMT
Message-ID: <fxcOa.27262$xK6.735187@news1.tin.it>

> the alternative would be to use orakill
> in that case however you would first need to retrieve the threadid of
> the offending process
>
> you can do that by
> select spid
> from v$session s, v$process p
> where p.addr =s.paddr
> and <any other criteria on v$session

That did the trick, thank you.

> 2 That is one of the ways to do it.
> Could you check (connected from a different session) what the
> procedure is waiting for or enable trace in the session?
>
> You can check what it is waiting for by selecting from v$session_wait
> (use select * from dict where table_name like '...' when I have mixed
> up underscores and plurals)

Not sure what to check, forgive me.

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.

Alessandro Received on Mon Jul 07 2003 - 06:08:59 CDT