Using the DBMS_LOCK package: how to lock a user independently from the SQL*Plus session.

@sujith80: I am trying to use locking to avoid that the same Oracle user logins from 2 or 3 or 8000 locations, and runs the same application.  

I've found a package called DBLOCK (see http://www.unix.com.ua/orelly/oracle/bipack/ch04_01.htm#ch04-SECT-1.4.1.1 , paragraph 4.1.4.1) but in that hyperlink I can't found the complete script package.

Of course if you have other solutions feel free for different suggestions.

Sounds like that!

We have many client locations, and we would avoid that different users with the SAME LOGIN launch the SAME APPLICATIONS, causing errors.  When a user OMICRON runs XYZ application with login ALPHA, another user GAMMA with the same login ALPHA ***CANNOT*** run XYZ application.

Perfect!  But HOW can I achieve this??

@johnsone: your solution is not applicable in my applicative context, because users have different login, but access to Oracle with the same login.    In example, users ALPHA, BETA and GAMMA access to the tool using the same Oracle login P300/P300@P300

sujith80 wrote:
>If all your code is in pl/sql code an anonymous block to do it.
>(But remember,the duration of the lock is until the end of your session. I mean, if you are switching
>between things like java, pl/sql, shell,back to java, pl.sql then you need to investigate further.)

In our situation, a C# program open a PL/SQL Session using always the same Oracle credentials P300/P300@P300

When the Oracle session is open, some PL/SQL code introduces a "lock" to the HIGH-LEVEL user (not to Oracle P300 user, of course: if so, all users are locked because they can't access to the unique oracle instance P300!).  The lock will be released when the user ends using the application.

It would be useful a global variable containing the list of the HIGH-LEVEL users locked....

But nobody has the complete source of the Oracle package dblock.sql ?!

I didn't make myself clear: I'm searching for the creation script of the PACKAGE "dblock.sql" contained in the book "Oracle Built-in Packages By Steven Feuerstein, Charles Dye, John Beresniewicz": in this book, the full script is only stored in the floppy/cd-rom attached.......

A table based approach was my first solution, refused from my boss.....

Hey sujith80, what do you think about the following solution I've written??

(see attached code)

I've found this code at site http://www.psoug.org/reference/sys_context.html

CREATE OR REPLACE CONTEXT App_Ctx using My_pkg
ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE my_pkg IS
PROCEDURE set_session_id(p_session_id NUMBER);
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
PROCEDURE close_session(p_session_id NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
g_session_id NUMBER;
PROCEDURE set_session_id(p_session_id NUMBER) IS
BEGIN
g_session_id := p_session_id;
dbms_session.set_identifier(p_session_id);
end set_session_id;
--
===============================================
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
END set_ctx;
--
===============================================
PROCEDURE close_session(p_session_id NUMBER) IS
BEGIN
dbms_session.set_identifier(p_session_id);
dbms_session.clear_identifier;
END close_session;
--
===============================================
END;
/

... and I've integrated id with two stored procedures "USER_LOCK" and "USER_UNLOCK".

I pass in input a VARCHAR2 representing the name of the user I would like to lock VIRTUALLY.

If I use USER_LOCK, this name (= ne_user) will be added to a list stored in a variabled called var1, which is linked to an Oracle context called APP_CTX.  The user will be preceded and followed by an asterisk, so in var1 I could have a long string as follows:

*NAMEOFUSER1**NAMEOFUSER2**NAMEOFUSER3*

When I call USER_UNLOCK with a parameter, the name of the user to VIRTUALLY unlock, a replace function will suppress all occurrences of the name (preceded and followed by an asterisk in order to avoid confusion between names as NE and NECOLL which have a root in common, NE) and will store the result in the same variable var1 linked to the Oracle context APP_CTX.

Opening different SQL*Plus sessions, Oracle will remember the var1 value, only if I give the command:

my_pkg.set_session_id (1234);   (===> of course 1234 is an example, It could be 4321 or 4917).

What do you think about?

What could be wrong?

CREATE OR REPLACE CONTEXT App_Ctx using My_pkg
ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE my_pkg IS
PROCEDURE set_session_id(p_session_id NUMBER);
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
PROCEDURE close_session(p_session_id NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
g_session_id NUMBER;
PROCEDURE set_session_id(p_session_id NUMBER) IS
BEGIN
g_session_id := p_session_id;
dbms_session.set_identifier(p_session_id);
end set_session_id;
--
===============================================
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
END set_ctx;
--
===============================================
PROCEDURE close_session(p_session_id NUMBER) IS
BEGIN
dbms_session.set_identifier(p_session_id);
dbms_session.clear_identifier;
END close_session;
--
===============================================
END;
/
 
CREATE OR REPLACE PROCEDURE NE.user_lock (ne_user IN VARCHAR2)
IS
BEGIN
   DECLARE
      indice                NUMBER;
      appoggio_variabile1   VARCHAR2 (250);
   BEGIN
      -- my_pkg.close_session(1234);
      my_pkg.set_session_id (1234);
      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');
      DBMS_OUTPUT.put_line (appoggio_variabile1);
 
      IF INSTR (appoggio_variabile1, ne_user) >= 1
      THEN
         BEGIN
            DBMS_OUTPUT.put_line (   'The user '
                                  || ne_user
                                  || ' is already locked!'
                                 );
         END;
      ELSE
         BEGIN
            my_pkg.set_ctx ('Var1',
                            appoggio_variabile1 || '*' || ne_user || '*'
                           );
            DBMS_OUTPUT.put_line (   'The user '
                                  || ne_user
                                  || ' is locked from now.'
                                 );
         END;
      END IF;
   END;
END user_lock;
/
 
 
DROP PROCEDURE NE.USER_UNLOCK
/
 
CREATE OR REPLACE PROCEDURE NE.user_unlock (ne_user IN VARCHAR2)
IS
BEGIN
   DECLARE
      indice                NUMBER;
      appoggio_variabile1   VARCHAR2 (250);
   BEGIN
      -- my_pkg.close_session(1234);
      my_pkg.set_session_id (1234);
      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');
      DBMS_OUTPUT.put_line (appoggio_variabile1);
 
      IF    INSTR (appoggio_variabile1, ne_user) = 0
         OR appoggio_variabile1 IS NULL
      THEN
         BEGIN
            DBMS_OUTPUT.put_line (   'The user '
                                  || ne_user
                                  || ' is already unlocked!'
                                 );
         END;
      ELSE
         BEGIN
            my_pkg.set_ctx ('Var1',
                            REPLACE (appoggio_variabile1,
                                     '*' || ne_user || '*'
                                    )
                           );
            DBMS_OUTPUT.put_line (   'The user '
                                  || ne_user
                                  || ' is unlocked from now.'
                                 );
         END;
      END IF;
   END;
END user_unlock;
/

Select allOpen in new window

I wish to HOLD the LOCK even after user session ends, because I don't want that other users, accessing to a software tool using different credentials, open a new Oracle connession (Oracle connections are opened with ALWAYS the same login and password on the same SID) launching the same procedure.

All this must be realized without using auxiliary tables.

The only doubt I have is related to the my_pkc.close_session (1234) command: we know that is uses the following command:

dbms_session.set_identifier(p_session_id);

I have to study the dbms_session.set_identifier command to see if that identifier is volatile or permanent: I suppose it is permanent, because opening different SQL*Plus sessions, if I recall my_pkg.set_session_id (1234) , I obtain that the variable instantiation is not changed from the last modification in another session.

Thanks to all for having helped me to find an acceptable solution!