SQLPlus updating a text log file after procedure is executed

Question

I have a batch (.bat) file with the below details that is automatically opened at 2 pm through the windows task scheduler.

Below are the contents of the Proc1.bat file:

@echo off
echo PROC 1 started at = %time% %date%>> C:\Users\username\Desktop\log.txt
sqlplus username/password@db @C:\Users\username\Desktop\procedure1.sql

below are the contents of the procedure1.sql file:

Exec schema1.procedure1;
prompt PROC1 Completed

Through the echo in the batch file I am able to update the log file with the start time and through the prompt in the sql file i am able to tell that the procedure completed on the sqlplus screen However, I need a way to update the end time "after" the procedure completes into the same log.txt file.

I do not have access to edit the stored procedure, i can only call it. I have only read access to the Oracle schema and tables, i cannot make any changes procedure or other changes. Please help me out with a way to update the log.txt file with "Proc1 executed successfully at hh:mm", "after" the procedure is completed.

I have spent hours and hours online trying to find a suitable result, but an unable to find any. Finally turning to the stack overflow experts for resolution!

Answer 1

Here is what I did to get the result:

I changed the contents of the SQL file to:

SET PAGESIZE 0
SET FEED OFF
EXEC schema1.procedure1;
SPOOL C:\Users\username\Desktop\log.txt APPEND
SELECT 'PROC 1 COMPLETED AT:', TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS.FF') FROM DUAL
SPOOL OFF;
EXIT;

This appends a new line into the log.txt file that says:

PROC 1 COMPLETED AT: 08/30/2023 13:35:59.480663

So, the .BAT file adds the procedure start time into the log file and the .SQL file adds the procedure completed 'end time' into the log file.

This helps in creating a 'log file' that captures the start time and end time for all the scripts/batch files that are scheduled to run, which helps in identifying whether the procedures are executed on a daily basis or not.

FYI, I have assigned the scripts to run as tasks through the task scheduler application in Windows.