RE: how to extract text from LONG field

[Amar Kumar Padhi]

Title: how to extract text from LONG field

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.

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
amar

 

-----Original Message-----
From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 14, 2001 12:15 PM
To: Multiple recipients of list ORACLE-L
Subject: 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
------------------------------ -------- ----
OWNER                                   VARCHAR2(30)
TRIGGER_NAME                            VARCHAR2(30)
TRIGGER_TYPE                            VARCHAR2(16)
TRIGGERING_EVENT                        VARCHAR2(216)
TABLE_OWNER                             VARCHAR2(30)
BASE_OBJECT_TYPE                        VARCHAR2(16)
TABLE_NAME                              VARCHAR2(30)
COLUMN_NAME                             VARCHAR2(4000)
REFERENCING_NAMES                       VARCHAR2(128)
WHEN_CLAUSE                             VARCHAR2(4000)
STATUS                                  VARCHAR2(8)
DESCRIPTION                             VARCHAR2(4000)
ACTION_TYPE                             VARCHAR2(11)
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