Toad AV when selecting a table in the editor (possibly array fetch size related?)

Toad for Oracle - Beta
1

Hi,

When I select a table that is fairly hefty (it has some xml-as-clob cols and has 42 cols) in the editor, I get the following error:

“Access violation at address 000000001BD9EE64 in module ‘OraClient11.Dll’. Read of address 0000000000000010”

Then, when I try to run another query (eg. SELECT * FROM dual;), Toad hangs, and uses 0% CPU.

Selecting the table via the schema browser works fine.

I believe John D did some work in this area a while back, and I believe the intention was to make the array fetch size in the editor be calculated in the same way it was in the schema browser. If my memory is correct, then I think there’s a bug there. If not, I’ve gone blind, because I can’t find the option to manually change the size any more!

Thanks,

Dawn

2

That option was removed because we can now tweak the fetch size on the fly for each query. When CLOBs are involved that setting didn’t have any effect anyway - Oracle automatically fetches one row at a time when CLOBs are involved.

It sounds like the error you are having is due to the Unicode OCI’s problems with XMLTYPE. In the SB, we cast XML columns to CLOBs for you (hit the view/edit query button and you’ll see what I mean) because we query the column datatypes before we write
the query for the data tab. The Editor doesn’t have that luxury (and you might not want it rewriting your queries anyway).

If you take the query from the view/edit dialog of the SB-Tables-Data tab, and run it in the Editor, I think it will work just fine.

From: Boneist [bounce-Boneist@toadworld.com]

Sent: Tuesday, June 11, 2013 10:17 AM

To: toadoraclebeta@toadworld.com

Subject: [Toad for Oracle - Beta Discussion Forum] Toad AV when selecting a table in the editor (possibly array fetch size related?)

Hi,

When I select a table that is fairly hefty (it has some xml-as-clob cols and has 42 cols) in the editor, I get the following error:

“Access violation at address 000000001BD9EE64 in module ‘OraClient11.Dll’. Read of address 0000000000000010”

Then, when I try to run another query (eg. SELECT * FROM dual;), Toad hangs, and uses 0% CPU.

Selecting the table via the schema browser works fine.

I believe John D did some work in this area a while back, and I believe the intention was to make the array fetch size in the editor be calculated in the same way it was in the schema browser. If my memory is correct, then I think there’s a bug there. If
not, I’ve gone blind, because I can’t find the option to manually change the size any more!

Thanks,

Dawn

3

I’ve tested the sql copied over from the schema browser, and it does indeed work when run in the editor. If I remove one of the .getclobval’s and rerun, the error reappears.

What I do not understand, though, is that a very similar table (same columns, bar two non-xmltype columns, and with a heck of a lot more data) has no issue when run as select * from table_name in the editor!

… Ah, in trying to set up a test case, I think I’ve noticed where the problem might lie - in the table where I get the issue, the XMLTYPEs are stored as binary xml, not CLOB as I said originally (11g mindset, where are you?!). If I recreate the table with the XMLTYPE columns stored as CLOBs, I can select * from it in the editor without any problems.

Here is a simplified test case to reproduce the error:

create table test_table (col1 xmltype);

insert into test_table values (xmltype(‘1’));

commit;

select * from test_table; — error occurs

create table test_table2 (col1 xmltype)

XMLTYPE col1 STORE AS CLOB (

ENABLE STORAGE IN ROW);

insert into test_table2 values (xmltype(‘1’));

commit;

select * from test_table2; — returns row as expected

Is that likely to be a client issue, or is it Toad not handling the new binary XMLTYPE column very well? SQL*Plus works just fine when selecting from the test_table.

4

This is not about the amount of data. The OCI, when in Unicode mode, has some problems with Binary XML. That’s why you get the error in Toad, and that’s why the SB adds the GetClobVal. SQLP Plus does not use the OCI in Unicode mode, and that’s why it
does not produce the error.

From: Boneist [bounce-Boneist@toadworld.com]

Sent: Wednesday, June 12, 2013 12:50 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Toad AV when selecting a table in the editor (possibly array fetch size related?)

I’ve tested the sql copied over from the schema browser, and it does indeed work when run in the editor. If I remove one of the .getclobval’s and rerun, the error reappears.

What I do not understand, though, is that a very similar table (same columns, bar two non-xmltype columns, and with a heck of a lot more data) has no issue when run as select * from table_name in the editor!

… Ah, in trying to set up a test case, I think I’ve noticed where the problem might lie - in the table where I get the issue, the XMLTYPEs are stored as binary xml, not CLOB as I said originally (11g mindset, where are you?!). If I recreate the table
with the XMLTYPE columns stored as CLOBs, I can select * from it in the editor without any problems.

Here is a simplified test case to reproduce the error:

create table test_table (col1 xmltype);

insert into test_table values (xmltype(‘1’));

commit;

select * from test_table; — error occurs

create table test_table2 (col1 xmltype)

XMLTYPE col1 STORE AS CLOB (

ENABLE STORAGE IN ROW);

insert into test_table2 values (xmltype(‘1’));

commit;

select * from test_table2; — returns row as expected

Is that likely to be a client issue, or is it Toad not handling the new binary XMLTYPE column very well? SQL*Plus works just fine when selecting from the test_table.

5

Ok, so there’s no nice way to get Toad to gracefully handle things? Ie. a warning that the query contains xml-as-binary and will crash Toad unless you convert it to a clob?

Toad hangs if I try to do anything else, such as open a new editor tab etc, which means killing it and restarting.

6

Hm, maybe that is possible now that we are checking field types before doing any fetching. I will check into that.

From: Boneist [bounce-Boneist@toadworld.com]

Sent: Wednesday, June 12, 2013 6:10 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Toad AV when selecting a table in the editor (possibly array fetch size related?)

Ok, so there’s no nice way to get Toad to gracefully handle things? Ie. a warning that the query contains xml-as-binary and will crash Toad unless you convert it to a clob?

Toad hangs if I try to do anything else, such as open a new editor tab etc, which means killing it and restarting.

7

There are known issues with XMLTYPE with OCI when the Unicode flag has been enabled that have been discussed on the boards many times over the past few years.
I’m not 100% certain on the full impact of the issue, but it seems that some clients behave better than others. An instant client (10.2.0.5) is working fine for me, but my 11.2.0.1 fat client fails with AV. For me Toad is still usable after that point though.
Do you have another client to test with?

From: Boneist [mailto:bounce-Boneist@toadworld.com]

Sent: Wednesday, June 12, 2013 3:50 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Toad AV when selecting a table in the editor (possibly array fetch size related?)

I’ve tested the sql copied over from the schema browser, and it does indeed work when run in the editor. If I remove one of the .getclobval’s and rerun, the error reappears.

What I do not understand, though, is that a very similar table (same columns, bar two non-xmltype columns, and with a heck of a lot more data) has no issue when run as select * from table_name
in the editor!

… Ah, in trying to set up a test case, I think I’ve noticed where the problem might lie - in the table where I get the issue, the XMLTYPEs are stored as binary xml, not CLOB as I said originally
(11g mindset, where are you?!). If I recreate the table with the XMLTYPE columns stored as CLOBs, I can select * from it in the editor without any problems.

Here is a simplified test case to reproduce the error:

create table test_table (col1 xmltype);

insert into test_table values (xmltype(‘1’));

commit;

select * from test_table; — error occurs

create table test_table2 (col1 xmltype)

XMLTYPE col1 STORE AS CLOB (

ENABLE STORAGE IN ROW);

insert into test_table2 values (xmltype(‘1’));

commit;

select * from test_table2; — returns row as expected

Is that likely to be a client issue, or is it Toad not handling the new binary XMLTYPE column very well? SQL*Plus works just fine when selecting from the test_table.