SQL & PL/SQL

HI All,

i'm using a 10g r2 database and i'm trying to output quite a large amount of data into a txt file using utl_file as opposed to spooling.

i've read that in 9i the line limit was increased to 32k, i'm not sure what that is in realworld terms.

I have a procedure as follows.


CREATE OR REPLACE PROCEDURE SP00UTLFILETEST
(p_filedir IN VARCHAR2, p_filename IN VARCHAR2, in_value IN NUMBER)

IS
v_filehandle utl_file.file_type;


CURSOR c_cur IS
SELECT
...... 500 preceding columns
nvl(col_n,0) AS col_n,
nvl(col_n,0) AS col_n
FROM
table
WHERE item = in_value;



BEGIN

v_filehandle := utl_file.fopen (p_filedir, p_filename, 'w');

FOR r_cur IN c_cur LOOP

utl_file.putf(v_filehandle,
...... 500 preceding columns
r_cur.col_n||'|'||
r_cur.col_n);

END LOOP;

utl_file.fclose(v_filehandle);

EXCEPTION
WHEN UTL_FILE.WRITE_ERROR THEN

DBMS_OUTPUT.PUT_LINE('WRITE ERROR HAS OCCURED');

END;


Could anyone tell me if its purely coz i'm trying to write data from so many columns thats causing the issue or is there a way around it.

I'm struggling to find an answer on the web as i can find many instructions on how to do this method but not much on its limitations.

Thanks in advance

G