Home » SQL & PL/SQL » SQL & PL/SQL » dbms_lock.sleep problems (Oracle 10g)
dbms_lock.sleep problems [message #430875] Thu, 12 November 2009 16:13 Go to next message
Arokh
Messages: 6
Registered: November 2009
Junior Member
Hi all,
I'm trying to create a procedure to run in sql plus that will read the number of rows in a table wait 10 second then do it again for an indefinite period of time.

In my testing it appears the dbms_lock.sleep procedure will wait the time of the entire loop process at the beginning and quickly spit out the results at the end. In short I expected to see the result followed by 10 sec delay followed by result, etc. However its not working as expected and fail to understand what I'm doing wrong. Any help or suggestions would be appreciated. Thanks.

SET SERVEROUTPUT ON
SPOOL C:\Temp\OracleTest.log
DECLARE
i NUMBER :=1;
r NUMBER;
BEGIN
LOOP
dbms_lock.sleep(10);
select count(*) into r from spacetypes;
dbms_output.put_line(r);
EXIT when i>10;
i := i + 1;
END LOOP;
END;
/

Ed
Re: dbms_lock.sleep problems [message #430877 is a reply to message #430875] Thu, 12 November 2009 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
DBMS_OUTPUT buffers result & only outputs after procedure ends
Re: dbms_lock.sleep problems [message #430882 is a reply to message #430875] Thu, 12 November 2009 18:31 Go to previous messageGo to next message
Arokh
Messages: 6
Registered: November 2009
Junior Member
In otherwords your telling me that if I want to loop through a table and retrieve 1000 records your saying that I can not dbms_output each record to be worked on I have to output the entire 1000 into a temporary area to be worked on later by another function?

If that is the case how would I loop through and produce each result and wait for 10 seconds before producing the next result?

Unfortunately I don't have a programming background so just trying to take what I want to do and figure out how to do it.

[Updated on: Thu, 12 November 2009 18:34]

Report message to a moderator

Re: dbms_lock.sleep problems [message #430883 is a reply to message #430882] Thu, 12 November 2009 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
Oracle presents to each session a "Read Consistant View" of the database
as it existed at the start of the transaction; regardless of subsequent changes.

This means the PL/SQL posted in this thread will ALWAYS return the same value.
In your case the transaction starts when the PL/SQL is 1st invoked.

What problem are you really trying to solve?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: dbms_lock.sleep problems [message #430923 is a reply to message #430883] Fri, 13 November 2009 03:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle's default transaction isolation level is read committed - this ensures that each query executed by a transaction sees only data that was committed before the query started, but not before the transaction started began.

Thus by looping through and repeatedly running the same query, selecting a different value each time is entirely possible.

It is possible to get the behaviour that @BlackSwan describes, but you need to set the transaction level to serializable.
Re: dbms_lock.sleep problems [message #430924 is a reply to message #430882] Fri, 13 November 2009 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to write output from your code that happens at the time, rather than at the end of the procedure, you want to look at UTL_FILE

As Black Swan says, what problem are you actually trying to solve - if you an describe what you want, rather than what you're actually doing, then we will probably be able to help better.
Re: dbms_lock.sleep problems [message #430966 is a reply to message #430875] Fri, 13 November 2009 08:06 Go to previous messageGo to next message
Arokh
Messages: 6
Registered: November 2009
Junior Member
For the past 5 months I've been searching for problem with an application that disconnects from Oracle with a broken connection
at random times with no discernable pattern. There are no errors in the listener logs only signs of the reconnection. There are no errors in with the OS. There are no errors on the switches. We've removed the application from VMWare and moved to physical.

I would like to prove if this problem is in the database or in the application so I came up with running a constant query through SQL Plus and wait for the next disconnect. If SQL Plus disconnects then I know it is in the database/network. If it does not then I know its in the application.

Oracle 10.2.0.4 client and back-end DB running on Unix. I don't know what flavor of unix since its not a database I have access to.

Hopefully this clears the air a bit.

[Updated on: Fri, 13 November 2009 08:11]

Report message to a moderator

Re: dbms_lock.sleep problems [message #430968 is a reply to message #430966] Fri, 13 November 2009 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So writing date/time in a file as JRowbottom pointed you should do the trick.

Regards
Michel
Re: dbms_lock.sleep problems [message #430974 is a reply to message #430875] Fri, 13 November 2009 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or insert logging data into a table.
Whatever you do you won't be able to see the results in sqlplus - it doesn't work that way.
Re: dbms_lock.sleep problems [message #431043 is a reply to message #430875] Fri, 13 November 2009 14:14 Go to previous messageGo to next message
Arokh
Messages: 6
Registered: November 2009
Junior Member
I appreciate all the ideas and assistance. I finally came up with this to prove my point that either the database or application is deciding to drop connection. If my calculations are correct the following will execute this query 6 times per minute for a 24 hour period. Yes I wont get any results, however by tracing the session I should see if the connection gets destroyed somehow by the database. Furthermore if an error is generated by sql plus I should also get that in the mytest.sql spool file. Either way I'm hopeful this will point the finger as to where this very elusive problem resides. Thanks again all!

spool c:\temp\mytest.sql

alter session set sql_trace = true;

show parameter user_dump_dest
show parameter optimizer

declare x passtypes.passtype%type;

begin

for i in 1..86400 loop
select passtype into x from passtypes where rownum = 1;
dbms_lock.sleep(10);
end loop;
end;
/
Re: dbms_lock.sleep problems [message #431642 is a reply to message #431043] Wed, 18 November 2009 16:22 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
you need to be very careful with your test case to ensure it mimics your application. Your client can influence whether it uses dedicated or possibly shared server connection. JDBC vs Sqlplus OCI etc are all different. You also need to know how your app connects - does it open a new connection each time, use a connection pool etc. I hope you know that your script listed below passes 1 anonomous PL/SQL to the database, so there's probably no further communication (even background stuff you can't see) with your SQLplus client until it's complete.

If you have a firewall and are using connection pools or are opening new connections - ensure the firewall isn't blocking a range of ports that you are hitting "randomly". I assume you've considered this because you mention the network is clear.

Enabling more verbose sqlnet logging (in client's sqlnet.ora) and the database listener can help to do better tracing.
Previous Topic: Unique constraint error
Next Topic: PL/SQL DWH features
Goto Forum:
  


Current Time: Tue Sep 27 17:56:09 CDT 2016

Total time taken to generate the page: 0.11986 seconds