1

How to extract data from LONG datatype field using only SQL (without using PL/SQL)? Getting error while concatenating with other columns-

ORA00932: inconsistent datatypes

DB: Oracle 8i enterprise edition

Matt
  • 14,906
  • 27
  • 99
  • 149
Soumya
  • 19
  • 4
  • Since this is an Oracle problem, what is preventing you from using PLSQL here, as [this article describes](http://www.techonthenet.com/oracle/questions/long_value.php) ? – Tim Biegeleisen Jan 05 '16 at 10:18
  • Business constraints.. I am not allowed to use PL/SQL – Soumya Jan 05 '16 at 10:20
  • So are you asking for a general SQL value to extract the value of the `LONG` ? Can you update your question with what is the exact use case here? Is this a one time need, or do you foresee having to do this often? – Tim Biegeleisen Jan 05 '16 at 10:34
  • Yes.. Any predefined function or any sql statement that can extract the Value... – Soumya Jan 05 '16 at 10:36
  • I don't think this is possible with your ancient and unsupported Oracle version. –  Jan 05 '16 at 11:10

1 Answers1

0

There is a trick using XML:

SELECT
    long_column long_column_as_clob
FROM
  XMLTABLE(
    'ROWSET/ROW'
    PASSING
      XMLTYPE(
        DBMS_XMLGEN.GETXML(
          Q'{SELECT long_column FROM your_table}'
        )
      )
    COLUMNS
      long_column CLOB PATH 'LONG_COLUMN'
  );
Husqvik
  • 5,669
  • 1
  • 19
  • 29
  • This will not work in Oracle 8i enterprise edition. `XMLTABLE` was introduced in Oracle 10 R2. – San Jan 05 '16 at 11:05
  • Oops, sorry, haven't noticed the version. XML table is not needed you can extract the data from the XML using other functions too but if `DBMS_XMLGEN.GETXML` is not available in Oracle 8i then you have a problem. – Husqvik Jan 05 '16 at 11:10