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

Improve this question

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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?
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?
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.
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?
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.
|
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';
Improve this answer

4 Comments

I tried moving it after BEGIN and it still gave the same error. I'll look into moving it outside the procedure.
I added update. You can't use CREATE directly. Must use EXECUTE IMMEDIATE, although it's usefulness is still arguable.
I'll try that. Since I'm still learning, would you mind telling me what that does?
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.
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.

Improve this answer

1 Comment

I use the sequence in the loop, basically outputting it into an INSERT statement, so I thought I created it before using it?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.