Oracle: 11.2.0.4 on Windows 2012 server. 64 bit. Cloud.
Oracle client: 11.2.0.1 64 bit.
I have a table with one row. There is an XML column in the table. Selecting a count(*) returns 1 as expected.
Running select * from the table hangs toad for quite some time, the throbber is throbbing and if I cancel the query and it will eventually cancel but displays an error dialog containing “ORA-21527: internal OMS driver error”.
If I don’t cancel the query, it will eventually return, but there are no results and the same error dialogue appears.
The table is the usual mix of VARCHAR2, NUMBER, there’s a CLOB and one of these:
I can easily access the data from SQL*Plus on the server and on my local desktop too, so it looks to be a Toad problem, I think. There’s almost nothing on “My Oracle (lack of) Support” other than a fix in Oracle 10g for something that is not related, but gave the same error code.
I have a trace file which I ran to trace the problem, and surprisingly enough, it doesn’t actually report the error - which Toad does. There’s nothing in the trace at all, and the FETCH shows that one row was retrieved, but it never arrived at Toad. The trace file has no confidential or sensitive information in it, so I can supply it if required.
The OCI in Unicode-enabled mode doesn’t play nice with XMLTYPE, especially if it’s got binary storage. The best way around it is to use GetClobVal() when you want to select XMLTYPE in Toad, like this:
select t.my_xml_column.GetClobVal() as my_xml_column
from my_table t
I don’t know why, but you have to use a table alias or GetClobVal() won’t work.
SB-Tables-RHS-Data will automatically use GetClobVal with XMLTYPE, but if you are in the Editor, you have to add that yourself.
Oracle: 11.2.0.4 on Windows 2012 server. 64 bit. Cloud.
Oracle client: 11.2.0.1 64 bit.
Norm!
I see that Dell has a KB article about this error. It says (rather definitively) that you need to upgrade your client to match the point release of the DB. I'm less definitive, but I've seen issues like this in 10.1 where the client is point releases behind the server.
Just something to consider, although I'd be inclined to go with John's answer myself. And upgrading a client point release is way more painful than it should be anyway...
In older versions of Toad, before binary XML storage came out, and before Toad was Unicode-enabled, XMLTYPE still had some problems in Toad, and was very picky about client versions. I don’t think that matters much anymore past version 11 or so. I think GetClobVal will be enough in this case.
John’s workaround of using getClobVal() on the XML column does indeed work, although I need to do a bit more typing in the editor when I want to select * from … (or use F4 to get the full list of columns).
Thankfully, there are only about 19 columns in this particular table.
Rich: I’m unable to get at the Dell information, it tells me that I’m not under maintenance. It has done this for a number of years now. Occasionally, it gets fixed, but then just as a new Toad Beta comes out, it barfs on me again. I think they are trying to tell me something!
And upgrading a client point release is way more painful than it should be anyway...
I think they are trying to tell me something!