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

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 06 Apr 1999 17:46:27 GMT
Message-ID: <371180ae.10927497@netnews.worldnet.att.net>

On Mon, 05 Apr 1999 15:52:02 -0400, Joe Condle <condle_at_mars-systems.com> wrote:

On Mon, 05 Apr 1999 15:52:02 -0400, in
comp.databases.oracle.misc you wrote:

>When using a PL/SQL block
>in sqlplus how are variable passed.?

Use the SQL*Plus VARIABLE command to declare a bind variable. You should be able to do something like this:

variable start_time varchar2(8)
variable end_time varchar2(8)

begin

	--note the colon that must preceed start_time
	:start_time := to_char(sysdate,'hh:mi:ss');

end;
/

...place your sql script here

begin

	--Now you may reference start_time from
	--this block as well, because it's a SQL*Plus
	--mind variable.
	:end_time := to_char(...
	
	--Do something heer to calculate the elapsed time

end;
/

SQL*Plus bind variables cannot be type date, so you need to use either varchar2 or number. You should still be able to make that work though.

Jonathan Received on Tue Apr 06 1999 - 12:46:27 CDT