Re: Time SQL Script Part Two

I think you can put a " ! " in front of the os commands, then "exit" to return to the sql plus process. I know it works in line mode.

Joe Condle wrote:

> First let me thnak everyone that answered my post. They all help me
> alot. The problem I am
> now having is that I get the elapsed time in a bind variable but I can
> not set a script variable to its value. What my ultimate goal is to
> calaculate the time it took to run the script. If that time is longer
> than five minutes I then want to use the mailx commnad in Solaris to
> mail the appropiate people. The problem is I can not get the shell side
> of the script to talk to the sqlplus side. Is there away to execute an
> OS command from PL/SQL? I hve looked in every book I have and searched
> the net looking for shell/plsql script examples.
>
> Here is my script so far.
>
> #!/bin/sh
> sqlplus esp/esp >/dev/null <<EOF
>
> --@@sqlplus_settings.sql
> WHENEVER OSERROR EXIT 99
> WHENEVER SQLERROR EXIT 99
>
> SET TERMOUT ON
> SET FEEDBACK ON
> SET HEADING ON
> SET COLSEP ' '
> SET LINESIZE 120
> SET TRIMSPOOL ON
> SET PAGESIZE 57
> SET NEWPAGE 0
> SET SERVEROUTPUT ON
>
> variable start_time NUMBER;
> variable stop_time NUMBER;
> variable all_time NUMBER;
>
> DECLARE
> start_time BINARY_INTEGER;
> stop_time BINARY_INTEGER;
>
> BEGIN
> :start_time := DBMS_UTILITY.GET_TIME;
> DBMS_OUTPUT.PUT_LINE (:start_time);
> END;
> .
> /
>
> spool joe.tmp
> select * from map.deptcodes;
> spool off
>
> spool time.tmp
> DECLARE
> start_time BINARY_INTEGER;
> stop_time BINARY_INTEGER;
> all_time BINARY_INTEGER;
>
> BEGIN
> :stop_time := DBMS_UTILITY.GET_TIME;
> :all_time := (:stop_time - :start_time)/6000;
> DBMS_OUTPUT.PUT_LINE (stop_time - start_time);
> DBMS_OUTPUT.PUT_LINE (:start_time);
> DBMS_OUTPUT.PUT_LINE (:stop_time);
> DBMS_OUTPUT.PUT_LINE (:all_time);
> IF :all_time <= 1 THEN
> DBMS_OUTPUT.PUT_LINE ('Execution took longer than 5 Min');
> # Want to execute the followng here
> /usr/bin/mailx -s "TEST SUBJECT" condle_at_mars-systems.com <
> /ufs/00/MPI/v15.1/ora_esps/timeout.msg
>
> END IF;
>
> END;
> .
> /
> A=JOE
> echo JOE
>
> print start_time
> print stop_time
> spool off
> exit
> echo JOE
>
> Joseph P. Condle Jr.
> Senior Systems Engineer
> condle_at_mars-systems.com
> Medical ARchival Systems, Inc. (MARS)
> 3500 Victoria Street Pgh. Pa. 15261 USA
> Office 412-383-1379
> Fax 412-647-9661
> Pager 412-572-9490
Received on Wed Apr 07 1999 - 19:04:11 CDT