Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> How to set "set long 25000" within PL/SQL
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);
fetch trig_cursor into trig_owner, trig_name, trig_action;
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);
fetch trig_cursor into trig_owner, trig_name, trig_action;
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