This discussion is archived
2 Replies Latest reply: Jan 31, 2013 2:38 PM by rp0428 RSS

Is there anyway I can manually change sp_getapplock, sp_releaseapplock

user480768 Newbie
Currently Being Moderated
Is there any way I can manually change following two MS SQL Server system stored procedures into Oracle system PL/SQL stored procedures:
1.sp_getapplock,
2.sp_releaseapplock

Thanks for your help.

Kevin
  • 1. Re: Is there anyway I can manually change sp_getapplock, sp_releaseapplock
    Gary Graham Expert
    Currently Being Moderated
    Hi Kevin,

    Maybe this is want you are looking for:
    http://stackoverflow.com/questions/2627609/is-there-an-equivalent-to-sp-getapplock-sp-releaseapplock-in-oracle

    -Gary-
  • 2. Re: Is there anyway I can manually change sp_getapplock, sp_releaseapplock
    rp0428 Guru
    Currently Being Moderated
    >
    Is there any way I can manually change following two MS SQL Server system stored procedures into Oracle system PL/SQL stored procedures:
    1.sp_getapplock,
    2.sp_releaseapplock
    >
    No - but as Gary suggested the DBMS_LOCK package has similar functionality.

    However, locks are seldom needed in Oracle so you should make sure you really need them first. Oracle works differently in many areas than sql server and provides read-consistency and other advantages that you need locks in sql server to achieve.

    Also with Oracle readers DO NOT block writers and writers DO NOT block readers unlike sql server, db2 and some other databases. So for the basics you don't need to do much locking at all in Oracle.

    And for data access there are constructs such as the FOR UPDATE clause of queries that will perform row-level locking for you and when you COMMIT or ROLLBACK those locks will be automatically released.

    Many times developers from the sql server world that are converting to Oracle try to do things the sql server way. That is not only unnecessary in most cases but can be counter-productive. The main gotcha is the use of temporary tables. In Oracle they are rarely needed and then only for special cases. But in sql server they are often mandatory.

    For the lock functionality you will need to use PL/SQL to use it since you need to work with lock handles to test the lock that is protecting your process.

    See my reply in this thread from last year that shows how to use Oracle locks to serialize access to a process
    Re: possible to lock stored procedure so only one session may run it at a time?

    See the detailed documentation in the PL/SQL Language doc
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lock.htm#ARPLS66769

    If you have specific conversion questions or issues you can post them in the DATABASE GENERAL forum
    General Questions

    Or in the SQL and PL/SQL forum
    SQL and PL/SQL

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points