How to convert LONG to CLOB or to VARCHAR type in select statement
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,
Software/Hardware used:
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,
Software/Hardware used:
ASKED:
October 30, 2009 8:00 PM
UPDATED:
April 19, 2011 7:27 PM
Answer Wiki:
Have you tried to_string()?
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you, Mrdenny,
However to_string() is not an Oracle function.
ORA-00904: “TO_STRING”: invalid identifier
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
The given function helps you to select,search local as well as remote table with long text
create or replace
function long_column( p_query in varchar2,
p_owner in varchar2,
p_owner_value in varchar2,
p_name in varchar2,
p_value in varchar2 )
return clob
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val clob;
l_long_piece clob;
l_long_len number;
l_buflen number := 32760;
l_curpos number := 0;
l_return_value number;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );dbms_sql.bind_variable( l_cursor,p_owner, p_owner_value );
dbms_sql.bind_variable( l_cursor,p_name, p_value );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
l_long_val, l_long_len );
l_curpos := l_curpos + l_long_len;
l_return_value := nvl(l_return_value,0) + l_long_len;
exit when l_long_len = 0;
l_long_piece := l_long_piece||l_long_val; -- added
end loop;
end if;
dbms_sql.close_cursor(l_cursor); -- added
return l_long_piece;
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end long_column;
To select the remote long table:
select owner,view_name, text_length,
',long_column( 'select text
from all_views
where owner=:owner and view_name =
':owner',owner,':x', view_name ) ll
from all_views
To search remote long column:
select * from (
',select owner,view_name, text_length,
long_column( 'select text from all_views@deblink where owner=:owner and
view_name =
':owner',owner,':x', view_name ) ll
from all_views@dblink
)
where ll like '%test%'
This function can be used to replicate remote table with long column:
create table long_t as
',select owner,view_name, text_length,
long_column( 'select text from all_views@dblink where owner=:owner and
view_name =
':owner',owner,':x', view_name ) ll
from all_views@dblink