Showing posts with label ORA-00600. Show all posts
Showing posts with label ORA-00600. Show all posts

Tuesday, February 09, 2016

ORA-00600: internal error code when executing trigger

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Here is another example of ORA-00600 where probably it makes no sense at all to contact Oracle Support because you can fix the problem by yourself.
 
ORA-00600: internal error code, arguments: 
[17273], [0x07A89CBA8], [0], [1], [], [], [], [], [], [], [], []
 ORA-04088: error during execution of trigger Y
 
I found out that SQLDeveloper version 4.1.1 (and later possibly, I couldn't verify), sometimes generates triggers' DDLs with one trailing (invalid) character that causes the Oracle session to crash in Oracle XE 11.

The funniest part in the story is in that such character is not to be seen anywhere when you copy and paste the DDL, so it goes through completely inadvertently and the code compiles without a hitch.
As soon as the trigger fires, the session crashes.
Luckily, when you open the trigger source code in SQLDeveloper, you'll notice a strange character in the last position.
Just remove it and recompile the trigger.

I ignore if the bug is in SQLDeveloper or at some lower level, if it affects only Oracle XE 11 or also different versions.

See message translations for ORA-00600 and search additional resources.

Thursday, March 26, 2009

ORA-22922: nonexistent LOB value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As mentioned in a previous posting about ORA-22275, a temporary LOB can be turned into a persistent LOB by moving it into a table, however one thing is persistence and a different thing is LOB consistency.

Funny things happen when one gets fancy in the use of SAVEPOINT/ROLLBACK in conjunction with LOBs.

The following PL/SQL block represents a simple scenario of an ill-designed procedure that is rolling back under a certain condition thus invalidating the LOB pointer.
declare
l_clob clob;
l_id number;
simulated_error exception;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback;
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 560
ORA-06512: at line 19
The procedure can be modified as follows:
declare
l_clob clob;
l_id number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/
This time no ORA-22922 will be raised and apparently we managed to transform a temporary LOB locator into a persistent one without inserting any rows, because the insert statement was rolled back.

Interestingly enough on my Oracle XE instance, if i further modify the program as follows, i'm eventually getting ORA-00600:
declare
l_clob clob;
l_id number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
insert into files(document) values(l_clob);
commit;
end;
/

ORA-00600: internal error code, arguments: [7999], [300], [], [], [], [], [], []
ORA-06512: at line 21
If i insert a COMMIT statement before the final INSERT, we're back to the initial situation:
declare
l_clob clob;
l_id number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
commit;
insert into files(document) values(l_clob);
commit;
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at line 22
In conclusion ORA-22922 is a symptom of a program that is incorrectly handling LOB locators across transactions.

For a complete review of weird effects achievable by tampering with LOB locators and partial LOB manipulation procedures, see the linked paragraph in the Oracle Application Developer's Guide for Large Objects - Advanced design considerations chapter.

See message translations for ORA-22922 and search additional resources.

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles