title: Creating and using Sequence in Oracle stored procedure - Sequence doesn't exist
body: <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>