PLS-00103 error when trying to compile a Package

Question

I am trying to compile the package below and the error is being returned: Error(9,3): PLS-00103: Found the symbol "BEGIN" when one of the following symbols was expected: language

CREATE OR REPLACE PACKAGE PCK_TB_ESTADO
IS
  PROCEDURE PRC_INSERE
  (P_NM_REDE_FUNCIONARIO IN TB_FUNCIONARIO.NM_REDE_FUNCIONARIO%TYPE,
  P_DS_ESTADO IN TB_ESTADO.DS_ESTADO%TYPE,
  P_ID_UF IN TB_ESTADO.ID_UF,
  P_MENS OUT VARCHAR2)
  IS
  BEGIN
    CREATE SEQUENCE SEQ_ESTADO
      MINVALUE 1
      MAXVALUE 99
      START WITH 1
      INCREMENT BY 1;
    INSERT INTO TB_ESTADO
    VALUES (SEQ_ESTADO.NEXTVAL,P_DS_ESTADO,P_ID_UF,SYSDATE,P_NM_REDE_FUNCIONARIO);
    COMMIT;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK;
        P_MENS := 'Você tentou executar um comando INSERT ou UPDATE que criou um valor duplicado em um campo restrito por um index único.';
      WHEN OTHERS THEN
        ROLLBACK;
        P_MENS := 'Erro.';
  END;
END PCK_TB_ESTADO;

Answer 1

Packages in Oracle are divided into specification (Specification) and implementation (body).

The implementation of procedures and functions, in addition to variable statements, must be done using the command CREATE PACKAGE BODY.

An example, drawn from of other documentation:

CREATE OR REPLACE PACKAGE Emp_package AS
    TYPE Emp_data_type IS RECORD (
        Emp_number NUMBER,
        Hire_date  VARCHAR2(12),
        Emp_name   VARCHAR2(10));
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_type);
END;

CREATE OR REPLACE PACKAGE BODY Emp_package AS
    PROCEDURE Get_emp_data
        (Emp_data IN OUT Emp_data_type) IS
   BEGIN
       SELECT Empno, Ename, TO_CHAR(Hiredate, 'DD/MON/YY')
           INTO Emp_data
           FROM Emp_tab
           WHERE Empno = Emp_data.Emp_number;
   END;
END;

Notes on the Outcome of the Question

Creating a auto-increment sequence within the Precedent will probably not have the effect you want for two reasons:

1. An object cannot be used in the same block that was created, because Oracle creates in a transactional way. At least in the versions I use, this code won’t even compile. However, you can use the command EXECUTE IMMEDIATE to force execution and immediate change.

2. Even creating the SEQUENCE successfully, it would always start with the same value. Why not just create it along with the table, once?