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

Jmodic, Marist98, Pando...any logical explaination for this?

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:

Quote:

---

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.

---

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.

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.