This content has been marked as final. Show 2 replies
Is there any way I can manually change following two MS SQL Server system stored procedures into Oracle system PL/SQL stored procedures:
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
If you have specific conversion questions or issues you can post them in the DATABASE GENERAL forum
General Database Discussions
Or in the SQL and PL/SQL forum