How to time a SQL script and return the time in a variable

From: Joe Condle <condle_at_mars-systems.com>
Date: Mon, 05 Apr 1999 15:52:02 -0400
Message-ID: <37091462.97F512A6@mars-systems.com>

I am running Oracle version 8 for solaris. All I want to do is time an sql script. After the script completes I would like to get the time it took to run and then use it in a if statement to mail me if it took longer than a specified time. I am relatively new to this. The following is what I have come up with so far. I want to get the starttime and subtract it from the end time. When using a PL/SQL block in sqlplus how are variable passed.? The part between the dotted lines is the original script before I started experimenting with the PL/SQL block to add the timing.

sqlplus user/user >/dev/null <<EOF

DECLARE
  CURSOR c_time IS
  SELECT sysdate
  FROM dual;

  v_time c_time%ROWTYPE;
  starttime DATE;

BEGIN
  OPEN c_time;
  FETCH c_time INTO v_time;

  if c_time%FOUND THEN
    starttime := sysdate;
  end if;

---

  spool JOE.tmp
  SELECT v.patienttype, v.unit, v.domain, v.dischargedate, v.admitdate,

         p.lname||', '||p.fname||' '||p.mname AS name, p.mrn,v.attdocname
  FROM visit v, patient p
  WHERE p.acctid = v.acctid
  AND p.source = v.source
  AND trunc(v.dischargedate) = trunc(sysdate-1)

  AND   v.domain IN ( 'PUH', 'SSH', 'BVH', 'SMH' )
  AND   v.patienttype IN ('I', 'S')
  AND   v.attdocno IN ( '039175','001356','010900',

'019457','002062','001236' )
  ORDER BY domain, patienttype, attdocname, name;   spool off

---

  spool JOEtime.tmp
  SELECT sysdate-starttime FROM DUAL;
  spool off
END;
.
/

--
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 Received on Mon Apr 05 1999 - 14:52:02 CDT