1

I am creating a dynamic query in a procedure and now want to see it through dbms_output.putline, but my query contains more than 255 characters.

How to view the query?

What are the alternates of dbms_output.putline?

F11
  • 3,703
  • 12
  • 49
  • 83
  • `dbms_output.putline` does not have a limit of 255 characters. Why do you think that? –  Dec 02 '12 at 08:18
  • dbms_output.put_line has limitation of 255 characters per line. – F11 Dec 02 '12 at 09:08

2 Answers2

4

There's a little bit of confusion going on.

In Oracle 9i dbms_output.put_line is limited to 255 characters. This restriction was removed in 10g and is similarly not present in Oracle 11g.

You have tagged your question , which means that you're limited to 32,767 bytes, the standard PL/SQL maximum.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • +1, I didn't know about the limit in 9i (that is soooo long ago) –  Dec 02 '12 at 09:18
  • I didn't know that the restriction had been lifted in 10g until I saw your comment and went looking :-). I don't really have occasion to print really long things. Thank you for fixing my fat fingered 1 @a_horse_with_no_name – Ben Dec 02 '12 at 09:22
  • I am getting this error in oracle 10g ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line... – F11 Dec 02 '12 at 09:39
2

try mess around something like 

create or replace procedure custom_output(in_string in varchar2 )
is 

   out_string_in long default in_string; 
   str_len number; 
   loop_count number default 0; 

begin 

   str_len := length(out_string_in);

   while loop_count < str_len
   loop 
      dbms_output.put_line( substr( out_string_in, loop_count +1, 255 ) ); 
      loop_count := loop_count +255; 
   end loop; 

end;

/
Alexander Tokarev
  • 1,000
  • 7
  • 16