XMLtype column displays as when stored as binary XML

[bcleaver]

Using Oracle 11g R2 I'm running into an issue where PL/SQL Developer will execute indefinitely while selecting an xmltype column that is stored as binary xml. The column is returned as and the only way to stop execution is to close/terminate the executing window; breaking execution with Shift+Esc is ineffective. If .getclobval() is added to the column it correctly displays as . Tying to rule out my local settings as the problem, I used Oracle's SQL Developer and the column displayed correctly without needing a CLOB conversion. Is there any way to resolve this error so xmltype columns don't need to be explicitly converted?

CREATE TABLE bin_xml (xml_col XMLTYPE)
XMLTYPE COLUMN xml_col STORE AS SECUREFILE BINARY XML;

INSERT INTO bin_xml
      VALUES(XMLTYPE('<TestNode>TestVal</TestNode>'));
COMMIT;

SELECT bx.xml_col --<Value Error>
  FROM bin_xml bx;

SELECT bx.xml_col.getclobval() --<XMLTYPE>
  FROM bin_xml bx;

Client: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
NLS_CHARACTERSET: AL32UTF8
Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1)
PL/SQL Developer - Version 10.0.5.1710

Server: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
NLS_CHARACTERSET: AL32UTF8

 

[Marco Kalter]

Can you go to Help > Support Info, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?

 

[Koderiz]

any progress/ recommendations in this ??? Have exactly the same issue.
By default have AMERICAN_AMERICA.BLT8CP921 client nls_lang, but setting to AMERICAN_AMERICA.AL32UTF8 (DB setting) don't helps.

PL/SQL Developer
Version 10.0.5.1710
Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1)

Character Sets
Character size: 4 byte(s)
CharSetID: 0
NCharSetID: 1
Unicode Support: False
NLS_LANG: AMERICAN_AMERICA.BLT8CP921
NLS_NCHAR_CHARACTERSET: AL16UTF16
NLS_CHARACTERSET: AL32UTF8

 

[T-Gergely]

I wouldn't hold my breath.
Neither the developer(s?) nor the users seem to care much.

 

[armand]

Thanks T-G. My PSD is also hanging a lot while working with XML. I was a heavy PSD user 5-10 years ago and haven't used it much for the past few years. Now I doing some Oracle development again and pretty disappointed. I hope AAA isn't just milking the efforts of yesteryear instead of reinvesting. Lucky for them Dell and Quest did just that with TOAD.

 

[Philippe Malera]

Same problem for me.
In an SQL window, when I select an XMLTYPE column stored in binary, window freeze.
No problem is XMLTYPE is stored as CLOB.

 

[T-Gergely]

AFAIK the developer(s) aren't interested in fixing this. Even though I proposed solutions that are simple and do work. The related bugs were reported for years, even for PSD 11 Beta.

 

[S_G]

We have come across this issue also but what it was not happening to all.

After some investigation we were able to replicate the error on a machine that was not receiving the value error. The difference between the two was the client used by PL/SQL Developer to connect to the database.

The machine that had no issues was using an Oracle 10g (10.1.0.5) client.
The machine that had issues was using an Oracle 11g (11.2.0.1) client.

The below code snippet replicates the error if you switch clients. This can be done by changing the homes in the preferences.

CREATE TABLE test_table1 (xmltext XMLTYPE)
XMLTYPE COLUMN xmltext STORE AS CLOB;

INSERT INTO test_table1 VALUES
(xmltype ('<dataset><data>hello</data></dataset>'));

COMMIT;

-- 10G Client Works ok
-- 11G Client Works ok
SELECT * FROM test_table1;

CREATE TABLE test_table2 (xmltext XMLTYPE)
XMLTYPE COLUMN xmltext STORE AS BINARY XML;

INSERT INTO test_table2 VALUES
(xmltype ('<dataset><data>hello</data></dataset>'));

COMMIT;

-- 10G Client Works ok
-- 11G <Value Error>
SELECT * FROM test_table2;

Hopefully this will give information to users to get the past the and to the developers to resolve it.

EDIT: I have been told that the Oracle 11.2.0.4 client does not have the

 

[T-Gergely]

Alas, the developer(s?) don't care at all.

And I don't think this workaround will help with the misused OCI NULL indicators and OCIObjectFree(). Or is that fixed in PSD 11? (I won't test new version until they claim these bugs are fixed.) E.g. what happens if you insert null, then you execute that select twice?

 

[S_G]

E.g. what happens if you insert null, then you execute that select twice?

As in this ? If so no issues on 10g/11g client.

CREATE TABLE test_table2 (xmltext XMLTYPE)
XMLTYPE COLUMN xmltext STORE AS BINARY XML;

INSERT INTO test_table2 VALUES (null);
INSERT INTO test_table2 VALUES (xmltype(null));

COMMIT;

-- 10G No issues
-- 11G No issues
SELECT * FROM test_table2;
SELECT * FROM test_table2;

 

[T-Gergely]

That's right. But issue the select in a SQL Window twice, not in a Command Window.

Also, select a few hundred rows where xmltest is not null. Are there no nulls in the grid?

 

[S_G]

All the code blocks I have placed above have been run in a SQL window.

Do you have a type in your second sentence? Select not nulls and see if null appear?

 

[T-Gergely]

Drat, I still had to install PSD 11 to see what you see.
OK, there's no access violation/value error with that old OCI client for these testcases. But the OCIDefineByPos() problem still remains.

Do you mean a typo? I didn't remember that you couldn't see the problem in the grid directly. Try viewing the result of the 7th row. When I click on the "..." button at the , Large Data Editor comes up empty.

 

[Roeland]

Regarding the issue of the 7th row: XMLElement sometimes empty in SQL Window

 

[T-Gergely]

Yeah, this is an old bug. I know exactly that it's caused by not using OCIDefineByPos()/OCIDefineObject() correctly as explained in my linked post and in the official Oracle Documentation. It could have been fixed in a few seconds if Allround cared.

 

[S_G]

create table test_table2 (xmltext xmltype)
xmltype column xmltext store as binary xml;

declare

  v_num number := 0;

begin

  while v_num <= 100

  loop

    insert into test_table2 values
    (xmltype ('<dataset><data>hello</data></dataset>'));

    v_num := v_num + 1;

  end loop;

  commit;

end;

select * from test_table2;

Ran above code. 7th row is blank.

 

[bcleaver]

It's nice to see I wasn't the only one with this problem. I gave up on looking for a solution, slapped a view on top of the table for ad-hoc queries, and then moved on.

It looks like T-G has basically done the bug-fix for them, but it's way beyond my comprehension level, though he made it clear the problem was with the way the OCI was being used. S_G found the problem was version specific.

I found that I'm running an 11.2.0.4.0 client, but with an old 11.1 version OCI. I downloaded the 11.2.0.4 OCI from OTN and replaced the files in my client BIN directory.

This has resolved the issue for me. Thank you all for your help!