Sequence does not exist (pl/sql)-CodePudding

I cannot figure out why I'm getting this sql error. Sorry in advance for the long example. (Trying to code a package that contains functions to add an employee to an employee table, given the personal data of an employee: name, phone, mail, etc). Tried declaring the sequence inside the package at first but it didn't work.

--5/9       PL/SQL: SQL Statement ignored
--5/16      PL/SQL: ORA-02289: sequence does not exist



create sequence sec_cdu 
            maxvalue 99999
            start with (select max(employee_id) 1 from employees)
            increment by 1;
    /
CREATE OR REPLACE PACKAGE pachet1_cdu AS
    function get_empId return number;
    
    function get_managerId(mngLn employees.last_name%type, mngFn employees.first_name%type)
    return number;
    
    function get_departmentId(depName departments.department_name%type)
    return number;
    
    function get_jobId(jbName jobs.job_title%type)
    return number;
    
    function get_salary(depId departments.department_id%type, 
                        jobId jobs.job_id%type)
    return number;
    
    procedure add_employee(empFn emp_cdu.first_name%TYPE, 
    empLn emp_cdu.last_name%TYPE, 
    empEm emp_cdu.email%TYPE, 
    empPn emp_cdu.phone_number%TYPE,   
    empHd emp_cdu.hire_date%TYPE:=SYSDATE,   
    empJid emp_cdu.job_id%TYPE,        
    empSal emp_cdu.salary%TYPE,
    empCom emp_cdu.commission_pct%TYPE:=0, 
    empMid emp_cdu.manager_id%TYPE,  
    empDid emp_cdu.department_id%TYPE);
end pachet1_cdu;
/
create or replace package body pachet1_cdu as
    function get_empId
        return number is empid number;
    begin
        select sec_cdu.nextval into empid
        from dual;
    return empid;
    end get_empId;
    
    function get_managerId(mngLn employees.last_name%type, mngFn employees.first_name%type)
        return number is mngId number;
    begin 
        select employee_id into mngId
        from employees 
        where upper(first_name) = upper(mngFn)
        and upper(last_name) = upper(mngLn);
    return mngId;
    end get_managerId;
    
    function get_departmentId(
    depName departments.department_name%type
    )
        return number is depId number;
    begin
        select department_id into depId
        from departments 
        where upper(department_name) = upper(depName);
    return depId;
    end get_departmentId;
    
    function get_jobId(jbName jobs.job_title%type)
        return number is jobId number;
    begin
        select job_id into jobId 
        from jobs j
        where upper(jbName) = upper(j.job_title);
    return jobId;
    end get_jobId;
    
    function get_salary(depId departments.department_id%type,jobId jobs.job_id%type)
        return number is val number;
    begin
        select min(salary) into val
        from employees e 
        where e.job_id = jobId
        and e.department_id = depId;
    return val;
    end get_salary;
    
    procedure add_employee(empFn emp_cdu.first_name%TYPE, 
    empLn emp_cdu.last_name%TYPE, 
    empEm emp_cdu.email%TYPE, 
    empPn emp_cdu.phone_number%TYPE,   
    empHd emp_cdu.hire_date%TYPE:=SYSDATE,   
    empJid emp_cdu.job_id%TYPE,        
    empSal emp_cdu.salary%TYPE,
    empCom emp_cdu.commission_pct%TYPE:=0, 
    empMid emp_cdu.manager_id%TYPE,  
    empDid emp_cdu.department_id%TYPE)
     is
    begin
        insert into emp_cdu values
    (get_empid, empFn, empLn, empEm, empPn, empHd, 
    empJid, empSal, empCom, empMid, empDid);
    end add_employee;
end pachet1_cdu;
/

CodePudding user response：

The 1st error (regarding a sequence) is that you can't use a subquery (as OldProgrammer already commented):

SQL> create sequence sec_cdu
  2              maxvalue 99999
  3              start with (select max(employee_id) 1 from employees)
  4              increment by 1;
            start with (select max(employee_id) 1 from employees)
                       *
ERROR at line 3:
ORA-01722: invalid number

Therefore, first find the starting value, and then use it for the sequence:

SQL> select max(employee_id) from employees;

MAX(EMPLOYEE_ID)
----------------
             100

SQL> create sequence sec_cdu
  2              maxvalue 99999
  3              start with 101
  4              increment by 1;

Sequence created.

SQL>

The 2nd error (actually, a mistake) is a slash that follows the create sequence statement because it'll re-execute previous statement, which means that you'll get this:

SQL> /
create sequence sec_cdu
                *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

But OK, nothing really happened, no harm done, but - it looks ugly. As long as PL/SQL requires the slash, SQL commands don't.