saving contents of dbms_output.put_line into a file.

I have the following pl/sql block to be run in unix, and the contents of dbms_output.put_line to be saved
to a file.

The script finds the tables and the number of rows in each table.

*************************************
set serveroutput on
declare
    row_cnt number;
begin
    for x in (select table_name
                from user_tables
               order by table_name) loop
        execute immediate
            'select count(*) from '
            ||x.table_name into row_cnt;
        dbms_output.put_line(rpad(x.table_name,30)||lpad(to_char(row_cnt),7)); --output to be saved to a file
    end loop;
end;
*************************************

It's sql program; spooling is simple:

$SQLPLUS <<!EOF
spool $file
select .........where.....
spool off
!EOF

However, how do I spool the contents of dbms_output.put_line in a pl/sql block
gram77Asked:
Who is Participating?
 
Jinesh KamdarConnect With a Mentor Commented:
Execute the SET SERVEROUT ON command on SQLPLUS before you fire the SPOOL file and then you should be able to get that output in the file as well.
0
 
MilleniumaireCommented:
Instead of using dbms_output you could use utl_file:

utl_file.fopen()
utl_file.put_line()
utl_file.fclose()
0
 
MilleniumaireConnect With a Mentor Commented:
With dbms_output output will only appear in the spooled file once the block has completed, so you won't be able to see the progress of your sql until the very end when all output will suddenly appear.

utl_file writes output to the standard output buffer, but this buffer can be flushed using utl_file.fflush() so that it appears instantly in the file.  This way you can monitor the growing file (if required) and see how far it has got.

The downside to using utl_file is that you will need to use the create directory command to define which directory on your os the files will be written to.  This may require a DBA to set this up if you don't have the necessary privileges.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jinesh KamdarConnect With a Mentor Commented:
Also, UTL_FILE would work only on the DB server-side and not the client-side, which is possible with SPOOL!
0
 
MilleniumaireCommented:
Jinesh is correct in his observation, however assuming you are running your script on unix then it is probably running on the DB server anyway so this shouldn't be an issue.
0
 
MilleniumaireCommented:
For future reference dbms_output.put_line is restricted to writing 255 characters per line and you are restricted by the size of the output buffer, however utl_file has no such restrictions.
Obviously in this case, line size and buffer size isn't an issue.
0
 
anandmahajanConnect With a Mentor Commented:
maybe you can create temp table and store dbms.output overe there and then just select from that table based on some id or timestamp
0
 
gram77Author Commented:
Milleniumaire:
Can you give me full script of utl_file to write to a file which I can use in my overall file.

Also please send the server parameter to change in the database
0
 
MilleniumaireConnect With a Mentor Commented:
Hi,
You can check out the Oracle 10g R2 overview of the utl_file package on Oracle Technology Network at http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref11789

To summarise its use, first you need to determine which directory the files will be created in and then issue the create directory statement:

CREATE OR REPLACE DIRECTORY TMP_DIR AS '/tmp';

The directory /tmp must already exist and the oracle unix account (usually oracle) must have permission to access it (read or write depending on what you are doing).  I've chosen /tmp as this usually gives read and write access to all accounts in a unix environment.

Then grant access to the required Oracle accounts to this new Oracle directory object:

GRANT READ, WRITE ON DIRECTORY SYS.TMP_DIR TO PUBLIC WITH GRANT OPTION

I've chosen PUBLIC so that all Oracle accounts can reference this directory from within Oracle, however, you might want to be more specific/secure.

Now that Oracle is aware of the existsance of a unix directory it can be used by the utl_file package.
Your plsql script can then call the relevant utl_file procedure as follows as shown in the following example:

DECLARE
  FileID       utl_file.file_type;
BEGIN
  FileID := utl_file.fopen('TMP_DIR','test.log','W');
  utl_file.put_line(FileID,'any text string required');
  utl_file.fflush(FileID);
  utl_file.fclose(FileID);
END;

There are a whole bunch of utl_file exceptions you might want to include to trap various errors that might occur while using utl_file.

0
All Courses

From novice to tech pro — start learning today.