Output From PL/SQL

This is our code:
Procedure WRITE_ORPHANED_CHILD is
CURSOR ORPHANED_cur IS
SELECT c.id FROM cat c
WHERE parent_count=0
AND NOT EXISTS (SELECT 'X'
FROM store_root_Cat src
WHERE src.cat_id=c.id);

begin
for c in fran loop
UTL_FILE.PUT_LINE ( Orphaned_Child_info, to_number(c.id);
end loop;
end;

Firstly does it look correct? We want to create a cursor based on a record_count field not being updated by the count and display them.

Secondly, I am contemplating using the spool command too? I do know that UTL uses the parameter in the init file, how different are these two utilities? and which was is better for me to allow developers to run this code and print it to a file?

Thanks,
Nirasha

I don't see code for opening and closing the file.

With UTL_FILE you write to the database server. With the Forms/Reports Built-in TEXT_IO you can write to your client, but only from Forms or Reports.

If you want a simple report for your developers, spooling from SQL*Plus seems the most appropriate.

If I am right, there are two fundamental differences in both
the approaches:

UTL writes the information during run-time that is it is written as these statements are encountered where as spooling waits for the entire program execution to complete and then writes to a file.

Happy coding !!!

I prefer UTL_FILE. It is much better to use than spooling using SQLPLUS.

Hope this code helps:

DECLARE
*** utl_file declarations
p_file_name VARCHAR2(20);
p_file_dir VARCHAR2(30);
v_file_handle UTL_FILE.file_type;


BEGIN

p_file_name := 'file_name.dat';
p_file_dir := '/xxx/xxx/utl_file_dir';
v_file_handle := UTL_FILE.fopen (p_file_dir, p_file_name, 'w');

**** Inside the cursor loop you can dump a record to the file by UTL_FILE.put_line (
v_file_handle,variable || ',' || variable);

***** After so many records, you can flush the records to clear memory

UTL_FILE.fflush (v_file_handle_log);

***** After the cursor loops, close the file and you are done.
UTL_FILE.fclose (v_file_handle);

END