Geek asks and answers

PL / SQL: error creating sequence

I am new to PL / SQL and use Oracle SQL Developer to write a procedure that uses a sequence to generate a primary key for some existing data, to write to another database.

This code is under the 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 throws error PLS-00103, saying that it encountered a CREATE character, expecting the following: begin, function, package, pragma, procedure, ...

I follow an example: http://www.techonthenet.com/oracle/sequences.php

+5
source share
3 answers

, , , DDL, PL/SQL. , execute immediate.

Alex, declare. :

begin

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

, Padmarag , , PL/SQL. , . DDL PL/SQL - ; .

, Oracle . 11, . 11g, , .nextval :

declare    
   l_seq number;    
begin

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

11 , ( DML) select, :

declare
   l_seq number;
begin

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

, , . , . script, , .

+7

DECLARE. BEGIN. , , . , .

Update

, , BEGIN/END :

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

You will need to create a sequence before using it.

And in 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 create Create from PL / SQL, but I think that is not what you want here.

0
source

More articles:


All Articles