How to convert LONG to CLOB or to VARCHAR type in select statement

15 pts.
Tags:
CLOB
Conversion
Long
Oracle
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,

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you tried to_string()?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SamG
    Thank you, Mrdenny, However to_string() is not an Oracle function. ORA-00904: "TO_STRING": invalid identifier
    15 pointsBadges:
    report
  • carlosdl
    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
    69,605 pointsBadges:
    report
  • Chandrasing
    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 = :x', ':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 = :x', ':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 = :x', ':owner',owner,':x', view_name ) ll from all_views@dblink
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following