Hangs on any query on table with XMLtype column

[Sri Gopalakrishna]

Hi,

We have several tables with columns of type XMLtype - storing XML data.

Just today, PL/SQL begins to hang when I Right click Query Data on any of these XML tables. All other tables are fine. This happens only on the Prod Database.

Also against all other databases, the same query returns all values.

I have rebooted PC - tried on several PC's with PL/SQL Dev - all same results.

We have checked with DBA to ensure no locks on tables. We have done everything short of rebooting the Prod database.

Queries in command line on this table work fine - just the query in PL/SQL Developer window that shows the XML data. No other tools show the XML data.

Any help will be useful - since this is a Production related problem.

 

[Marco Kalter]

Did this previously work correctly on the same production database? If so, what has changed? Did you upgrade a client or server, or apply a patch? Did you shutdown and restart the database instance since the error occurred?

 

[Sri Gopalakrishna]

To the best of our knowledge nothing changed. We have restarted the Production DB server. Another DB instance on the same machine (Training) works fine.

This is rather critical since I cannot see any of the XML columns for Production support.

 

[Sri Gopalakrishna]

The DBA and sysadm has confirmed that nothing changed on the Prod Database.

 

[Marco Kalter]

Maybe you have changed the client side then? Something must have changed, since the behavior changed. Can you go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?

Note that you can probably workaround this by using the xmltype.getclobval() function to query the xml text as a clob instead of an xmltype instance.

 

[Sri Gopalakrishna]

Just emailed you the information

 

[karthik]

Hi Marc,

I am facing the problem of the PL/SQL Developer hangs when the query involves XMLType column sometimes XML features of oracle9i.I copied all the pages. There is nothing changed in the server. Created a new table with the xmltype column and another table with a clob column.

 

[karthik]

Hi Marco,

I have mailed the information
-------------------------------------------------
Can you go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail reply?
-------------------------------------------------

 

[Viliam Durina]

This query reliably fails with PL/SQL Developer 6.0.6.947 and oracle client 9.2:

select xmlelement(dummy) from dual;

The same query with Oracle Instant Client 10.2 causes following error in SQL Window:
OCI-21500: internal error code, arguments: [58], [], [], [], [], [], [], []
OCI-21500: internal error code, arguments: [kghfrh:ds], [0x7CA01C4], [], [], [], [], [], []

but after clicking OK continues with no problem. In Command Windows nothing appears...

Probably it is a bug of OCI, but couldn't you do something with it?

 

[Marco Kalter]

We'll check it out.

 

[Quasar]

Hi.

I have exactly the same problem.
I mainly work on an application that deals with a lot of XML so you can imagine how frustrated I am.
I have to kill plsql developer process through task manager to unlock the soft because I just get an "executing..." in the status bar but it never completes.
This morning I was able to work duting a few hours but now PL/SQL Developer hangs all the time each time I want to query data from a table that has an XMLTYPE column.
I usually violently kill PL/SQL Developer around 20 times per day, very annoying and a waste of time.
I will follow this thread to see if you find out a solution.

 

[Quasar]

Hi.

Problem fixed.

It seems that one of the (numerous) Oracle tools was not installed on my PC.
This tool named Oracle 9i Application Server has been installed under the directory ORA9IDS on my C drive.
I then let PL/SQL Developer deals automatically with
the Oracle Home and the OCI Library and everything works well.
I may now read/update XMLType columns and PL/SQL Developer does not freeze anymore.
You guys at Allroundautomations do not have an easy job.
It must be difficult to develop a tool which has so many dependencies with such a complex third-party tool like Oracle.
BTW, PL/SQL Developer is a very nice tool.

Have fun.

 

[Marco Kalter]

Okay, thanks for letting us know.