bug : type problem

[Babo]

hello.
Here is the bug i came across:
db 10.2 on windows XP (32 ).

SQL> create or replace type TVArray is varray (31) of number ;
2 ;
3 /

Warning: Type created with compilation errors

// here u HAVE to open the window with the type , remove the semicolumn and hit F8 to recompile it. In the browser it will show that it's valid now.
If you replace the type from the statement - it will work and it won't produce the error below

SQL> create table TEST_VARRAY ( id number, V_VARR TVARRAY );

Table created

SQL> insert into test_varray values (1,TVArray(1,2,3,4,5,6,7,8,9,10));

insert into test_varray values (1,TVArray(1,2,3,4,5,6,7,8,9,10))

ORA-00600: internal error code, arguments: [qcdogcti-ado], [], [], [], [], [], [], []

The bug is reproducable.

Using
Home: OraDb10g_home1
DLL: C:\oracle\product\10.2.0\db_1\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Character size: 1 byte(s)
DLLs
C:\oracle\product\10.2.0\db_1\bin\oci.dll

PL/SQL Developer
Version 7.0.1.1066 (MBCS)
09.32287 - 2 user license
Windows XP version 6.0 (build 2600) Service Pack 2

Physical memory : 2,096,360 kB (1,340,588 available)
Paging file : 4,038,524 kB (3,259,220 available)
Virtual memory : 2,097,024 kB (1,996,016 available)

Parameters
C:\Program Files\PLSQL Developer\plsqldev.exe

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Plug-Ins
*PL/SQL Developer CompareSource Plug-In version 2003/12/03 (C:\Program Files\PLSQL Developer\PlugIns\CompareSource.dll)
*Shortcuts Plug-In v1.2 by Tiger (C:\Program Files\PLSQL Developer\PlugIns\shortcuts.dll)
(* is Active)

 

[Marco Kalter]

Seems like an Oracle Server bug. I assume the same error occurs if you run the same in SQL*Plus?

 

[Babo]

nope. only if i use the context menu to recompile the type.
in sqlplus and in command window everything works w/o a glitch.

 

[Claus Pedersen]

Just a thought, do you have debug information turned on in PLD? In that case, try to turn it off when compiling types.

Best regards Claus Pedersen

 

[Babo]

thank you Claus.
But would it matter ?
// Just curious. no offence meant

 

[Babo]

doh..
But would it matter ? -> But why would it matter ?

 

[Marco Kalter]

ORA-00600 errors are by definition Oracle Server bugs (the server process dumps). It's difficult to predict what might and what might not affect this, but debug information is a good candidate. Just give it a try and let us know.

 

[vladzak]

Looks like it would - because that's the only difference between compiling from plus and from context menu. To see if it really matters you can try to
ALTER TYPE TVArray COMPILE DEBUG;
and then try your insert in SQL*Plus. Then patch the server to 10.1.0.2 patch set and see if the bug persists.

 

[vladzak]

Sorry, meant 10.2.0.2

 

[Babo]

To Marco:
Oracle does NOT have any known issues with these arguments. I still filed the SR, but, at this point, it is clear that this is not their problem.
If you can demonstrate that this is server problem -i'll be more than happy to update SR with your information.

As you proposed :
if the type has been created w/o errors ( initially ) then compilation ( either using menu or by pressing F8) with or w/o debug info added ( using context menu ) will NOT cause any problems later on.

SQL> create or replace type TVArray is varray (31) of number ;
2 /

Type created

SQL> alter type tvarray compile debug ;

Type altered

SQL> create table tbl_tvarr( id tvarray );

Table created

SQL> insert into tbl_tvarr values ( tvarray(1) );

1 row inserted

SQL> drop table tbl_tvarr purge ;

I'm not sure how can i modify the def. of the type from the command line w/o overwriting the whole thing... in other words - how can i mimic the context menu.

 

[Babo]

to vladzak :
It's a good idea to patch the server. But let's first fix the problem with the tool....

 

[Marco Kalter]

ORA-00600 is never a tool problem. It is, by shere definition, an Oracle Server bug, since the server process crashed. This should never happen, regardless of the client side tool used, or the statements executed.

 

[Babo]

Marco, i agree and disagree on this statement:
yes, it's an indication that oracle process ( actually it's an user process crashing ) crashed.
But the tool can perform illegal operations that would cause the process to crash which is normal.

Plus, i have not come up with the way to reproduce this problem in sqlplus - which leads me to my original statement - the review should start at the tool's level.

Another note - don't you think that it's much easier for your CS to try to reproduce this and thus had a chance to improve your tool, then push your customer back telling that this is not your problem?

BTW, don't you have some kind of application where custoemrs can file bugs or shortcomings of the tool other then forums ?

 

[Marco Kalter]

But the tool can perform illegal operations that would cause the process to crash which is normal.

This is not true. ORA-00600 is never a predictable result of any operation, unless there is a known server bug.

Plus, i have not come up with the way to reproduce this problem in sqlplus - which leads me to my original statement - the review should start at the tool's level.

Unfortunately there is nothing for us to review. I can't reproduce this, and "ORA-00600: internal error code, arguments: [qcdogcti-ado]" holds no information for anyone but Oracle Support.

Another note - don't you think that it's much easier for your CS to try to reproduce this and thus had a chance to improve your tool, then push your customer back telling that this is not your problem?

Yes, and I don't like to do it, but sometimes there is nothing else you can do. We cannot reproduce every Oracle bug on every client/server/platform/version combination, and try to come up with work arounds for everything.

BTW, don't you have some kind of application where custoemrs can file bugs or shortcomings of the tool other then forums ?

You can send support requests to support@allroundautomations.com.

 

[Babo]

quotelus, i have not come up with the way to reproduce this problem in sqlplus - which leads me to my original statement - the review should start at the tool's level.

Unfortunately there is nothing for us to review. I can't reproduce this, and "ORA-00600: internal error code, arguments: [qcdogcti-ado]" holds no information for anyone but Oracle Support.

and i can't reproduce this in sqlplus. there is nothing "not normal" in my setup - xp with latest patches and oracle 10.2 base.

anyway. I don't really care about this thing anyway. just i'm getting some strange feeling - this is the second time i'm trying to file a bug with you and the second time i'm getting the same "not our problem" response.

 

[Bo Pedersen]

I just got curios. This is reproducible in a multi-session scenario both in PL/SQL Developer (7.0.1.1066) and SQL*Plus (10.2.0.1.0) against an Oracle 9.2.0.6.0 database.

Start two SQL*Plus windows/prompts:

In the first prompt type:

create or replace type TVArray is varray (31) of number ;
;
/

In the second prompt type:

create or replace type TVArray is varray (31) of number ;
/

Now go back to the first one and type:

create table TEST_VARRAY (id number, V_VARR TVARRAY);
insert into test_varray values (1,TVArray(1,2,3,4,5,6,7,8,9,10));

Now I get an 'ORA-03113: end-of-file on communication channel' . So it is now demonstrated to be an Oracle problem as Marco stated. But not an debug information problem.

And I think it is well known that Oracle tries to blame the client application or the written SQL in case like these until it is proven that it is their bug. At least in my experience.

Bo Pedersen

 

[Marco Kalter]

just i'm getting some strange feeling - this is the second time i'm trying to file a bug with you and the second time i'm getting the same "not our problem" response.

Like I said, I don't like to do that, but sometimes I have no other choice. I'm glad that Bo (thanks!) was able to reproduce the issue on his configuration in SQL*Plus, which demonstrates that this is a server issue and not a client tool issue.

 

[vladzak]

yes, it's an indication that oracle process ( actually it's an user process crashing ) crashed.
But the tool can perform illegal operations that would cause the process to crash which is normal.

The first statement is outright incorrect: it's Oracle server process that crashed, no way it could be user process unless you mix Oracle shadow processes and client-side processes and call both "user". ORA-600 are internal errors, ones for which no better error message exists yet or ones that indicate server software malfunction (probably due to code defect in server software itself, but not necessarily.) The client-side tool can't be blamed in this case, though exact commands the tool issues and their order may be different from other tools doing seemingly the same thing and it's what makes the difference.

Second statement is at least questionable - if a client-side tool can perform operations on the server that predictably crash the server process, then this is a defect in server code, and it's a security bug, because it can at least cause denial of service and at most it can allow arbitrary code execution on the server host. This is not normal and all such "operations" should be reported to the vendor.

Back to the issue: to reproduce it in SQL*Plus you would do the following:

1) in one session, create a type with syntax error (or without, I am not sure it's relevant.)
2) in another session, recreate it with correct syntax so that it's valid.
3) back in first session, create a table of this type and try to insert new type instance into it.

This should result in ORA-600 in first session. I believe it will, because it's what PL/SQL Developer does when you run a script in command window and then open code editor and compile from it - code editor uses its own session. You may need to check if you are using dedicated server processes or shared server processes and which mode causes the error if not both - dedicated vs shared can make a big difference.

Still, my suggestion to patch the server to the latest patch set level stands - it may be a known code defect already fixed. It could be filed with different symptoms and that's why you and OSS analysts didn't find anything related. For example, we had an ORA-600 issue recently which OSS at first didn't recognize as a known bug, but after more thorough analysis of trace files they figured out it's the same bug they already fixed, just with different symptoms.