Re: How can I DBMS_OUTPUT a VARCHAR2(32767) variable ?

Just use ordinary sql*plus to accomplish this set long <anything larger than 32k>
rem as trg_body is a long not a varchar
column trigger_name new_line
select trigger_name

        , trg_body
from dba_triggers
where table_name = upper('&1')
/

One of the drawbacks of PL/SQL is many people don't bother to learn sql*plus anymore, and it is such a nice tool.

Hth,

Sybrand Bakker, Oracle DBA

<monsri_at_my-deja.com> wrote in message news:8jib78$9mo$1_at_nnrp1.deja.com...
> Hi,
> The following code doesn't work:
> 1 declare
> 2 trg_body VARCHAR2(32767);
> 3 begin
> 4 for trg_names in (select trigger_name
> 5 from dba_triggers
> 6 where table_name = upper('&1'))
> 7 loop
> 8 select trigger_body into trg_body
> 9 from dba_triggers a
> 10 where a.trigger_name = trg_names.trigger_name;
> 11
> 12 dbms_output.put_line('CREATE OR REPLACE'||
> lower(trg_names.trigger_name)||' IS');
> 13 dbms_output.put(trg_body);
> 14 end loop;
> 15* end;
>
> I get the message:
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at line "dbms_output.put(trg_body);"
>
> Is there a way of outputting the contents of my 32K-big variable ?
>
> Thanks !
>
> Regards,
> Seb
>
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Jun 30 2000 - 00:00:00 CDT