[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Create dB sequence

Posted on 2010-04-05
18
Medium Priority
?
716 Views
Last Modified: 2013-12-07
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/
0
Comment
Question by:itsme_asif
  • 9
  • 3
  • 3
  • +1
17 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29849879
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29861319
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29903988
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 74

Expert Comment

by:sdstuber
ID: 29904135
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29907827
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29913322
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29913609
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
 

Author Comment

by:itsme_asif
ID: 29913934
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29914642
"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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29914981
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29939022
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29939746
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
 

Author Comment

by:itsme_asif
ID: 29941850
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 29941960
no you can't see what values were previously used  that isn't tracked anywhere.


0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 29944603
>>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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 29945073
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 32631147
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question