Output From PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Output From PL/SQL

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    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

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    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.

  3. #3
    Join Date
    Jan 2001
    Posts
    26
    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 !!!

  4. #4
    Join Date
    Jan 2001
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width