RE: how to extract text from LONG field

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Wed, 14 Nov 2001 05:02:55 -0800
Message-ID: <F001.003C470F.20011114044025@fatcity.com>

Create a procedure and
use a cursor to pick the long column. You can now use functions in your code for the variable being populated with the long column. <FONT face=Courier color=#800000
size=2>declare   cursor c1 is    select trigger_name, trigger_body    from  
user_triggers;c1_rec c1%rowtype;begin   open c1;   loop   fetch c1 into c1_rec;  
exit when c1%notfound;        if
upper(c1_rec.trigger_body) like '%amar kumar padhi%' then          
dbms_output.put_line(c1_rec.trigger_name);        else          --
dbms_output.put_line(c1_rec.trigger_name);    null;        end
if;    end loop;    close
c1;end;/
 

rgds <FONT face=Courier
color=#000000 size=2>amar
 

  <FONT face="Times New Roman"
  size=2>-----Original Message-----From: Daiminger, Helmut   [mailto:Helmut.Daiminger_at_KirchGruppe.de]Sent: Wednesday, November   14, 2001 12:15 PMTo: Multiple recipients of list   ORACLE-LSubject: how to extract text from LONG   field
  Hi!
  Is there a way that I can extract the source code of a trigger   from dba_triggers? The problem is that the information is stored in a LONG   colum.
  SQLWKS> desc dba_triggers Column

  Name                    

  Null?    Type <FONT

  size=2>------------------------------ -------- ---- <FONT 
  size=2>OWNER                                   

  VARCHAR2(30) <FONT

  size=2>TRIGGER_NAME                            

  VARCHAR2(30) <FONT

  size=2>TRIGGER_TYPE                            

  VARCHAR2(16) <FONT

  size=2>TRIGGERING_EVENT                        

  VARCHAR2(216) <FONT

  size=2>TABLE_OWNER                             

  VARCHAR2(30) <FONT

  size=2>BASE_OBJECT_TYPE                        

  VARCHAR2(16) <FONT

  size=2>TABLE_NAME                              

  VARCHAR2(30) <FONT

  size=2>COLUMN_NAME                             

  VARCHAR2(4000) <FONT

  size=2>REFERENCING_NAMES                       

  VARCHAR2(128) <FONT

  size=2>WHEN_CLAUSE                             

  VARCHAR2(4000) <FONT

  size=2>STATUS                                  

  VARCHAR2(8) <FONT

  size=2>DESCRIPTION                             

  VARCHAR2(4000) <FONT

  size=2>ACTION_TYPE                             

  VARCHAR2(11) <FONT

  size=2>TRIGGER_BODY                            

  LONG
  select table_owner, table_name from dba_triggers   where trigger_body like '%T_JOURNAL%';   does not work, because the LONG field can't be searched with   LIKE. to_char conversion doesn't work either.   Any ideas?
  This is 8.1.7 on Sun Solaris.
  Thanks, Helmut Received on Wed Nov 14 2001 - 07:02:55 CST