Home > IT Answers > Oracle > How to convert LONG to CLOB or to VARCHAR typ...
SamG
15 pts.
Q:
How to convert LONG to CLOB or to VARCHAR type in select statement
Oracle, Long, CLOB, DataType, Conversion, PL/SQL, SQL
Hi, I need to convert LONG to CLOB or to VARCHAR type, to be able to apply either dbms_lob.substr() or just SUBSTR() function to this conversion result. Unfortunately Oracle function to_lob() converts LONG value in the column long_column to LOB values in the select list of a subquery in an INSERT or UPDATE statement O_N_L_Y. It doesn't work in straight select statement. I tested it, and also see http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions185.htm.

Is there a SQL function (beside to_lob) that converts LONG to CLOB? Or may be there is a PL/SQL package having similar function in it?

Thank you,

ASKED: Oct 30 2009  8:00 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
Mrdenny
49385 pts.
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • Bookmark and Share
Have you tried to_string()?
Last Answered: Oct 30 2009  10:29 PM GMT by Mrdenny   49385 pts.
  •   
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

SamG   15 pts.  |   Oct 30 2009  11:23PM GMT

Thank you, Mrdenny,
However to_string() is not an Oracle function.
ORA-00904: “TO_STRING”: invalid identifier

 

Carlosdl   32725 pts.  |   Oct 30 2009  11:26PM GMT

to_string doesn’t exist in Oracle.

I have never done that, but maybe you could use the COLUMN_VALUE_LONG procedure of the dbms_sql package.

Here are a couple of examples that might be helpful:

askTom - Conversion/Extraction of Longs
askTom - Long to Varchar2 conversion