DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Internal Error if Alterred a table

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  


Click Here to Expand Forum to Full Width