when I run a sql/xml query, and retrieve xmltype data, it works fine the one the first time. The second xmltype select will make plsqldev hang, and generate an file in the udump dir.

the result happens if I query xml from a comand line window or a sql window. It does not matter if the xmltype comes from a view, a table or directly from sql/xml function. If I wrap the xmltype into a clob, then then everything is fine (but is unreadable, as all carriage returns are removed). Finally, SQL plus can diplay several xmltype on a row.

to reproduce, try:
select xmlcomment('hello pretty sql/xml world') as result1 from dual;
select xmlcomment('hello pretty sql/xml world') as result2 from dual;

in the command window, it takes times (2 sec) to display the second header (result2), and xml comment never appears.

here is some support info from plsqldev, and the oracle log for the error.

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

Physical memory : 1'046'960 kB (264'636 available)
Paging file : 2'517'092 kB (1'780'152 available)
Virtual memory : 2'097'024 kB (2'014'872 available)

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

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

Preference Files
C:\Program Files\PLSQL Developer\Preferences\Default\Default.ini
C:\Program Files\PLSQL Developer\Preferences\jsalerno\default.ini

Plug-Ins
*Comment lines v1.1 (C:\Program Files\PLSQL Developer\PlugIns\commentline.dll)
(* is Active)

Aliases
TELLUS

Homes
OraClient10g_home1 (C:\Oracle\product\10.1.0\Client_1)

DLLs
C:\Oracle\product\10.1.0\Client_1\bin\oci.dll

TNS File
C:\Oracle\product\10.1.0\Client_1\Network\Admin\tnsnames.ora

Using
Home: OraClient10g_home1
DLL: C:\Oracle\product\10.1.0\Client_1\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Release 10.2.0.2.0
Character size: 1 byte(s)


SOFTWARE\ORACLE
inst_loc = C:\Program Files\Oracle\Inventory
VOBHOME2.0 = C:\Oracle\product\10.1.0\Client_1

SOFTWARE\ORACLE\KEY_OraClient10g_home1
ORACLE_HOME = C:\Oracle\product\10.1.0\Client_1
ORACLE_HOME_NAME = OraClient10g_home1
ORACLE_GROUP_NAME = Oracle - OraClient10g_home1
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME = Enterprise
ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_OraClient10g_home1

SOFTWARE\ORACLE\Workflow
Icon Directory = C:\Oracle\product\10.1.0\Client_1\WF\ICON
Level = 100
Version = 2.6.3.0.0

SOFTWARE\ORACLE\Workflow\Languages

C:\Oracle\product\10.1.0\Client_1\bin
C:\Oracle\product\10.1.0\Client_1\jre\1.4.2\bin\client
C:\Oracle\product\10.1.0\Client_1\jre\1.4.2\bin
D:\Oracle\product\10.1.0\Client_1\bin
C:\PVSW\BIN
C:\Program Files\ThinkPad\Utilities
C:\WINDOWS\system32
C:\WINDOWS
C:\WINDOWS\System32\Wbem
C:\Program Files\Intel\Wireless\Bin\
C:\Program Files\ATI Technologies\ATI Control Panel
C:\WINDOWS\Downloaded Program Files
C:\IBMTOOLS\Python22
C:\PROGRA~1\ULTRAE~1
C:\Program Files\QuickTime Alternative\QTSystem\


*********************
ora-600 core header:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production
ORACLE_HOME = /opt/oracle/product/10g
System name: SunOS
Node name: tellus
Release: 5.10
Version: Generic_118822-23
Machine: sun4u
Instance name: P*****
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 12334, image: oracleP*****@tellus

*** ACTION NAME:(SQL Window - New) 2006-09-20 20:09:26.993
*** MODULE NAME:(PL/SQL Developer) 2006-09-20 20:09:26.993
*** SERVICE NAME:(P*****) 2006-09-20 20:09:26.993
*** SESSION ID:(294.30813) 2006-09-20 20:09:26.993
*** 2006-09-20 20:09:26.993
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kolrrdl:0rfc], [], [], [], [], [], [], []
----- Call Stack Trace -----
bla bla bla

tell me if you need something else! thank you ^^


edit: my plsqldev.log file last entry is from... 2005 :p

MetaLink has this to say about the error:

Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.2.0
This problem can occur on any platform.

Symptoms

Received internal error ORA-00600: internal error code, arguments: [kolrrdl:0rfc], [], [], [], [], [], [], []
when freeing session duration temp lobs before the end of the session.

Call stack trace is similar to:

kolrrdl kolradc kokegPromoteLob

Cause
This is Bug:4745114
Abstract: ORA-600 [KOLRRDL:0RFC] BEING IN IN XML/XLOB PL/SQL BLOCK

Session durations were getting added on to the duration table. Since session durations are not expected, delete and search operations are not handled properly causing error.

Solution

A possible workaround is: Don't free session duration lobs until the end of session.

This issue is fixed in Oracle server version 11.0.0.0.0

One-off patches may be available depending on your current release and operating system.

To obtain a patch from MetaLink:
1) Click on Patches & Updates
2) Click on Simple Search
3) Enter patch number: 4745114
4) Select your O/S
5) Click Go.

wohooo! thanks a lot!!!

I'm not dba here so it might take some time before the patch is applied.

update: Murphy strikes again! There is no release for sun sparc-64 10.2.0.2 :'(

I will do some lobbying on my dba company so that we can get it compiled for our server.

but thank you again for this precious insight

>This issue is fixed in Oracle server version 11.0.0.0.0

interesting