CODEWITHSUNDEEP
oracle-databaseplsqlwindows-7oracle11gplsqldeveloper
Aaron
Aaron

Reputation: 61

Selecting from table is causing pl/sql to hang

Anytime I insert into my table the insert runs fine, as expected.

I select select * from... from the table and it hangs forcing me to terminate the query, but only when my xml_file column is populated, if it isn't then it returns the results and stops executing.

If xml_file is populated <value_error> is present in both the xml_file and extract_file columns, whereas if it isn't <CLOB> is present in the extract_file column with the value present and also in the xml_file column with no values

Table: 

ID                  VARCHAR2(6),
  VERSION             NUMBER(3),
  XML_FILE            PUBLIC.XMLTYPE,
  MODIFIED_DATE       DATE,
  MODIFIED_BY         VARCHAR2(50),
  REVISION            VARCHAR2(50),
  EXTRACT_FILE        CLOB

We can see the values in sqlplus so they're being inserted correctly but PL/sql can't seem to view it. This was working a few weeks ago but since then we have moved from windows xp to 7 and with this upgrade came an upgraded pl/sql (version 7.1.1.1339). Not sure why this would cause the problems, any ideas?

Upvotes: 3

Views: 2260

Answers (2)

Rusty
Rusty

Reputation: 2138

XMLType takes time to parse values. It is normal. You probably have big XML values stored there. You can exclude this column from select (use explicit column names instead of *) and it will run faster. And it is always better to restrict returned result set with where clause.

And btw, there is nothing related to PL/SQL engine.

Update: Btw, another solution it so store XML as CLOB and convert into XML on fly when you need it. It;s better when you don't need to select from XML content itself using xpath. Read also more about storage options in 11g for XMLType: http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb01int.htm#BABECDCF

Upvotes: 0

ThinkJet
ThinkJet

Reputation: 6735

There are a bug in PL/SQL Developer or in Oracle Client libraries which appears while retrieving query results with XMLType fields stored as "Binary XML". I deal with this bug at least last 3 years.

This bug appears only in certain configurations. E.g. I fixed such situation last time by replacing OCI libraries in client by same libraries from 11.2.0.4 server distribution. So my suggestion is to install latest Oracle client version and experiment with it, or even, install client part from x86 version of Oracle Server.

Of course, there are workaround with changing storage type of column to slower CLOB or use XMLSerialize() function, but usage of such workaround in everyday development is annoying.

P.S. I didn't test this problem on latest version of PL/SQL Developer (11.0 released March 3, 2015), so another possible but not proven solution is to upgrade PL/SQL Developer to the latest version.

Upvotes: 3

Related Questions