Home » SQL & PL/SQL » SQL & PL/SQL » SQL/XML and LONG columns - getting ORA-00932
SQL/XML and LONG columns - getting ORA-00932 [message #187655] Mon, 14 August 2006 16:44 Go to next message
nmerritt
Messages: 2
Registered: August 2006
Location: San Diego
Junior Member
I am running Oracle 9.2.0.6.0 over an old database containing some LONG columns. I want to extract data using SQL/XML functions (i.e. select xmlelement(...)) but if I try to use xmlelement() on a LONG column for some reason I get "ORA-00932: inconsistent datatypes: expected NUMBER got LONG". This makes no sense to me, since the column is a LONG column; why would Oracle expect a NUMBER?? Is there some issue with taking xmlelement() of a LONG? Any workaround?
Re: SQL/XML and LONG columns - getting ORA-00932 [message #187715 is a reply to message #187655] Tue, 15 August 2006 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Basically, XMLELEMENT doesn't support LONGS.
Other than replacing your LONG columns with CLOBs (always worth doing anyway), I can't think of an easy way round it.
Re: SQL/XML and LONG columns - getting ORA-00932 [message #187717 is a reply to message #187715] Tue, 15 August 2006 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All is not lost....
I dug up this function I posted some months back that uses some very iffy Oracle weak typing to convert LONGs to CLOBs. It'll impose a fairly high overhead on the system.

SQL> create table test_long (col_1 varchar2(10), col_2 number, col_3 long);

Table created.

SQL> CREATE OR REPLACE function long_to_clob(p_id rowid) return clob is
  2    l_long long;
  3  begin
  4    SELECT col_3
  5    INTO   l_long
  6    FROM   test_long
  7    WHERE  rowid = p_id;
  8    return l_long;
  9  exception
 10    when no_data_found then return null;
 11    when too_many_rows then return null;
 12  end;
 13  /

Function created.

SQL> insert into test_long values ('A',23,'ACFDFEERrwerwerwe');

1 row created.

SQL> select xmlelement("col_3" ,long_to_clob(t.rowid)) from test_long t;

XMLELEMENT("COL_3",LONG_TO_CLOB(T.ROWID))
----------------------------------------------------------------------------------
<col_3>ACFDFEERrwerwerwe</col_3>
Re: SQL/XML and LONG columns - getting ORA-00932 [message #188264 is a reply to message #187717] Thu, 17 August 2006 12:42 Go to previous message
nmerritt
Messages: 2
Registered: August 2006
Location: San Diego
Junior Member
Thanks for the help / workaround. I ended up doing as you originally suggested, i.e. altering the LONG columns to CLOB.
It can be done quite easily/quickly even on a very large table, although I found that this left some indexes in an unusable state so I had to rebuild those also. Even so, its a better approach. It helps that OCI programs can retrieve CLOB columns as though they were LONG columns; such programs don't have to be rewritten to use LOB locators and LOB-specific functions, which is nice.
Previous Topic: Can a function out parameter be a pl/sql table
Next Topic: Missing output parameters values after using RAISE
Goto Forum:
  


Current Time: Thu Dec 08 06:33:41 CST 2016

Total time taken to generate the page: 0.15035 seconds