PL/SQL problem

I would like to read a table and save the results (bold) in a text file.


**** Here's my actual PL/SQL code :
CREATE OR REPLACE procedure generate_ldif
IS
cursor user_nt is
select usr_nt from ntusr where ...;
var_usr_nt ntusr.USR_NT%type;
begin
open user_nt;
loop
fetch user_nt into var_usr_nt,var_adr_lot;
exit when user_nt%NOTFOUND;

dbms_output.put_line('Username:'||trim(var_usr_nt));
dbms_output.put_line('...');
dbms_output.put_line('...');

end loop;
close user_nt;
end;
--------------------------------------------------------------------------------
**** Here's my SQL code who executes this PL/SQL code:
set feedback OFF
set pagesize 0
set linesize 200
set serveroutput ON;
SPOOL genLDIF.tmp
execute generate_ldif;
spool off;
exit
--------------------------------------------------------------------------------
**** Here's my batch file whoe executes the SQL :
sqlplus.exe usr/pwd@inst @Generate_LDIF.sql
--------------------------------------------------------------------------------
**** This error appears :
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 106
ORA-06512: at "SYS.DBMS_OUTPUT", line 65
ORA-06512: at "GESPER.GENERATE_LDIF", line 22
ORA-06512: at line 1
--------------------------------------------------------------------------------

-->
I would like to see the results in a text file (genLDIF.tmp), another way to proceed ?

I don't think that is a MySql problem, this is an oracle probleme

Moving....

use the pl/sql spool command.
spool genLDIF.tmp
.....
spool off
also you will need to sert your serveroutp size higher.
SET serveroutput ON SIZE 1000000

If you are working on the server itself you can also use the oracle utl_file package.

Thanks!
This command solves my problem :
set serveroutput ON SIZE 1000000;

You also may want to look at the utl_file Oracle supplied package that allows you to write files.

UTL_FILE create file on the server, not in the client folder...

And how can i write a blank line in the output buffer ?
---------------------------------------------------------
My code :

dbms_output.put_line('a');
dbms_output.put_line('b');
dbms_output.put_line(' ');
dbms_output.put_line('c');

-----------------------------------------------------
I would like to insert a blank line between 'b' and 'c' line (without using ASCII chr(10)&&chr(13) ...

Did dbms_output.put_line(' '); not work for you?

dbms_output.put_line(' '); does not work ...

Are you using spool filename.txt to write to a spool file?

as said in the beginning of this document :-p
**** Here's my SQL code who executes this PL/SQL code:
set feedback OFF
set pagesize 0
set linesize 200
set serveroutput ON;
SPOOL genLDIF.tmp
execute generate_ldif;
spool off;
exit

I don't get the point, you are executing a procedure from sql+ and spooling output on a file, right? But in this case a simple select, with formatting instruction is enough, the procedure (IMHO) would be better if used to save the file somewhere, passing path as a parameter.
Like this one I got on a forum:


Can also be easily scheduled as an DBMS_JOB