0

I'm on a remote server without privileges to create a directory and I have a clob column (a xml code) that I want to see. As I'm using a very old version of PL/SQL_developer (8.0.4) and I can't update to a new one, with a single "select X from T", I get "CLOB" as result. So, searching on the Internet I found this in the AskTOM and I try to use the plsql solution

declare
my_var varchar2(32000 char); --tried with long, didn't work too.
begin 
for x in ( SELECT X from T) 
loop
    my_var := dbms_lob.substr( x.X, 32000, 1 );
    dbms_output.put_line(my_var);
end loop;
end;

But when i try to run, I have "ORA-20000 ORU-10027 buffer overflow limit of 10000 bytes".

I try to increase the limit with DBMS_OUTPUT.ENABLE(32000); but got error too "ORA-06502: PL/SQL: numeric or value error: character string buffer too small", I only can decrease the limit of 10000.

I know I don't have the SET serveroutput ON, but when I tried to add this line, guess what, error: "ORA-00922: missing or invalid option" but if I put 4000 instead of 32000 it works, show the first 4000b of data, so, I don't need this line.

So, I can't print, since the variable is too big, and I can't write the text to a file, since I don't have privilegies, there is any other way to see that variable?

Alessandro Melo
  • 39
  • 7
  • Did you double click on SQL developer's clob result? Does it show an editor icon which pops up a window that could show the clob's contents. – Kaushik Nayak Jul 04 '18 at 11:57
  • Did you try first output data in chunks, second spool to file? eg. 'spool file.txt' look at this and sub links: https://stackoverflow.com/questions/1576478/print-clob-content-out-as-is-in-oracle-sql-script – jareeq Jul 04 '18 at 12:30
  • There isn't a version 8.anything of Oracle's SQL Developer; do you perhaps mean Allround Automations' PL/SQL Developer? – Alex Poole Jul 04 '18 at 13:04
  • Kaushik, when I click in the icon on CLOB result, give me 2 errors: "The configuration registry key could not be read" and "Access violation at address XXX in module plsqldev.exe" Looks like the program is trying to alocate the result in a variable out of the memory range reserved for it. I will try the jareeq solution and say something if it works. Alex, the version is 8.0.4.1415, It's PL/SQL Developer, not Oracle, sorry for wrong tag. Question updated. – Alessandro Melo Jul 04 '18 at 13:34
  • I have also faced the similar issue-"ORA-20000 ORU-10027 buffer overflow limit of 10000 bytes".. try:- `SET serveroutput ON size unlimited` and set the buffer size to NULL `DBMS_OUTPUT.ENABLE (buffer_size => NULL);` – Atul Kr Dey Jul 04 '18 at 22:41

0 Answers0