3

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?

DilumN
  • 2,889
  • 6
  • 30
  • 44
Aaron
  • 61
  • 1
  • 9
  • when you say "pl/sql" do you mean you're running a pl/sql statement (eg. `begin ... end;`) or do you mean PL/SQL Developer, the IDE? – Boneist Apr 09 '15 at 11:03
  • Do you need to `commit` the data from the `insert` prior to performing the `select`? – MT0 Apr 09 '15 at 11:15
  • @MTO Readers do not block writers, and writers do not block readers. – Lalit Kumar B Apr 09 '15 at 11:20
  • @Boneist Within a standard sql window in the IDE. I have already done what Rusty said below as a workaround, but wondered if there was anything i was missing or doing wrong i.e. types etc. For now as its working and I don't nessesarly need to query the column i'll leave it as it is. Happy to try any other suggestions anyone has – Aaron Apr 09 '15 at 12:45
  • @Aaron - there is also a programming language called PL/SQL. Perhaps you've heard of it? ;) – William Robertson Nov 17 '15 at 13:13

2 Answers2

3

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.

ThinkJet
  • 6,725
  • 24
  • 33
0

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

Sevle
  • 3,109
  • 2
  • 19
  • 31
Rusty
  • 1,988
  • 10
  • 12
  • Yeah this is currently my work around. I would mark as the answer but i don't have enough reputation :( – Aaron Apr 09 '15 at 12:53
  • I use extractvalue functions to build insert statements using this column, so it's 100% needed, just not to any user rather another process. But i'll be sure to give the doumentation a read. Thanks @Rusty – Aaron Apr 09 '15 at 13:45