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
MachinegunnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

slightwv (䄆 Netminder) Commented:
>>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.
MachinegunnerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MachinegunnerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Here is a quick example.  All the UTL_FILE stuff is pretty much straight from the online docs:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS72681

You will need to create the database directory MYDIR or change the example to use one you have already created.

drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
insert into tab1 values('a');
insert into tab1 values('a');
insert into tab1 values('b');
commit;


declare
	F1 UTL_FILE.FILE_TYPE; 
begin
	F1 := UTL_FILE.FOPEN('MYDIR','mylog.log','W'); 

	delete from tab1 where col1='a';
	UTL_FILE.PUT_LINE(F1,'Rows deleted from tab1 for ''a'': ' || to_char(sql%rowcount,'fm9,999,999'));
	
	UTL_FILE.FCLOSE(F1);

end;
/

Open in new window

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
MachinegunnerAuthor Commented:
Thanks for the example, this is just what I needed, as that sql%rowcount will work.
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.