Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Throw exception if there are pending updates

Posted on 2011-02-28
5
Medium Priority
?
574 Views
Last Modified: 2013-12-07
Is it possible to create and SQL block that will execute a SELECT only if there are no an uncommitted updates pending.

Ideally I would like to throw an exception if there are pending updates.  Any suggestions
0
Comment
Question by:weegiraffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34995790
you can check V$LOCKED_OBJECT or V$LOCK accordingly to check whether there are any locks on the tables ( which are being updated/deleted ).
0
 

Author Comment

by:weegiraffe
ID: 34995802
hi nav_kum_v

Can you please provide a simple example of how to do this in PL/SQL block?
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 2000 total points
ID: 34995834
i mean just run a select statement against that view. something like this...

declare
rr_type v$locked_object%rowtype;
begin
select  * into rr_type from v$locked_object where object_id = ( select object_id from dba_objects
where object_name='MY_TEST_TABLE' ) and rownum = 1;

-- lock found
-- do something else here...may be log into error/debug table or thrown an exception/error message.

exception when no_data_found then
    -- no locks
    null;    
end;
/
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34996429
why do you want to check for updates before issuing a select?

updates don't block select?  It's perfectly safe to do so.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 34997946
Rather than checking a V$ view, why not just do a SELECT ... FOR UPDATE NOWAIT.  That should error out if it cannot lock all the rows.

However, I do agree with sdstuber, as the locks would not block a select, why do you need to check?
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question