Unable to see Oracle XML output in Data Grid

Toad for Oracle & DevOps Toolkit
1

Hello Everyone,

Am unable to see the output in data grid when i run simple oracle xml query. output showing as blank.

SELECT XMLELEMENT (
"PaymentInput",
xmlattributes ( ('PURCHASE') AS "type", '2' AS "version")
)
AS auth_xml
FROM DUAL;

2

which version of Oracle and Toad are you using?

Toad had problems displaying XMLTYPE in versions before 13.3.

As a workaround, you can do this:
SELECT XMLELEMENT ("PaymentInput",xmlattributes ( ('PURCHASE') AS "type", '2' AS "version")).GetClobVal() AS auth_xml
FROM DUAL;

3

Thanks for the quick response. i have one more question my teammates using same version of oracle toad what i am using ..but they are getting the output.

4

two thoughts:

  1. Compare your Oracle client version to that of your teammate.
  2. do you see data when you double-click in the cell?
5

both are using same oracle client version and when they double click on the cell in the new window they can see output.

6

Do you see data when you double-click?

7

with your work around i see data...

8

Once again verified with my team people they are using oracle 10g client version and toad version 9.7.2 version but my team people can see the data with out your work around. am using same version but am seeing data with your workaround.

Thank you.

9

Oh wow that is a really old Toad version. I don't think Toad had any special handling of XMLTYPE back then.

If everyone has same version of Toad, as well as Oracle client and server, the only thing that I can think of is maybe there is some grid setting at play here. Maybe Toad Options -> Data Grids -> Data -> Preview CLOB and LONG data?

10

This option is unchecked(Data -> Preview CLOB and LONG data) for all of us..

11

Are you logging into the database as the same user? Also, have you been running with F5 or F9? I assume F9. That is the preferred way unless you need to run a script with multiple statements.

I can't think of any other option that might apply. I just tried it with 9.7.2 on a 10g database and saw (ORAXML) in the grid, then when double-clicked I see data.

BTW, (ORAXML) indicates that data is present in an XMLTYPE field. (OraXML) indicates null in an XMLTYPE field. I don't know how, with version 9.7.2, you would see actual data there w/o double-clicking. I also can't think of a way that you'd see a blank cell....at least not with this SQL that you've provided.

12

we are using same DB user account. when am running the sql xml which i provided using F9 , in the data grid i see ORAXML , when i double click a new window is opening i see blank data. but same way my team is seeing data. image

13

Ok. Maybe it's something in the popup editor. I'll take a look at that tomorrow.

Edit:

I don't see anything in the popup editor code to explain why it's blank for you.

You could try a fresh set of Toad settings and see if that helps. To do that in Toad 9.7:

  1. While Toad is not running, go to C:\Users\YOUR_WINDOWS_USERNAME\AppData\Quest Software\Toad for Oracle\9.7
  2. Rename the User Files subfolder to "User Files Save" or something.
  3. Restart Toad. It will look as if it is freshly installed. See if the problem still happens.

If problem goes away, you can copy some of the settings over from "User Files Save" to the new "User Files" folder. I am guessing that the most important ones to you will be
Connections.*
SavedSQL.dat
ToadActions.dat
Any subfolders named after TNSNames aliases

If there is something else that you'd like to copy across, let me know.