Question with INTO clause of SQLPLUS

From: Joe Condle <condle_at_mars-systems.com>
Date: Sat, 10 Apr 1999 09:38:27 -0400
Message-ID: <370F5453.CE794462@mars-systems.com>

I want to read sysdate into a bind variable.

First since I want the time in minutes I

        alter session set nls_date_format ='mi'; Second set bind variable

        variable time number;
  There isn't a date variable. I also tried varchar2

Third

        select sysdate into :time from dual;
   OUTPUT

	SYSDATE
        ---------
         10-APR-99

When I execute print time I get.

   OUTPUT

	      TIME
         ----------

The SQLPLUS manual says the syntax for the select statemnt on page 7-115 is

        select sysdate into time: from dual;
    Produces

	select sysdate into time: from dual
                    *
        ERROR at line 1:
        ORA-00905: missing keyword

I am thinking because there isn't a date bind variable, I must have to convert the date to number. If that is true then I will have to have a start date and end date both in minutes then subtract the two.

My goal is to use the time variable as a start time and then at some later time
execute

        select sysdate-time into elapsed_time: from dual. If the elapsed_time is greater than five minutes then I want to send mail to the DBA's warning ofa possible process that needs tuning.

--
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 Sat Apr 10 1999 - 08:38:27 CDT