Control output from PL/SQL

Usually when I write a PL/SQL script and schedule it, there are certain things I want to generate and record into a log file.   I usually do that using a pipe command.   Assuming my program is in a file called comp.sql, I would then execute:


sqlplus scott/tiger@orcl comp.sql >> comp.log

Within the comp.sql, I will start with the following commands:

SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000

I usually like to declare at least one date variables:

    vdate         DATE;


After the Begin, I like to place the following command:

    DBMS_OUTPUT.DISABLE;
    DBMS_OUTPUT.ENABLE(1000000);

    vdate := SYSDATE;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
Towards the end, I like to know how long the script runs by adding the following command:

    DBMS_OUTPUT.PUT_LINE('Elapse time is ' || ((sysdate-vdate)*24*60) ||' minutes');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));

So the script would look something like this:

SET TIMING ON
SET SERVEROUTPUT ON SIZE 1000000

Declare

    vdate         DATE;

    .........

Begin

    DBMS_OUTPUT.DISABLE;
    DBMS_OUTPUT.ENABLE(1000000);

    vdate := SYSDATE;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));

    .........

    DBMS_OUTPUT.PUT_LINE('Elapse time is ' || ((sysdate-vdate)*24*60) ||' minutes');
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));

End;
/