create/declare a sequence in a stored procedure in oracle

Question

I have a table non_employee with emp_no as the primary key and a package with a procedure to insert in to this table.

I need to be able to autoincrement the emp_no when the procedure is run. I tried creating a sequence within the procedure like this, but getting errors. please see below and advice.

CREATE OR REPLACE PACKAGE BODY WFDDEV."ERD" IS

create SEQUENCE @seq_emp_nmbr;

PROCEDURE INS_NON_EMPLOYEES
(
in_DATE_ADDED        DATE,  
in_EMPLOYEE_NAME     VARCHAR2,
in_DEPT_ID           VARCHAR2,
in_SUB_DEPARTMENT    VARCHAR2,
in_LOCATION          VARCHAR2,
in_WORK_TEL_NO       VARCHAR2,
in_TOUR              VARCHAR2,
in_REST_DAYS         VARCHAR2,
in_HOME_ADDRESS      VARCHAR2,
in_CITY              VARCHAR2,
in_STATE             VARCHAR2,
in_ZIP               VARCHAR2,
in_HOME_TEL_NO       VARCHAR2,
in_GENDER            VARCHAR2,
in_RACE              VARCHAR2,
in_DATE_OF_BIRTH     DATE,
in_AGE               VARCHAR2,
in_HIRE_DATE         DATE,
in_UNION_AFFILIATION VARCHAR2,
in_TITLE             VARCHAR2,
in_NON_EE_INDICATOR  VARCHAR2
) IS
BEGIN



INSERT INTO WFDDEV.NON_EMPLOYEES
  (
  EMP_NO,
  DATE_ADDED,
  EMPLOYEE_NAME,
  DEPT_ID,
  SUB_DEPARTMENT,
  LOCATION,
  WORK_TEL_NO,
  TOUR,
  REST_DAYS,
  HOME_ADDRESS,
  CITY,
  STATE,
  ZIP,
  HOME_TEL_NO,
  GENDER,
  RACE,
  DATE_OF_BIRTH,
  AGE,
  HIRE_DATE,
  UNION_AFFILIATION,
  TITLE,
  NON_EE_INDICATOR
  )
VALUES
  (
 emp_no.NEXTVAL,
  in_DATE_ADDED,
  in_EMPLOYEE_NAME,
  in_DEPT_ID,
  in_SUB_DEPARTMENT,
  in_LOCATION,
  in_WORK_TEL_NO,
  in_TOUR,
  in_REST_DAYS,
  in_HOME_ADDRESS,
  in_CITY,
  in_STATE,
  in_ZIP,
  in_HOME_TEL_NO,
  in_GENDER,
  in_RACE,
  in_DATE_OF_BIRTH,
  in_AGE,
  in_HIRE_DATE,
  in_UNION_AFFILIATION,
  in_TITLE,
  in_NON_EE_INDICATOR
  );
END;

Im getting PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin end function pragma procedure subtype type

error with this...

You will have to use EXECUTE IMMEDIATE CREATE SEQUENCE.... to be able to do that from a PLSQL. — Anjan Biswas
– Anjan Biswas, Commented Sep 27, 2012 at 15:21

WoMo · Accepted Answer · 2012-09-27 15:50:24Z

5

You need to create the sequence just once outside of the package as a separate database object. Then, in the insert statement in your package body you can reference the sequence to get the next value.

answered Sep 27, 2012 at 15:50

WoMo

7,2862 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anjan Biswas · Accepted Answer · 2012-09-27 15:30:32Z

2

Try-

EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_NAME  START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER';

This should be inside a Procedure or Function body and not in the Declaration section i.e. this should be treated as a executable statement.

Creating a sequence using Dynamic SQL is a bad idea and I am not sure why you would want to do that. However, if you are creating a sequence dynamically then remember to drop it as well after finishing up, using

EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_NAME';

This way you would atleast not run into errors (like ORA-00955) while calling the package procedure.

edited Sep 27, 2012 at 15:30

answered Sep 27, 2012 at 15:24

Anjan Biswas

8,0126 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

create/declare a sequence in a stored procedure in oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related