Re: How to convert LONG to VARCHAR2????

A copy of this was sent to "Manuel Dias" <manueld_at_burotica.pt> (if that email address didn't require changing) On Thu, 17 Feb 2000 10:09:27 -0000, you wrote:

>Hello,
>
>How can I convert a LONG to VARCHAR2 data,
>so I can use standard string functions (SUBSTR,etc)
>on it?
>
>Thanks for any hints.
>Manuel Dias
>
>

is the string a LONG and is the long <32k? if so,

create or replace function long_substr( p_rowid in rowid, p_from, p_forbytes ) return varchars
is

   l_text long;
begin

   select thecolumn into l_text from t where rowid = p_rowid;    return substr( l_text, p_from, p_forbytes ); end;
/

and then you can

select long_substr( rowid, 1, 255 ) from T;

(note: you need to code long_substr for each table that has a long as it directly queries the table in the function).

If you want to convert it to a varchar2 permanently (eg: it is always less then 2000 characters in 7.x or 4000 charcaters in 8.x) you can code:

declare

   l_str varchar2(4000);
begin
  for x in ( select rowid rid, long_column from t loop )

     l_str := substr( x.long_column, 1, 4000 );
     update t set new_varchar2_column = l_str where rowid = x.rid;

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation