Creating and using Sequence in Oracle stored procedure - Sequence doesn't exist

Question

    DECLARE
      v_emp_id NUMBER;
      empid    NUMBER;
      stmt     VARCHAR2(1000);
    BEGIN
      SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
      BEGIN
        dbms_output.put_line(v_emp_id );
        stmt := 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
        EXECUTE IMMEDIATE stmt;
        COMMIT;
      END;
      insert into emp_new select emp_seq.nextval,empname from (select * from employee where active = 0);
      dbms_output.put_line(empid);
    END;
    /

When executing above procedure, I get the following errors ORA-06550: line 13, column 10: PL/SQL: ORA-02289: sequence does not exist ORA-06550: line 13, column 3: PL/SQL: SQL Statement ignored

But when executing the below procedure, it is successful and sequence is created.

    DECLARE
      v_emp_id NUMBER;
      empid    NUMBER;
      stmt     VARCHAR2(1000);
    BEGIN
      SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
      BEGIN
        dbms_output.put_line(v_emp_id );
        stmt := 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
        EXECUTE IMMEDIATE stmt;
        COMMIT;
      END;
      dbms_output.put_line(empid);
    END;
    /

Solution 1

During compile time sequence not exists so compiler returns error. Execute immediate will be executed on runtime but compiler don't know that it will create sequence you called later in code.

create or replace procedure createtest as
begin 
execute immediate 'create table t1 (c1 number)';
insert into t1 values (1);
end;
/

Gives the same error as yours as table t1 not exists. So insert statement is invalid. This is error during compilation of PL/SQL.

create table t1 (c1 number);

After that I can create procedure but when I do:

exec createtest;

I got error that table already exists. But compiler didn't knew that I'm trying create again same table so it will be returned on run not during compilation.

If you really need to do it such way please do:

create or replace procedure createtest as
begin 
execute immediate 'create table t1 (c1 number)';
execute immediate 'insert into t1 values (1)';
end;
/

In you case:

execute immediate 'SELECT emp_seq.nextval FROM dual' INTO empid;

[EDIT] What I understand is you want to be sure that sequence is set to max(empid) so please do not try to create it inside procedure. Create sequence once then in procedure body execute:

select max(empid) into maxempid from employee;
select emp_seq.currval into maxseq from dual;
if(empid-maxseq>0) then
execute immediate 'alter sequence emp_seq increment by ' || empid-maxseq;
end if;

Solution 2

You should create sequence statical before you create procedure.

DROP SEQUENCE emp_seq
/

BEGIN
  SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
  dbms_output.put_line(v_emp_id );
  execute immediate 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
END;
/

and then CREATE OR REPLACE FUNCTION/PROCEDURE/PACKAGE .... which refers to your sequence

Comments

[Doubtful]

Thanks:) I am using the seq in insert with select statement. Any ideas on how to do it? Sorry I have very little idea on oracle procedures

[Kacper]

execute immediate 'SELECT emp_seq.nextval FROM dual' INTO empid; This is how to use your newly created sequence in select and return value to empid.

[Doubtful]

insert into emp_new select (emp_seq.nextval,empname) from employee;

[Doubtful]

I have updated the question asked. Can you look at it

[Kacper]

You can obtain it using cursor that is selecting value from sequence but it is really unnecessary complication. Please create sequence once. Then create procedure without create sequence statement inside.

[Kacper]

I've posted you edit to my answer. I would create sequence once and then only take care of setting proper max value.