5

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

iank
  • 800
  • 3
  • 10
  • 32

3 Answers3

7

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

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • I've modified it to match your second method as I'm quite sure we're before 11g. It says that the sequence (which I created in execute immediate) does not exist. Do you have any ideas as to why that could be? – iank Jul 03 '12 at 20:55
  • It's probably because you're referencing the sequence in the code. As you create the object in a block you have to have _every_ reference to that object also wrapped in `execute immediate`. Is there any reason why you can't create the sequence outside of the block? – Ben Jul 03 '12 at 20:57
  • I'm just really new to writing procedures.. I don't know how to put something like this outside of the file that I'm working inside of. – iank Jul 03 '12 at 21:03
  • You're not using a GUI then? You should be able to execute it. Can you create a second file and just execute that instead? – Ben Jul 03 '12 at 21:05
  • I'm using SQL developer. I just have one procedure open in the Procedures dropdown, which has all the code for this one thing in it. – iank Jul 03 '12 at 21:07
  • Unfortunately I don't use SQL Developer, you should be able to just open a new window. The docs suggest there's a [create new object](http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#autoId16) method. Though apparently there's a [separate dialog box](http://docs.oracle.com/cd/E25259_01/appdev.31/e24285/dialogs.htm#autoId48)/. – Ben Jul 03 '12 at 21:12
2

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';
Alex Gitelman
  • 24,429
  • 7
  • 52
  • 49
  • I tried moving it after BEGIN and it still gave the same error. I'll look into moving it outside the procedure. – iank Jul 03 '12 at 19:34
  • I added update. You can't use `CREATE` directly. Must use `EXECUTE IMMEDIATE`, although it's usefulness is still arguable. – Alex Gitelman Jul 03 '12 at 19:41
  • I'll try that. Since I'm still learning, would you mind telling me what that does? – iank Jul 03 '12 at 19:42
  • Yes. It's a way to use dynamic SQL. That is you can construct a SQL string and execute it in place. Try to minimize this kind of stuff in your code. DDL can't be used directly in PL/SQL. – Alex Gitelman Jul 03 '12 at 19:43
0

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.

Padmarag
  • 7,067
  • 1
  • 25
  • 29
  • I use the sequence in the loop, basically outputting it into an INSERT statement, so I thought I created it before using it? – iank Jul 03 '12 at 19:36