Creating and using Sequence in Oracle stored procedure - Sequence doesn't exist
<pre><code>    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;
    /
</code></pre>

<p>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</p>

<p>But when executing the below procedure, it is successful and sequence is created.</p>

<pre><code>    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;
    /
</code></pre>