Re: Long To Varchar2

From: Klaus Zeuch <KZeuch_at_hotmail.com>
Date: Wed, 21 Feb 2001 22:27:09 +0100
Message-ID: <971bv1$anq$06$1@news.t-online.com>

In my opinion it can only done by using dbms_sql. Example (not tested, not compiled!):

create or replace function my_function (v_id in number, v_size in integer) return varchar2
is
-- v_size = number of characters to retrieve

v_cur integer;
v_rc integer;
v_buffer varchar2(4000);
v_buf_len integer;

begin
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur,'select long_col from table_long where id = ' || to_char(v_id),dbms_sql.v7);
dbms_sql.define_column_long(v_cur,1);
v_rc := dbms_sql.execute_and_fetch(v_cur); dbms_sql.column_value_long(v_cur,1,v_size,0,v_buffer,v_buf_len); dbms_sql.close_cursor(v_cur);
return substr(v_buffer,1,v_buf_len);
end;

"Ray" <ray.dowling_at_portbris.com.au> schrieb im Newsbeitrag news:pVpk6.2372$v4.100832_at_ozemail.com.au...
> Creating a data warehouse using data extraction - Oracle d/b to Oracle
 d/b.
> Some data is in a table is in Long datatype - need to extract and insert
 in
> varchar2 column.
>
> Would like to use one select statement eg
>
> Insert into xxxx (varchar_data, ...
> Select substr(long_data,1,100) from xxxx => get error
>
> Any thoughts on creating a function convert long to varchar2 (input
> long_data output varchar2??
>
>
>
>
Received on Wed Feb 21 2001 - 15:27:09 CST