Database - create package and procedure

Here showing you an insert events package and procedure creation.

Package creation:

create or replace PACKAGE "PCK_PROVISIONING"

AS

  /* enter package declarations (types, exceptions, methods etc) here */

PROCEDURE INSERT_EVENT_IN_PROGRESS(

    EMP_NUMBER     VARCHAR2,

    EMP_COUNTRY    VARCHAR2,

    BPEL_INST_ID   VARCHAR2,

    EMP_LAST_NAME  VARCHAR2,

    EMP_FIRST_NAME VARCHAR2,

    EV_ID OUT NUMBER );

END pck_provisioning;

Package body:

create or replace PACKAGE BODY "PCK_PROVISIONING"

AS

PROCEDURE INSERT_EVENT_IN_PROGRESS

  (

    EMP_NUMBER     VARCHAR2,

    EMP_COUNTRY    VARCHAR2,

    BPEL_INST_ID   VARCHAR2,

    EMP_LAST_NAME  VARCHAR2,

    EMP_FIRST_NAME VARCHAR2,

    EV_ID OUT NUMBER

  )

AS

BEGIN

/* calling the Insert_Event() procedure*/

  INSERT_EVENT

  (

    EV_TYPE => 'T', EMP_NUMBER => EMP_NUMBER , EMP_COUNTRY => EMP_COUNTRY, EV_STATUS => 2,            -- 2.- In Progress

    BPEL_INST_ID => BPEL_INST_ID, LAST_NAME => EMP_LAST_NAME, FIRST_NAME => EMP_FIRST_NAME, STS => 1, -- 1.- Active

    EV_ID => EV_ID

  );

END INSERT_EVENT_IN_PROGRESS;

/* called the Insert_Event() procedure body with Table.field%type */

PROCEDURE INSERT_EVENT(

    EV_TYPE EMPLOYEE_EVENT.EVENT_TYPE%type, 

    EMP_NUMBER EMPLOYEE_EVENT.EMPLOYEE_NUMBER%type,

    EMP_COUNTRY VARCHAR2,

    EV_STATUS EMPLOYEE_EVENT.EVENT_STATUS%type,

    STS EMPLOYEE_EVENT.STATUS%type,

    BPEL_INST_ID EMPLOYEE_EVENT.BPEL_INSTANCE_ID%type,

    LAST_NAME EMPLOYEE_EVENT.EMPLOYEE_LAST_NAME%type,

    FIRST_NAME EMPLOYEE_EVENT.EMPLOYEE_FIRST_NAME%type,

    EV_ID OUT EMPLOYEE_EVENT.EMPLOYEE_EVENT_ID%type )

AS

BEGIN

--Calling a sequence to get the event id.

  SELECT EMPLOYEE_EVENT_SEQ.NEXTVAL INTO EV_ID FROM DUAL;

--Inserting data to Employee_event table

  INSERT INTO EMPLOYEE_EVENT(

      EVENT_DATETIME,

      EVENT_TYPE,

      EMPLOYEE_NUMBER,

      EMPLOYEE_COUNTRY,

      EVENT_STATUS,

      STATUS,

      BPEL_INSTANCE_ID,

      EMPLOYEE_FIRST_NAME,

      EMPLOYEE_LAST_NAME,

      EMPLOYEE_EVENT_ID

    )

    VALUES(

      SYSDATE,

      EV_TYPE,

      EMP_NUMBER,

      EMP_COUNTRY,

      EV_STATUS,

      STS,

      BPEL_INST_ID,

      LAST_NAME,

      FIRST_NAME,

      EV_ID

    );

END INSERT_EVENT;

END pck_provisioning;