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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/16
Message-ID: <34bfbeac.244146333@inet16>#1/1

The problem isn't "set long" at all. set long affects SQL*Plus fetching of longs only and has no effect on pl/sql. the set long command tells sql*plus how much of a long column to display when it selects it out -- this is all.

I think the cause of your problem here is the fact that stored procedures execute with the BASE priveleges of the owner of the procedure, without roles enabled. Your procedure probably cannot see the trigger in question (where as the anonymous block can).

Try this:

SQL> select count(*) from all_triggers;

  COUNT(*)

---

       202

SQL> set role none;

Role set.

SQL> select count(*) from all_triggers;

  COUNT(*)

---

         5

SQL> So you see, without roles enabled, I see significantly fewer triggers. Try running your anonymous block again, then set role none; and run it once more. I bet the second time around it will perform just like the procedure does.

On Thu, 15 Jan 1998 17:11:24 -0600, Poorna Prakash <poorna_prakash_at_sabre.com> wrote:

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 16 1998 - 00:00:00 CST