Toad World® Forums

XML display issue


#1

I’m running Toad version 10.1.1.8, and have an issue with running the following in the editor (using a 11.1.0.6.0 oracle database):

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

INSERT INTO test_table1
VALUES (xmltype (‘hello’));

COMMIT;

SELECT * FROM test_table1;

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

INSERT INTO test_table2
VALUES (xmltype (‘hello’));

COMMIT;

SELECT * FROM test_table2;

All works fine, except the last select. It throws the error:

ora-21500: internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]

Only difference is the storage type of the xml.

This works fine in Toad 9.7, and sql developer, sql*plus, etc.

Any ideas what may cause this? Thanks.


#2

Toad supports unicode starting with Toad 10. One of the changes we had to make
is to use the Unicode-enabled OCI methods. Unfortunately, the OCI has a few bugs
with Unicode. This is one of them.

You can work around it like this:

SELECT T.XMLTEXT.GetClobVal() as XMLTEXT

FROM TEST_TABLE2 T

This workaround will be automatic in Toad 10.5’s schema browser.
You’ll still have to code it manually in the editor though.

John


#3

One of my coworkers can no longer debug his packages and procedures after
upgrade to oracle 10.2.0.4.3

TOAD help says that the minimal requirement to debug is:

You must have the DEBUG CONNECT SESSION privilege, or Oracle will not let you
use DBMS_DEBUG.

I am not aware of any privilege or role named DEBUG. Please advise.


#4

image001.png


#5

Hi George,

I am not aware of any privilege or role named DEBUG. Please advise.

you need to :

GRANT DEBUG CONNECT SESSION TO your_user;

The privilege is “debug connect session”

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#6

Thanks. But running that query, even though there’s now no error, just produces a blank (null) cell. The query returns data ok through the other clients I’ve tried.


#7

When I run that query, I see a cell with in it. If I double-click
it, then I see the data.

If I go to Options -> Data Grids -> Data, and check “Preview CLOB
and LONG data”, then I see the first 20 characters or so followed by
’.…’ and then I can again double-click to see the rest.


#8

I had that option checked.

When unchecked I see the (HUGECLOB) and I can double click and see the data in the popup editor.

But when it’s checked then it’s a null value.

However, if I also check the Deferred LOB read box then it displays as I would expect. Any ideas what this option is doing? Thanks.


#9

‘Deferred Lob Read’ is supposed to delay fetches of LOB values until
you scroll to them in the grid. If there is more than a screen full of
data, it will reduce initial fetch time. But it should not cause what you are
seeing. What Oracle client version do you have? I am not sure offhand if
this is a factor, but it is worth checking. I am using an 11g client, and
I’m not seeing null regardless of the option settings.


#10

Thanks. It’s the latest 11g database I have installed on my laptop, not just the client. 11.1.0.6.0 for windows.


#11

Actually Oracle has a 11.1.0.7 patch – so you’re close to latest J


#12

Mine is actually 11.1.0.1, so I don’t think this is related to client
version.


#13

Toad Version 10.5
Hi, is this the same explanation as to why I am getting (HUGECLOB) displayed in my xmltype column, when I try to view the contents of my table via the Schema Browser ? If I try to click on HUGECLOB, it displays my xml in one line only.

If i do a select * from

, the data grid displays my xmltype data in a formatted manner and easily readable.

I’ve tried the options the others mentioned in this thread, which doesn’t really fix how its displayed in the schema browser…


#14

Some Oracle clients would crash when you look at XML Data. So I made a change
in the schema browser to always return XML as CLOBS by changing the query using
the GetClobVal function.

Later, we learned that 11g clients do not have the problem. So I made a
change in the 10.5 patch (and current beta) to stop using GetClobVal if you have
an 11g client.

Short version – get the latest Toad patch and this will go away.


#15

Thanks, just got the patch the other day. It worked.


#16

I have not been able to get this working for a couple of months and have had to revert back to TOAD 9.7.2. I have installed TOAD 10.5 and I have Oracle 11.1.0.6 client on my desktop. I still get the OCI error.


#17

I reread the thread, and tried it again. I am able to reproduce the problem
even with the latest 10.5 patch and an 11g client. I should have been more
thorough before I replied the first time. Sorry about that.

I also tried it with the beta – it gets a little better there – I
only get an error one time in the beta, and after that, it works! I’m
not sure what is going on, but I think the problem is coming from our 3rd party
Oracle access components. I will send your test case to them and see what
happens.


#18

I opened a case with our 3rd party component developer, they are saying that it
is a bug in the OCI, which happens for XMLTYPEs with binary storage and OCI in
the Unicode mode (in 9.7 and prior, there was no Unicode).

So I’m afraid all I can tell you is what you already know – you can
work around it in 10.x by not using the binary storage options for XMLTYPE.


#19

SOLVED: Yo tambien tuve ese problema y despues de revisar el tema y varios foros encontre que algunas tblas en las cuales tenia campos xmltype, sucedia el error y en otras no.

revise y detecte que las tablas que no habia error tenian la sig declarción:

ALTER TABLE WF_USUARIO
ADD (COBERTURA XMLTYPE);

y las que no tenian error:

ALTER TABLE WF_USUARIO
ADD (COBERTURA SYS.XMLTYPE);

entonces, recree el campo con la definicion completa a sys.xmltype y toad funciono correctamente

espero les sirva

saludos