Create dB sequence

Hi Experts,

I am a newbie when it comes to database sequences. I have a database containing a USR (user) table containing User ID and Password. I would like to create a db sequence in the database to generate user id and password.

Appreciate if you can give me examples as to how to do it/
itsme_asifAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What are you wanting to do here?  Sequences in Oracle are numeric sequence generators.  Creating a sequence is easy and a minute or two in the docs shows you how.

http://psoug.org/reference/sequences.html

I'm thinking your question is a little deeper than that.
0
sdstuberCommented:
perhaps something like this?


one option to this would be to create the procedure with "AUTHID CURRENT_USER"
CREATE OR REPLACE PROCEDURE create_users(p_cnt IN INTEGER DEFAULT 1)
IS
    v_seq VARCHAR2(10);
    v_sql VARCHAR2(1000);
BEGIN
    FOR i IN 1 .. p_cnt
    LOOP
        SELECT TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') INTO v_seq FROM DUAL;

        v_sql := 'create user usr_' || v_seq || ' identified by pwd_' || v_seq;
        DBMS_OUTPUT.put_line(v_sql);

        EXECUTE IMMEDIATE v_sql;

        v_sql := 'grant create session to usr_' || v_seq;
        DBMS_OUTPUT.put_line(v_sql);

        EXECUTE IMMEDIATE v_sql;
    END LOOP;
END;

Open in new window

0
sdstuberCommented:
small correction, the procedure above creates oracle users, you requested application users.
you can use the same idea though,
CREATE OR REPLACE PROCEDURE create_users(p_cnt IN INTEGER DEFAULT 1)
IS
    v_seq VARCHAR2(10);
    v_sql VARCHAR2(1000);
BEGIN
    FOR i IN 1 .. p_cnt
    LOOP
        SELECT TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') INTO v_seq FROM DUAL;

        v_sql := 'insert into usr (user_name,password) values (''usr_' || v_seq || ''' , ''pwd_' || v_seq || ''')';
        DBMS_OUTPUT.put_line(v_sql);

        EXECUTE IMMEDIATE v_sql;

         -- if there are any privileges you need to grant or other updates to new users you can add them here
    END LOOP;
END;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
you can also create multiple users in a single sql statement
and bypass the procedure completely


insert into usr (user_name,password) select 'usr_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') ,  'pwd_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') from dual
0
sdstuberCommented:
small adjustment to above.

if you want the initial password to be based on the user name then
 use currval instead of nextval for the password


insert into usr (user_name,password) select 'usr_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') ,  'pwd_' || TO_CHAR(usr_sequence.currval, 'fm000009') from dual
0
Mark GeerlingsDatabase AdministratorCommented:
Oracle sequences are independent database objects that generate sequential numbers.  One of the ways that you can use them is in a pre-insert trigger to automatically populate one of the columns in new records.  You cannot directly assign a sequence to a particular table.

Also, you should be aware that some of the syntax in the PL\SQL examples posted by others here is legal and demonstrates a point, but they are not not necessarily a good idea to use in a production system, for example: dbms_output.put_line and execute immediate.
0
slightwv (䄆 Netminder) Commented:
One more point about Oracle sequences that you need to be aware of:  They are not guaranteed gap-less.  In other words:  They can skip numbers.
0
itsme_asifAuthor Commented:
Thanks very much @sdstuber. Like i mention im a newbie when it comes to sequences, I would really appreciate if u can please explain me the following sql stmnt

insert into usr (user_name,password) select 'usr_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') ,  'pwd_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') from dual
0
sdstuberCommented:
"they are not necessarily a good idea to use in a production system"
what is wrong with dbms_output.put_line or execute immediate in production?

I'll grant that there is a slight performance issue with dbms_output but creating a user
isn't going to be high-performance loop. I guess in old db's you "might" have issues with blowing out your output buffer if you frequently have output turned on but that would be odd and in modern db's the output buffer is unlimited.

But good catch, it is a small, but possible concern, so if you are running the procedures above in 9i or lower db's then be sure to either remove the dbms_output lines, or be sure to have the output turned off unless you really need it.

as for execute immediate ,particularly in my first example: "create user", you must use execute immediate or dbms_sql or dbms_utility.exec_ddl_statement, neither of the latter two offer anything superior to execute immediate.

and, there is no danger of sql injection attacks in any of the above, the sql construction is entirely controlled in the procedure


0
sdstuberCommented:
let's break the sql down...

insert into usr (user_name,password)    -- the basic framework of an insert statement, defines which table and which columns will be populated as well as the order of input for those columns.

select   ... from dual   --  we must get the data somehow.  you can't select from a sequence directly we must do it from a table,  Oracle includes the dummy table "dual" for just this type of operation.

 'usr_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009')  -- prefix each user name with "usr_" then append the sequence value, the to_char is format forces the number to be 6 digits long and left-padded with zeroes if needed


  'pwd_' || TO_CHAR(usr_sequence.NEXTVAL, 'fm000009') -- prefix each password with "pwd_" then append the sequence value, the to_char is format forces the number to be 6 digits long and left-padded with zeroes if needed

hope that helps


0
Mark GeerlingsDatabase AdministratorCommented:
Dbms_output is useful for debugging and trouble shooting, but it isn't designed or intended for production use.  Also, the output is not displayed in real time (that is: line-by-line as the procedure executes, or with each iteration of a loop if a loop is used.  Rather, the output is buffered, then dumped to the screen only after the PL\SQL block (or procedure or function) finishes.  And, in SQL*Plus at least, even then the output is not displayed unless you run:
set serveroutput on
before you run the block (or procedure).

Sdstuber: are you sure about the unlimited buffer size for dbms_output in Oracle10 or 11?  I have not observed that.
0
sdstuberCommented:
yes, I agree with everything you said about it's utility. And, if there is some other form of instrumentation available then I use that, but if the only instrumentation I have available is dbms_output, then I leave it in, even in production unless it will cause significant performance degradation or buffer overruns (which shouldn't be a problem in 10g and above)

10g and above do support unlimited buffers but they can be set to have a limit if you want
I always use unlimited and assumed everyone else did too. Good catch though.

if you do "set serveroutput on" that sets the buffer limit to NULL which is unlimited

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#CIHEGBBF
0
itsme_asifAuthor Commented:
HI @sdstuber,

I tried creating a sequence (a simple one)

  CREATE SEQUENCE  "IDMDEV2"."CAMPUS_SITE_ID_SEQ"  MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 5689 NOCACHE  NOORDER  NOCYCLE ;

and when I called SELECT seq_campus_site_id.NEXTVAL FROM dual;
i saw the value starting from 5689 incrementing by 1 and as u mentioned Oracle included the dual table to select the next value in the sequence. However when I did a select * from dual; i did not see any values, is there anyway i can view the previously used sequence values?
0
sdstuberCommented:
no you can't see what values were previously used  that isn't tracked anywhere.


0
slightwv (䄆 Netminder) Commented:
>>did not see any values
Just to add:  the DUAL table is an invention from Oracle to retrieve a single row from a result set.  It serves no other purpose.

In older versions of Oracle (up until somewhere in 11g) you could only get a sequence value from a select statement.

For example:  

select sysdate from dual;
select 'Hello World' form dual;
0
Mark GeerlingsDatabase AdministratorCommented:
You can never retrieve a previous sequence value from the "dual" table.  That table always has just one column (named "dummy") and a single row with the value: "X".  The main purpose of this table is to satisfy the requirements of SQL "select" statement syntax, when exactly one value is desired to be reurned by the query.

But, if you create your sequence with "NOCACHE" (as you did) it is possible to retrieve the last value that the sequence gave out *IF* you are logged in as the owner of the sequence with this query:

column By format 999;
column Min format 999,999;
column Cache format 9999;
column Last format 999,999,999;
column Max format 999,999,999;
select s.sequence_name, s.min_value "Min", s.max_value "Max", s.increment_by "By",
s.last_number "Last", s.cache_size "Cache", cycle_flag, o.created "Created"
from user_objects o, user_sequences s
where o.object_name = s.sequence_name
and o.object_type = 'SEQUENCE'
order by s.sequence_name;

Note: if you run that in SQL Developer, or TOAD or another GUI tool you will not need the "column..." commands.  Those are for SQL*Plus.
0
sdstuberCommented:
I recommend http:#29903988 and http:#29907827 

the first answers the question for creating a bunch of users from a sequence, the second contains a small correction to that script
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.