Time SQL Script Part Two

From: Joe Condle <condle_at_mars-systems.com>
Date: Wed, 07 Apr 1999 15:00:42 -0400
Message-ID: <370BAB5A.1ACF8641@mars-systems.com>

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 - 14:00:42 CDT