PL/SQL: Error when creating sequence

Question

I'm quite new to PL/SQL, and am using Oracle SQL Developer to write a procedure which uses a sequence to generate a primary key for some existing data, to write into another DB.

The code in question is under NDA.. Essentially I have the following:

create or replace
PROCEDURE Generate_Data
(
   output IN VARCHAR2
) 
AS

-- Variables here --

CURSOR myCursor IS
SELECT data1, data2 
FROM table;

CREATE SEQUENCE mySequence      <-- error on this line
START WITH 0
INCREMENT BY 1;

BEGIN
LOOP
    -- snip --

It raises the error PLS-00103, saying it encountered the symbol CREATE when expecting on of the following: begin, function, package, pragma, procedure, ...

I've been following the example at: http://www.techonthenet.com/oracle/sequences.php

Answer 1

The reason you're getting this error is that you're trying to perform DDL, in this case creating a sequence, within PL/SQL. It is possible to do this, but you must use execute immediate.

As Alex says, you also wouldn't be able to do this in the declare section. It would look something like this:

begin

   execute immediate 'CREATE SEQUENCE mySequence
                          START WITH 0
                          INCREMENT BY 1';    
end;

However, as Padmarag also says, it's highly unlikely that you want to do this within PL/SQL. It would be more normal to create a sequence outside and then reference this later. More generally speaking, performing DDL inside a PL/SQL block is a bad idea; there should be no need for you to do it.

You don't mention what version of Oracle you're using. From 11g the ways in which you could access sequences got extended. If you're using 11g then you can access the sequence by creating a variable and assigning the next value in the sequence, .nextval, to this variable:

declare    
   l_seq number;    
begin

   loop
      -- For each loop l_seq will be incremented.
      l_seq := mysequence.nextval;
   -- snip    
end;

If you're before 11g you must (outside of DML) use a select statement in order to get the next value:

declare
   l_seq number;
begin

   loop
      -- For each loop l_seq will be incremented.
      select mysequence.nextval into l_seq from dual;
   -- snip    
end;

Please bear in mind that a sequence is meant to be a persistent object in the database. There is no need to drop and re-create it each time you want to use it. If you were to run your script, then re-run it the sequence would happily keep increasing the returned value.

Further Reading

• About sequences
• Using sequences