CODEWITHSUNDEEP
oracle-databaseplsqlpls-00103
iank
iank

Reputation: 810

PL/SQL: Error when creating sequence

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

Upvotes: 5

Views: 10744

Answers (3)

Ben
Ben

Reputation: 52863

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

Upvotes: 7

Alex Gitelman
Alex Gitelman

Reputation: 24722

You can't create sequence in the DECLARE block of procedure. Move it after BEGIN. It's arguable if it makes sense, though. You probably need to create it outside your procedure in the first place.

Update

Actually, if you truly want it inside BEGIN/END use following:

EXECUTE IMMEDIATE 'CREATE SEQUENCE mySequence  START WITH 0 INCREMENT BY 1';

Upvotes: 2

Padmarag
Padmarag

Reputation: 7214

You'd need to create the sequence before using it.

And in the PL/SQL code use
-- Variables here --1
v_seq_val number;

BEGIN
Select mySequence.nextval from dual into v_seq_val

In general SQL is for DDL(Data Definition Language) and PL/SQL is for DML(Data Manipulation Language) and logic.

If you wanted you could do Create from PL/SQL, but I think that's not what you want over here.

Upvotes: 0

Related Questions