-
Internal Error if Alterred a table
Hi All,
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production
I am seeing a strange error...Please look into below example..
Code:
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger Testing_Internal_Error after ALTER on schema
2 begin
3 insert into scott.test(created_by) values('hell');
4 --commit;
5 --dbms_output.put_line('Hello Abhay');
6 --RAISE_APPLICATION_ERROR(-20115, 'Abhay u r screwing DB');
7* end;
SQL> /
Trigger created.
SQL> alter table test modify created_by varchar(20);
alter table test modify created_by varchar(20)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 2, column 19:
PL/SQL: ORA-00600: internal error code, arguments: [12830], [SCOTT], [TEST], [], [], [], [], []
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
SQL> alter trigger Testing_Internal_Error disable;
Trigger altered.
SQL> alter table test modify created_by varchar(20);
Table altered.
I have a trigger which monitors any ALTER commands issued on any object in particular schema & inserts record in reference table.
The above example is dummy example to reproduce the error.
what i see is that if we altered the table defination which is used in trigger to insert a record...it throws Internal error...
Rather it could have thrown some meaningful error.
Please if any body could explain this..
Thanks
Abhay
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Jmodic, Marist98, Pando...any logical explaination for this?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I think in this case Oracle doesn't know how to handle the error so it throws an ORA-600. The ORA-600 part is probably fixed in a patched version. I think error trying the real error is an ORA-12830 which is:
Error: ORA 12830 Text: Must COMMIT or ROLLBACK after executing parallel INSERT/UPDATE/DELETE
---------------------------------------------------------------------------
Cause: After executing a parallel INSERT/UPDATE/DELETE statement, a command
other than COMMIT or ROLLBACK was issued. Action: Execute COMMIT or ROLLBACK before issuing another SQL command.
Jeff Hunter
-
I can reproduce this same behavior on 9.2.0.1.0, exactly the same ORA-600 message.
I couldn't find anything similar on metalink, so I suspect it hasn't been patched yet.
Abhay, I suggest you report this to Oracle as a potential bug.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Jmodic,Marist89...
Please check this...i think this is expected error ..
Code:
SQL> ed
Wrote file afiedt.buf
1 create or replace procedure test2 as
2 begin
3 declare v_created_by varchar(28);
4 Begin
5 insert into scott.test(created_by) values ('Hell');
6 -- commit;
7 --select created_by into v_created_by from scott.test where rownum=1;
8 --dbms_output.put_line('Hello Abhay, the user who created the column :' || v_created_by );
9 end;
10* end;
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger Testing_Internal_Error after ALTER on schema
2 begin
3 scott.test2;
4 --commit;
5 --dbms_output.put_line('Hello Abhay');
6 --RAISE_APPLICATION_ERROR(-20115, 'Abhay u r screwing DB');
7* end;
SQL> alter table test modify created_by varchar(26);
alter table test modify created_by varchar(26)
*
ERROR at line 1:
ORA-30512: cannot modify SCOTT.TEST more than once in a transaction
But if i give the insert statement directly in Trigger i get INTERNAL Error.....
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|