I am on Toad 25.2 an since ages running into the same problem when showing tables that contain a XMLTYPE column in a data grid. It seems that Toad converts every single XMLTYPE using GetClobVal although it is not even displaying the data in the grid (it only shows "ORAXML"). During that conversion which can take ages Toad is frozen. Then I have to decide to go and fetch a coffee or fire up task manager to kill Toad. Although I like coffee and I like coffee often this is most of the times very annoying
It would be really great if there was an option in the data grid settings to disable fetching/converting the XMLTYPE data by default and only fetch it when a cell has been double clicked and opened in the popup editor.
Toad does not convert XMLTYPE to CLOB, but it is fetching them if you selected them.
I am assuming that you are referring to the "Cast" option in Options -> Data grids -> Data. That only happens if you are using an older client where XMLTYPE is not supported. In these cases, Toad adds that GetClobVal to your SQL and Oracle does that conversion, not Toad. (Notice right above that option there is another that says "Use native XMLTYPE when possible". If you see ORAXML in your grids, then that is the native XMLTYPE). It might be worth a try to uncheck "use native XMLTYPE", I'm not sure.
Once that option is unchecked, you could play with these options to see if it behaves better. The DEFERRED one does what you were asking for.
I have checked "Deferred LOB read" and also "use native XMLTYPE". But as soon as I click on the data tab of such a table I see the session doing this for a long time:
SQL_ID fxr1rdjdp6jg5, child number 0
DECLARE XML SYS.XMLTYPE := :1; BEGIN :2 := XML.GetClobVal; END;
If I wait five minutes it shows "ORAXML" in the cells.
Now I have unchecked "use native XMLTYPE", opened a new Schema Browser and now it works fast and I see "CLOB" in the cells.
That is great, thanks!
Although for me the problem is solved, should it not be the other way around?
with the 'Use native XMLTYPE' option unchecked, Toad parses your SQL and injects GetClobVal into your SQL so that Oracle returns Clob instead of XMLTYPE.
OK, If I turn on Deferred Lob Read I see that plsql block run for each visible cell in the grid.
It's deferring the reading of the XML data from the initial fetch, but running that pl/sql block for each XML cell right after that. It's not much of deferral. That option is kind of a stink-bomb, if you ask me. I'll investigate to see if I can make it defer until you double-click in the cell.
Have you tried it with "Deferred" unchecked, and "Native" checked? I think that will be fast too, and then Toad won't be modifying your SQL under the covers.
I have discovered that if you connect to the DB with the "Connect using Oracle client" box unchecked, Toad does not run the DECLARE XML SYS.XMLTYPE := :1; BEGIN :2 := XML.GetClobVal; END; block at all, even when Native is checked.
When the no-client option was initially introduced, it had a few problems but now I think it is usually better than using an Oracle client. Update: No client is faster than client, but not as fast as unchecking Native.
Here is my blurb about no-client mode:
Toad's no-client option
Uncheck the "Use Oracle Client" box on the login window to connect to the database without an Oracle client (the box is only enabled when there are no connections in Toad).
Pros of no-client option:
No setup required
It is a bit faster than using a client.
No issues with XMLTYPE
Supported: LDAP, TNSNames.ora, TNS_ADMIN environment variable, Oracle Encryption and Data Integrity, Spool SQL, SSH, TCPS, IPV6, External authentication, and Oracle Wallet connections.
Can connect to every database version since 7.3!
Can connect to very old and very new database versions at the same time.
Cons of no-client option:
Not supported: Oracle client style tracing/logging, proxy connections, Kerberos
If you don't have an Oracle client installed, of course you won't have their tools like SQL*Plus, DataPump, Import/Export, but if you have a client installed Toad can still use these tools, even if you run Toad in no-client mode.
Thanks for the follow up John. To be honest I have never considered that option and would not have expected any impact on the XMLTYPE grid performance. But currently I have over 200 stored connections and although I will probably not need all of them in the future I am just to lazy to set them up again.
Currently I am totally happy with unchecking the "Native" mode
If you have any very complicated queries that select XMLTYPE columns, that option may cause a SQL error. I think we've fixed all of the problems there but I wouldn't be surprised if we missed something. Anyway, if that happens, please let me know and I'll fix. A workaround in that case would be to temporarily re-check Native.
Thought about that again. What is the XMLTYPE to CLOB cast for at all? Does Toad need it to show the XMLTYPE content or can it show XMLTYPE as text on its own?
Maybe there should be a separate option to defer the XMLTYPE to CLOB cast specifically... or not doing it at all automatically as long as it is not shown as text?
I just installed Toad 2026R2. When I opened the connection window the first time I saw that I was able to uncheck the "use Oracle client" checkbox, so I did that.
"Use native XMLTYPE" is checked and "deferred LOB read" is checked but it still is super slow on XMLTYPE columns and session browser shows
DECLARE XML SYS.XMLTYPE := :1; BEGIN :2 := XML.GetClobVal; END;
So for me the no oracle client option does not make any difference.
If Native option is checked, the SQL for the data tab looks like the below, without GetClobVal in it, but that plsql block that you brought to my attention earlier is executed once for every XML value returned by the SQL. It's slow because there are a lot of SQLs fired, and it's a lot of round trips across the network.
If you have Native Option is unchecked, then the schema browser writes a SQL like this tables with XMLTYPE columns. It's fast because the conversion happens all at once on the database side, and it does not result in extra round trips across the network:
This happens in the Editor too, even if you rewrite your SQL like "select * from my_table_with_xmltype". Toad will detect the XMLTYPE and rewrite the SQL to use GetClobVal.
The reason for the cast to CLOB is because some Oracle clients don't handle XMLTYPE well when in Unicode-enabled mode, which is how Toad uses the Oracle client. Earlier, I thought the PL/SQL block was only happening when you were using an Oracle client, but I see now that it happens even in no-client mode. I am not sure why, but I will try to find out, because as you've found it's really inefficient.
OK. The question is why do you need the GetClobVal at all if it only displays CLOB or XMLTYPE. It is enough to fetch the String representation on the fly if the user opens the cell.
We only display CLOB or XMLTYPE in the grid because this fields tend to be really big.
If you really want to see the values in the grid you can check Options -> Data Grids -> Data -> Preview CLOB and LONG data (I know, maybe it should also say "XMLTYPE" here)
Delaying the fetch of that data until you double-click would be helpful, but that's not how it currently works.
