Re: Data conversion question: LONG to VARCHAR

From: <markp7832_at_my-deja.com>
Date: Wed, 08 Dec 1999 14:23:06 GMT
Message-ID: <82lpk6$6hp$1@nnrp1.deja.com>

In article <384D9586.572BDB67_at_dallas.net>,   Steve Batzer <sbatzer_at_dallas.net> wrote:
> Hi all, I'm looking for a way to convert data stored as a LONG (turns
> out to be description text) into a VARCHAR. I was trying to find a
way
> to do a text search on the LONG, but haven't been successful. So, the
> next best thing is to convert the LONG to VARCHAR that I could search
> on.
>
> Any ideas about doing the conversion? Or, is there a way to do a text
> search on a LONG attribute?
>
> Thanks!
>

Steve, you can retrieve (fetch) long columns into varchar variables in pl/sql providing the length of the long does not exceed 32K. For most applications this will allow accessing all the data.

Here is some sample code that read the old reportwriter 1.1 tables and checked the contents a long column to see if it contained a to_date function (Y2K stuff).

set serveroutput on
declare

v_owner       system.srw__query.owner%type  ;
v_appid       system.srw__query.appid%type  ;
v_itemid      system.srw__query.itemid%type ;
v_query       varchar2(32767)               ;  <== hold long
v_short       varchar2(2000)                ;
v_report_name system.srw__report.report_name%type ;

--
cursor srwq is
select owner, appid, itemid, query
from system.srw__query
;
cursor srwr is
select report_name
  from system.srw__report
 where appid = v_appid
;
begin
open srwq ;
loop
  fetch srwq into v_owner, v_appid, v_itemid, v_query ;   exit when srwq%notfound ;
  if instr(v_query,'to_date') > 0 then

     open  srwr ;
     fetch srwr into v_report_name ;
     close srwr ;
     v_query :=  replace(v_query,chr(10),chr(32)) ;
     v_query :=  replace(v_query,'    ',chr(32)) ;
     v_query :=  replace(v_query,'  ',chr(32)) ;
     v_query :=  replace(v_query,'  ',chr(32)) ;
     v_short :=  substr(v_query,1,2000) ;
     insert into srw_todate values        <== work table I created
       ( v_owner, v_report_name, v_appid, v_short) ;
--     dbms_output.put_line('Owner = '||v_owner||' appid = '||
--                           v_appid||' v_itemid = '||v_itemid ) ;
--     dbms_output.put_line(v_short);
  end if   ;
end loop   ;
close srwq ;
commit     ;
end        ;

/
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 08 1999 - 08:23:06 CST