CODEWITHSUNDEEP
oracle-databaseplsqloracle11gsequence
Rahul Chauhan
Rahul Chauhan

Reputation: 11

Assign a unique value to a user each time

How to generate and assign a unique value (sequence) to a user every time a user logs in? If two users login in at the same time simultaneously, I should be able to avoid assigning same value to both. And it is not a specific requirement. It can be any operation for which a sequence is required to be generated and assigned. I am trying to understand how the situation can be handled if the operation happens at the same time. I require a solution for Oracle 11g specifically.

Upvotes: 0

Views: 44

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Without understanding what exactly what you want to accomplish with the unique value, as you are not explaining it, you might do something like this:

Create a Sequence for creating an incremental sequential number

SQL> create sequence my_seq start with xxx increment by yyy cache zzz;

Where

  • xxx is the number where the sequence should start
  • yyy is the increment value of the sequence ( normally 1 )
  • As you plan to use it each time the user logs in, it might be a good idea to cache some values for faster access. zzz represents the number of values of the sequence you want to cache.

Assign the values to each user which logs in

A way to assign this value to a user each time logs in is through a logon trigger and related it to the properties of the session, using the advantages provided by the default context SYS_CONTEXT. Of course, for that you need a DBA user to install the trigger in an admin schema ( as sys or system )

An example to store those values in a custom audit table:

SQL> CREATE TRIGGER AFT_LOG_DBT 
     AFTER LOGON ON DATABASE
     DECLARE 
     -- declare variables
     v_db_user varchar2(100);
     v_os_user varchar2(100);
     v_seq number;
     BEGIN
         SELECT sys_context('userenv','session_id') , -- the session id 
                sys_context('userenv','osuser') , -- the os user
                my_seq.nextval                   -- the sequence next val
         INTO
               v_db_user,
               v_os_user,
               v_seq 
         FROM DUAL;
     -- insert those values into an audit table 
     insert into your_schema.log_audit ( logon_time , db_user, os_user, seq_val )
     values ( sysdate , v_db_user, v_os_user, v_seq );
     commit;
   END;

As you were not explaining what you wanted to do with the unique sequence value, I tried to provide you with an example of a use case.

I have never gotten duplicate sequence numbers from a sequence generator unless it has wrapped the max value or is set to cycle.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143033

You named it - use a sequence.

create sequence myseq;

Fetch value from it using

select myseq.nextval from dual;

Assingn it to that user. Though, I'm not sure I understand what that actually is; what would you assign it to, really? I hope you know.

Upvotes: 1

Related Questions