PLS-00103 error when trying to compile a Package

Question

PLS-00103 error when trying to compile a Package

Navigation

#1 by (2 votes)

0

I'm trying to compile the package below and the error is being returned: Error (9,3): PLS-00103: Found the "BEGIN" symbol 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;

oracle

asked by anonymous 09.03.2014 / 05:42

1 answer

List / Static menu showing bank option Search for HTML within an iframe

score 2 · Accepted Answer

Packages in Oracle are broken down into specification and implementation ( body ).

The implementation of procedures and functions, as well as variable declarations, must be done using the CREATE PACKAGE BODY .

An example, extracted from 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 question procedure

Creating a self-incrementing sequence within the procedure will probably not have the effect you want for two reasons:

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

Even creating SEQUENCE successfully would always start with the same value. Why do not you just create it together with the table once?