Creating a file using sql plus or pl/sql

[megatron33]

If i have a table holding 3,000 records, How can I create three files called file
A,B and C each olding a thousand recorrds using sql plus or pl/sql.

 

[carp]

I would use the UTL_FILE utility to write out the files.
You will also need two loops:

DECLARE
CURSOR record_cursor IS
SELECT * FROM my_table WHERE rownum < 3001; -- 3000 records
v_file utl_file.file_type;
v_record my_table%ROWTYPE;

BEGIN

OPEN record_cursor;

FOR i IN 1..3 LOOP
v_file_name := 'file_'||i||'.dat'; --file name is file_1.dat, file_2.dat or file_3.dat
v_file := utl_file.fopen('c:\file_path',v_file_name,'W');
FOR j IN 1..1000 LOOP
fetch record_cursor INTO v_record;
utl_file.put_line(v_file,v_record.field1||','||......||','||v_record.fieldn);
END LOOP;
utl_file.fclose(v_file);
END LOOP;
END;

This should give you three comma-delimited files (although I haven't actually run this - you may have to debug it).

 

[carp]

Oops. Forgot to close the cursor:

.
.
.
END LOOP;
close record_cursor;
END;