Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!newsfeed.vmunix.org!newsfeed01.sul.t-online.de!t-online.de!nntp.infostrada.it!news-out.tin.it!news-in.tin.it!news1.tin.it.POSTED!not-for-mail
From: "Alessandro Nazzani" <alexnsb@tin.it>
Newsgroups: comp.databases.oracle.misc
References: <KZXMa.16883$HU3.477831@news2.tin.it> <r1u8gv04f5mrr3vefqmuqeq755ovaatbq3@4ax.com> <oT9Na.13898$xK6.352766@news1.tin.it> <irhbgv8p9l9ikqvrp6fr2q70bunm6s1f5a@4ax.com>
Subject: Re: Dyanmic sql and "INSERT... RETURNING"
Lines: 93
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <fxcOa.27262$xK6.735187@news1.tin.it>
Date: Mon, 07 Jul 2003 11:08:59 GMT
NNTP-Posting-Host: 151.99.250.95
X-Complaints-To: newsmaster@tin.it
X-Trace: news1.tin.it 1057576139 151.99.250.95 (Mon, 07 Jul 2003 13:08:59 MET DST)
NNTP-Posting-Date: Mon, 07 Jul 2003 13:08:59 MET DST
Organization: TIN
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:129090

> 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