This is our code:
Procedure WRITE_ORPHANED_CHILD is
CURSOR ORPHANED_cur IS
SELECT c.id FROM cat c
AND NOT EXISTS (SELECT 'X'
FROM store_root_Cat src
for c in fran loop
UTL_FILE.PUT_LINE ( Orphaned_Child_info, to_number(c.id);
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?
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
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:
*** utl_file declarations
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
***** After the cursor loops, close the file and you are done.