Home » SQL & PL/SQL » SQL & PL/SQL » How can I read a variable runtime in a stored proc?
How can I read a variable runtime in a stored proc? [message #650818] Wed, 04 May 2016 07:46 Go to next message
gouravhere
Messages: 1
Registered: May 2016
Junior Member
 
CREATE OR REPLACE PROCEDURE SLEEPTEST3

IS
  start_dts DATE := SYSDATE;
  curr_dts  DATE;
  I       NUMBER := 0;
  VAL CHAR(1);
BEGIN

  LOOP I := I + 1;
       curr_dts := sysdate;
       DBMS_OUTPUT.PUT_LINE('PLEASE TYPE Y TO EXIT');
       VAL := &VAL; #this particular line is throwing an error [Error] PLS-00103 (13: 15): PLS-00103: Encountered the symbol ";" when expecting one #of the following: ( - + case mod new not null <an identifier>
#   <a double-quoted delimited-identifier> <a bind variable>
#   continue avg c
  EXIT WHEN curr_dts > start_dts + 0.25/(24*60) AND VAL = 'Y';
  END LOOP;
DBMS_OUTPUT.PUT_LINE('start '||start_dts||', '||curr_dts
                   ||' cnt='||to_char(I,'999,999,999'));
END;

END;
/


Here I am trying to sleep for 15 seconds and asking for an input. It sleeps until it gets 'Y' as an input.

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Wed, 04 May 2016 14:37] by Moderator

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650819 is a reply to message #650818] Wed, 04 May 2016 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't do it this way.
PL/SQL is executed on the server and returns only when the block is finished.
You have to loop on the client side.

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650820 is a reply to message #650818] Wed, 04 May 2016 08:45 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
DB Procedures can't stop and ask for user input. Ever.
The client calls a procedure, the procedure does stuff and then returns to the client only when it's finished.

You can't do what you're trying to do.

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650821 is a reply to message #650818] Wed, 04 May 2016 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forgot the moderator bit:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as most solutions depend on it.

[Updated on: Wed, 04 May 2016 08:45]

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650831 is a reply to message #650818] Wed, 04 May 2016 15:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
You can do this using SQL*Plus, as shown below.

-- sleeptest3 procedure (run script one time to create the procedure):
CREATE OR REPLACE PROCEDURE sleeptest3
  (p_seconds IN NUMBER)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE ('start:  ' || TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS'));
  DBMS_LOCK.SLEEP (p_seconds);
  DBMS_OUTPUT.PUT_LINE ('end:    ' || TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS'));
END sleeptest3;
/


-- sleeptest.sql script:
SET ECHO OFF FEEDBACK OFF HEADING OFF VERIFY OFF
ACCEPT stop_sleeping PROMPT 'STOP SLEEPING? (Y OR N):  '
SET TERMOUT OFF
COLUMN next_script NOPRINT NEW_VALUE the_next_script
SELECT DECODE ('&&stop_sleeping', 'Y', 'stopsleep.sql', 'startsleep.sql') next_script FROM DUAL
/
SET TERMOUT ON
@&&the_next_script


-- startsleep.sql script:
BEGIN
  sleeptest3(15);
END;
/
START sleeptest


-- stopsleep.sql script:
BEGIN
  DBMS_OUTPUT.PUT_LINE ('stop:   ' || to_char (SYSDATE, 'YYYY-Mon-DD HH24:MI:SS'));
END;
/


-- sample executions of sleeptest.sql script:
SCOTT@orcl> start sleeptest
STOP SLEEPING? (Y OR N):  N
start:  2016-MAY-04 13:37:52
end:    2016-MAY-04 13:38:07
STOP SLEEPING? (Y OR N):  N
start:  2016-MAY-04 13:38:10
end:    2016-MAY-04 13:38:25
STOP SLEEPING? (Y OR N):  Y
stop:  2016-May-04 13:38:27
SCOTT@orcl> 

SCOTT@orcl> start sleeptest
STOP SLEEPING? (Y OR N):  N
start:  2016-MAY-04 13:39:08
end:    2016-MAY-04 13:39:23
STOP SLEEPING? (Y OR N):  N
start:  2016-MAY-04 13:39:25
end:    2016-MAY-04 13:39:40
STOP SLEEPING? (Y OR N):  Y
stop:   2016-May-04 13:39:42
SCOTT@orcl>


Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650835 is a reply to message #650831] Thu, 05 May 2016 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But:
SQL> @E:\sleeptest.sql
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:44
end:    2016-MAI  -05 07:32:45
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:55
end:    2016-MAI  -05 07:32:56
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:56
end:    2016-MAI  -05 07:32:57
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:57
end:    2016-MAI  -05 07:32:58
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:58
end:    2016-MAI  -05 07:32:59
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:32:59
end:    2016-MAI  -05 07:33:00
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:33:00
end:    2016-MAI  -05 07:33:01
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:33:01
end:    2016-MAI  -05 07:33:02
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:33:02
end:    2016-MAI  -05 07:33:03
STOP SLEEPING? (Y OR N):

start:  2016-MAI  -05 07:33:03
end:    2016-MAI  -05 07:33:04
SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650868 is a reply to message #650835] Thu, 05 May 2016 12:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Wed, 04 May 2016 22:34
But:
...SP2-0309: SQL*Plus command procedures may only be nested to a depth of 20.


I didn't know that. Is there any workaround?

Report message to a moderator

Re: How can I read a variable runtime in a stored proc? [message #650869 is a reply to message #650868] Thu, 05 May 2016 12:54 Go to previous message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

None I'm aware of.

Report message to a moderator

Previous Topic: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter
Next Topic: Date Updation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 02 11:18:09 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.