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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MilleniumaireCommented:
Instead of using dbms_output you could use utl_file:

utl_file.fopen()
utl_file.put_line()
utl_file.fclose()
0
MilleniumaireCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jinesh KamdarCommented:
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
anandmahajanCommented:
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
MilleniumaireCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.