Solved

Creating sequences inside stored procedures

Posted on 2000-02-14
13
1,609 Views
Last Modified: 2011-09-20
I'm trying to write a stored procedure to create sequences. I will be bulk loading tables, and then I will want to reset the sequence to the next appropriate number.

Example:

CREATE OR REPLACE PROCEDURE TBLSPC."CreateSequencesOnOracle"
AS


DECLARE Key Number;

BEGIN

SELECT MAX(ABCKey)+1 INTO Key FROM ABCTbl;

END;

/

CREATE SEQUENCE SEQ_ABCKey
    START WITH Key
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER;


This example does not work. Can someone give me one that does?
0
Comment
Question by:covington
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 4

Expert Comment

by:urim
Comment Utility
if you want to run this in sqlplus you can write a little script like this:

CREATE SEQUENCE SEQ_ABCKey
    START WITH &Key
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER;

When you'll run this script it will ask you for the value of key.

if you want to do it in stored procedure then here is your stored procedure:
CREATE OR REPLACE PROCEDURE TBLSPC."CreateSequencesOnOracle"
AS


DECLARE
   Key Number;
   cid number;
BEGIN

   SELECT MAX(ABCKey)+1 INTO Key FROM ABCTbl;
   cid := dbms_sql.open_cursor();
   dbms_sql.parse (cid,
      'CREATE SEQUENCE SEQ_ABCKey
    START WITH '||to_char(key) ||
      ' INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 20
    NOORDER', dbms_sql.NATIVE);
   dbms_sql.execute (cid);
   dbms_sql.close (cid);

   exception
    when others then
        if dbms_sql.is_open (cid) then
            dbms_sql.close (cid);
        end if;
end;
/


Uri
0
 
LVL 4

Expert Comment

by:urim
Comment Utility
Oops I forgot,

Your script don't works because the key variable you declared in your stored procedure isn't devine outside. The create sequence statement is outside the stored procedure.

So I guess the first option is waht you looked for, although you can use the second one, if you plan to automate the jobs.

If you want to do a DDL command in stored procedure you _must_ do it by calling to the procedures in dbms_sql like the second option.

cheers,
Uri
0
 
LVL 3

Author Comment

by:covington
Comment Utility
Urim:

1) When I put the create statement inside the SP, the compiler chokes on the 'CREATE' keyword.

2) When I tried to use your code to create the SP, I got the following error message:
PLS-00103: Encountered symbol 'DECLARE' when expecting...

The DB is Oracle 8.0.5
0
 
LVL 4

Expert Comment

by:urim
Comment Utility
remove the declare word, you don't need it if you created stored procedure
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility

If you performing bulk load and need to reinitialize sequence with certain number, you'll need to use below LOAD option:

Setting a Column to a Unique Sequence Number

The SEQUENCE keyword ensures a unique value for a particular column. SEQUENCE increments for each record that is loaded or rejected. It does not increment for records that are discarded or skipped.

SEQUENCE
The combination of column name and the SEQUENCE function is a complete column specification.


columnname - SEQUENCE ( - N/COUNT/MAX - , increment - )

where:

columnname  
 The name of the column in the database to which to assign the sequence.  
 
SEQUENCE  
 Use the SEQUENCE keyword to specify the value for a column.  
 
n  
 Specifies the specific sequence number to begin with  
 
COUNT  
 The sequence starts with the number of rows already in the table plus the increment.  
 
MAX  
 The sequence starts with the current maximum value for the column plus the increment.  
 
increment  
 The value that the sequence number is to increment after a record is loaded or rejected  

Sometimes, you might want to generate different sequence numbers for each INTO TABLE clause. For example, your data format might define three logical records in every input record. In that case, you can use three INTO TABLE clauses, each of which inserts a different part of the record into the same table. Note that, when you use SEQUENCE(MAX), SQL*Loader will use the maximum from each table which can lead to inconsistencies in sequence numbers.


SEQUENCE(MAX)will work in your case.

Regards,
 
  rwarsh
0
 
LVL 3

Author Comment

by:covington
Comment Utility
The bulk load is coming through a third party tool, so it does not appear as though your suggestion will work.

This tool will completely remove and re-create the tables each time, so I need an automated stored procedure to do the task.

Urim's solution seems to be the closest to what I am looking for, but I have found at least a few syntax errors in the code. I am still working through it.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Expert Comment

by:junfeb
Comment Utility
Here is a simple script that will enable you to extract sequence information from your database, then you can drop the sequence and rebuild it. This can be done for all of the sequences in your database, or with tiny modification you can do it for one sequence or for sequences belonging to a user. This is a sql*plus scipt -
easy one ---

set  pages 0 feedback off echo off
spo cre_seqs.sql
select 'set echo on' from dual;
select 'spool cre_seqs.lst' from dual ;
select 'create sequence '||sequence_owner||'.'||sequence_name||'
                start with '||last_number||'
                increment by '||increment_by||'
                '||decode(cycle_flag, 'N', 'NOCYCLE', 'cycle')||'
                '||decode(order_flag, 'N', 'NOORDER', 'order')||'                '||decode(cache_size,0,'NOCACHE','CACHE'||cache_size)||'
                maxvalue '||max_value||';'
from sys.dba_sequences
/
select 'spool off' from dual;
spo off
set heading on feedback on


If you don't want to do it as sys, take out the sequence_owner and run it from user_sequences , or if you want to create for only one sequence add a where clause to indicate the sequence name.

Thanks.
0
 
LVL 3

Expert Comment

by:junfeb
Comment Utility
Here is a simple script that will enable you to extract sequence information from your database, then you can drop the sequence and
                     rebuild it. This can be done for all of the sequences in your database, or with tiny modification you can do it for one sequence or for
                     sequences belonging to a user. This is a sql*plus scipt -
                     easy one ---

set  pages 0 feedback off echo off
spo cre_seqs.sql
select 'set echo on' from dual;
select 'spool cre_seqs.lst' from dual ;
select 'create sequence '||sequence_owner||'.'||sequence_name||'
start with '||last_number||'
increment by '||increment_by||'
'||decode(cycle_flag, 'N', 'NOCYCLE', 'cycle')||'
'||decode(order_flag, 'N', 'NOORDER', 'order')||'                '||decode(cache_size,0,'NOCACHE','CACHE'||cache_size)||'
 maxvalue '||max_value||';'
         from sys.dba_sequences
/
select 'spool off' from dual;
spo off
set heading on feedback on


                     If you don't want to do it as sys, take out the sequence_owner and run it from user_sequences , or if you want to create for only one
                     sequence add a where clause to indicate the sequence name.

Once you run this this will create a cre_seqs.sql file which you can run in your instance.

Thanks.
0
 
LVL 3

Author Comment

by:covington
Comment Utility
Once the bulk load is done, there is NO sequence information in the DB. That's why I need to use select(max)+1 to rebuild the sequences.
0
 
LVL 4

Expert Comment

by:syakobson
Comment Utility
You need to:

1. Directly (not via role) grant CREATE ANY SEQUENCE privilege to stored procedure owner.

2. Directly (not via role) grant SELECT ANY TABLE privilege to stored procedure owner.

3. Directly (not via role) grant EXECUTE privilege on SYS.DBMS_SQL package to stored procedure owner (you might need to do it as SYS).

4. Create the following stored procedure (I assume CREATE PROCEDURE privilege is there):

CREATE OR REPLACE
  PROCEDURE CreateSequencesOnOracle(OWNER IN VARCHAR2,
        TBL IN VARCHAR2,
        COL IN VARCHAR2,
        SEQ IN VARCHAR2
       )
   AS
     C INTEGER := DBMS_SQL.OPEN_CURSOR;
     Stmt VARCHAR2(200);
     StartWith NUMBER;
     ignore INTEGER;
   BEGIN
     stmt := 'SELECT MAX(' || COL || ')+1 FROM ' || OWNER || '.' || TBL;
     DBMS_SQL.PARSE(c,stmt,DBMS_SQL.NATIVE);
     DBMS_SQL.DEFINE_COLUMN(C,1,StartWith);
     ignore := DBMS_SQL.EXECUTE_AND_FETCH(C);
     DBMS_SQL.COLUMN_VALUE(C,1,StartWith);
     stmt := 'CREATE SEQUENCE ' || OWNER || '.' || SEQ || ' START WITH ' ||
           StartWith || ' INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER';
     DBMS_SQL.PARSE(c,stmt,DBMS_SQL.NATIVE);
     ignore := DBMS_SQL.EXECUTE(C);
END;
/

Below is an example:

SQL> create user u1 identified by u1;

User created.

SQL> grant CREATE SESSION,CREATE PROCEDURE,CREATE PUBLIC SYNONYM to u1;

Grant succeeded.

SQL> grant SELECT ANY TABLE,CREATE ANY SEQUENCE to u1;

Grant succeeded.

SQL> grant EXECUTE on SYS.DBMS_SQL to u1;

Grant succeeded.

SQL> connect u1/u1
Connected.
SQL> CREATE OR REPLACE
  2    PROCEDURE CreateSequencesOnOracle(OWNER IN VARCHAR2,
  3          TBL IN VARCHAR2,
  4          COL IN VARCHAR2,
  5          SEQ IN VARCHAR2
  6         )
  7     AS
  8       C INTEGER := DBMS_SQL.OPEN_CURSOR;
  9       Stmt VARCHAR2(200);
 10       StartWith NUMBER;
 11       ignore INTEGER;
 12     BEGIN
 13       stmt := 'SELECT MAX(' || COL || ')+1 FROM ' || OWNER || '.' || TBL;
 14       DBMS_SQL.PARSE(c,stmt,DBMS_SQL.NATIVE);
 15       DBMS_SQL.DEFINE_COLUMN(C,1,StartWith);
 16       ignore := DBMS_SQL.EXECUTE_AND_FETCH(C);
 17       DBMS_SQL.COLUMN_VALUE(C,1,StartWith);
 18       stmt := 'CREATE SEQUENCE ' || OWNER || '.' || SEQ || ' START WITH ' ||
 19             StartWith || ' INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER';
 20       DBMS_SQL.PARSE(c,stmt,DBMS_SQL.NATIVE);
 21       ignore := DBMS_SQL.EXECUTE(C);
 22* END;
SQL> /

Procedure created.

SQL> grant EXECUTE on CreateSequencesOnOracle to PUBLIC;

Grant succeeded.

SQL> create public synonym CreateSequencesOnOracle for CreateSequencesOnOracle;

Synonym created.

SQL> connect scott/tiger
Connected.
SQL> select sequence_name from user_sequences;

no rows selected

SQL> select max(empno) from emp;

MAX(EMPNO)
----------
      7934

SQL> exec CreateSequencesOnOracle('SCOTT','EMP','EMPNO','EMPSEQ');

PL/SQL procedure successfully completed.

SQL> select * from user_sequences;

SEQUENCE_NAME                  MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ --------- --------- ------------ - - ---------- -----------
EMPSEQ                                 1 1.000E+27            1 N N         20        7935

SQL>

As you can see, stored procedure created a sequence EMPSEQ starting with 7935.

Solomon Yakobson.
0
 
LVL 3

Author Comment

by:covington
Comment Utility
Syakobson, it appears that your solution will work, but Uri answered first.

I will accept Uri's answer. It was the closest to what I want, and I used most of his code to make my solution. In addition to the changes he mentioned above, I had to make the following changes:

1) Change

dbms_sql.close (cid);

to

dbms_sql.close_cursor (cid);

2) Change

 dbms_sql.execute (cid);

to

 dummy := dbms_sql.execute (cid);



Uri, please post an answer so that I can give you points.
0
 
LVL 4

Accepted Solution

by:
urim earned 200 total points
Comment Utility
thanks
0
 
LVL 3

Author Comment

by:covington
Comment Utility
I gave it a B because of the typos mentioned above.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now