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;
/
Best Answer
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.
Gives the same error as yours as table t1 not exists. So insert statement is invalid. This is error during compilation of PL/SQL.
After that I can create procedure but when I do:
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:
In you case:
[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: