Link to home
Get AccessLog in
Avatar of Steve A
Steve AFlag for United States of America

asked on

Passing output results to log file within Oracle procedure

Hello,
I have been searching for an example of how to pass the output results into a log file or inserting into a log table.
How do you capture the output being created?
Example - delete from employee table where employee_status = '4';
Output results - '34 rows deleted'

I want to capture the output result - '34 rows deleted' and either put this in a log file or insert into a log table.

This would be done within a Oracle procedure only.  (Oracle 11g)
I have the procedure written and works fine, just that I would like to add additional code (utl_file or anything else)
to capture these output results and insert these into a log file or table.

Thanks,
MG
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>This would be done within a Oracle procedure only.  (Oracle 11g)

See my comment below about sqlplus output.

>>Output results - '34 rows deleted'

That looks like sqlplus output.  You can 'capture' that with the spool command.

example:
spool myfile.txt
delete from employee table where employee_status = '4';
spool off

Then myfile.txt will have everything echoed to the screen.  You would need to parse it for the exact messages you want.

>>just that I would like to add additional code (utl_file or anything else)  to capture these output results and insert these into a log file or table.

If the procedure actually displays the output above, you have the text to display, just insert it wherever you want or as you suggest, use utl_file to write it to disk.

To provide anything more, I'm afraid we'll need more information and maybe the procedure you have so we can see what it is doing.
Avatar of Steve A

ASKER

Thanks for the information and reply.
I went ahead and created an example of a procedure created and this
would then be executed.
when I execute the procedure, I want the output results for each command to
show up in a log file.  I got only one utl_file command within but is commented out.
don't have any put_line's in yet or any other syntax necessary.

Thanks

CREATE OR REPLACE PROCEDURE InsertNewAutoParts
       
AS
  l_input           Utl_File.File_Type;
  v_dir             varchar2(50) := 'AutoParts';
  v_filename        varchar2(50) := 'AutoLogFile.txt';
  v_output          varchar2(2000);

BEGIN

  --Open the file...
  --l_input := UTL_FILE.FOPEN( v_dir, v_filename, 'rw' );

        --remove all the existing records first...
        delete from auto_documents where source_system_nm like 'Auto%';
        commit;
       
        delete from auto_Config where source_system_nm like 'Auto%';
        commit;
       
        delete from auto_parts where source_system_nm like 'Auto%';
        commit;

        --insert new auto parts records...
        execute immediate 'truncate table auto_parts';
       
        insert into parts
        select * from auto.parts@prod1;
       
        commit;
       
        --insert new auto documents records...        
        execute immediate 'truncate table auto_documents';
       
        insert into auto_documents
        select * from auto.documents@prod1;
       
        commit;

        --insert new auto config records...        
        execute immediate 'truncate table auto_Config';
       
        Insert Into auto_Config
        select * from auto.config@prod1;
       
        commit;

     
end;  --end of procedure...


---output results...
--I want the log file to show the output results for each command within the procedure
--or somehow to capture the output and then write it in a log file.
--where if I open the log file, it would basically show the information below after executing the proc.

450 rows deleted

commit completed

135 rows deleted

commit completed

60 rows deleted

commit completed

table auto_parts truncated

482 rows inserted

commit completed

table auto_documents truncated

154 rows inserted

commit completed

table auto_config truncated

88 rows inserted

commit completed
Why delete from the table then truncate it?  Seems like a waste of resources.


Which do you prefer, inserting into a log table or writing to a file?

There are TONS of examples using utl_file to write to a file so I'm not sure what the problem is.

After the delete you can get the number of rows with SQL%ROWCOUNT:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#CIHJJJDG
Avatar of Steve A

ASKER

yeah, the proc was just an example and not all the logic is shown for the delete commands on what certain records are removed.  Anyways...

that sql%rowcount might work and will try it out.

the log file would be better to use or have created.

thanks
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Steve A

ASKER

Thanks for the example, this is just what I needed, as that sql%rowcount will work.