How to set "set long 25000" within PL/SQL

From: Poorna Prakash <poorna_prakash_at_sabre.com>
Date: 1998/01/15
Message-ID: <34BE979C.7924@sabre.com>#1/1

Hello,

I am trying to search for a pattern within a LONG column, by extracting data from the LONG column into a VARCHAR2 column using PL/SQL. Thanks to Sandy Thrasher and Shajith, T.V, for suggesting me to take this path.

The problem I am encountering is, the length of the LONG column is 25000 and the "instr" function fails, if I try within a procedure, whereas it works if I do not declare as a procedure, like a declare .... begin ... end;

The reason I am trying to use a procedure is not to hard code the string, but to pass as a parameter to a procedure. Can anyone suggest me how to make "set long 2500" have effect within PL/SQL. I feel that is what is causing the instr function not to work within a procedure. Please find below two versions of the programs, one which works, and the other which dosen't

Program that works;

---

set long 25000
set feedback off
declare

  trig_owner    varchar2(30);
  trig_name     varchar2(30);
  pattern       varchar2(30) := 'CUST';
  trig_action   varchar2(25000);

   cursor trig_cursor is
    select owner,trigger_name,trigger_body     from all_triggers;
begin
  open trig_cursor;
  loop

    fetch trig_cursor into
          trig_owner,
          trig_name,
          trig_action;

    exit when trig_cursor%notfound;
    if instr(upper(trig_action),pattern) != 0 then       dbms_output.put_line(rpad(trig_owner,30,' ')|| ' '|| rpad(trig_name,30,' ')||' '||'Trigger');

    end if;
   end loop;
   close trig_cursor;
end;
/

Same as the Above program but within a Procedure which does not work:

---

set long 25000
set serveroutput on size 50000
create or replace procedure search_trigger_body as declare

  trig_owner    varchar2(30);
  trig_name     varchar2(30);
  pattern       varchar2(30) := 'CUST';
  trig_action   varchar2(25000);

  first_pass boolean := TRUE;
  cursor trig_cursor is
    select owner,trigger_name,trigger_body     from all_triggers;
begin
  open trig_cursor;
  loop

    fetch trig_cursor into
          trig_owner,
          trig_name,
          trig_action;

    exit when trig_cursor%notfound;
    if instr(upper(trig_action),pattern) != 0 then       dbms_output.put_line(rpad(trig_owner,30,' ')|| ' '|| rpad(trig_name,30,' ')||' '||'Trigger');

    end if;
   end loop;
   close trig_cursor;
end;
/

execute search_trigger_body;

If someone has time please try by creating this procedure by changing the tablename to known value .
Thanks for all your response.

-Prakash Received on Thu Jan 15 1998 - 00:00:00 CST