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

monsri_at_my-deja.com wrote:
>
> 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.

See Sybrand's post but if you need to output more than 255 chars, then wrap it up in a loop...

x := my_large_plsql_var;
while x is not null loop
  dbms_output.put_line(substr(x,1,80));
  x := substr(x,81);
end loop;

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse