Home » SQL & PL/SQL » SQL & PL/SQL » Restricting a package from other users
Restricting a package from other users [message #336361] Fri, 25 July 2008 18:24 Go to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member

Hi,
Is there any way I can make sure that my package can be accessed by only one user at a time. i.e. as long as it is being used by one user others cannot access it.

Regards,

Sudipta
Re: Restricting a package from other users [message #336362 is a reply to message #336361] Fri, 25 July 2008 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

Please elaborate on what should occur to the user who needs to invoke your package, but somebody else is currently running it?

Why do you wish to build a NON-SCALABLE application?

I suspect that you have a serious design flaw.

[Updated on: Fri, 25 July 2008 20:39] by Moderator

Report message to a moderator

Re: Restricting a package from other users [message #336369 is a reply to message #336361] Sat, 26 July 2008 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first question is why do you need this?
Then, if you don't care about scalability remark from Ana, have a look at dbms_alert or dbms_lock package.

Regards
Michel
Re: Restricting a package from other users [message #336680 is a reply to message #336361] Mon, 28 July 2008 12:18 Go to previous messageGo to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member

Hi,
There may be a design issue. But I need this because the same package is updating tables specific to users, which is being used by another program in later stage.

Here specific type of user has specific tables ( all seperate).

I want pt put some restriction on other user when one user is using the package. Other should get a message like
' The system is being used by this specific user Please wait or come back later'

Regards,

Suditpa
Re: Restricting a package from other users [message #336681 is a reply to message #336680] Mon, 28 July 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is indeed a big design problem.

Regards
Michel
Re: Restricting a package from other users [message #336682 is a reply to message #336361] Mon, 28 July 2008 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
PL/SQL never interacts directly with any end user.
Re: Restricting a package from other users [message #336723 is a reply to message #336682] Mon, 28 July 2008 15:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Sure this can be done - batch processing is common. Not everything in this world is OLTP. v$session is fine for a single instance database - there may be a more appropriate view in RAC etc.

Pseudo code...
-- check if the code is aready running 
-- (say for the same dept if you want some granularity)
select count(*) into v_dummy from v$session where client_info = 'my batch job: dept='||v_dept; 

if v_dummy > 0 then error "try later"...
else 
  -- create your logical lock for others to see  
  dbms_application_info.set_module( 'my-module', 'my-action'); 
  dbms_application_info.set_client_info( 'my batch job: dept='||v_dept); 

  -- your processing ...

  -- done (remove your logical lock)
  dbms_application_info.set_client_info( 'done' ); 
end if;
  exception
  when others then
    -- (remove lock & leave the error msg. Useful if session remains open)
    dbms_application_info.set_client_info( substr(sqlerrm, 1, 30) ); 
end;

[Updated on: Mon, 28 July 2008 15:39]

Report message to a moderator

Re: Restricting a package from other users [message #336734 is a reply to message #336723] Mon, 28 July 2008 15:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what happens if 2 processes executes the same first query at the same time?
Without a "lock" you can guarantee nothing.

Regards
Michel
Re: Restricting a package from other users [message #336742 is a reply to message #336361] Mon, 28 July 2008 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
I would not be surprised if OP comes from the Micro$soft side,
where they fret endlessly about locking & such.

I'd be willing to bet dollars to donuts that no explicit locking is required for this Oracle application.
Re: Restricting a package from other users [message #336745 is a reply to message #336742] Mon, 28 July 2008 16:42 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Michel, yes dbms_lock is probably a more current solution.

Ana - don't be so quick the deride the OP until know their environment.
Previous Topic: Converting month/year to month number? (merged)
Next Topic: ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Wed Dec 07 18:39:24 CST 2016

Total time taken to generate the page: 0.08532 seconds