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;
