CREATE OR REPLACE PROCEDURE TEST_PROC 
(
    in_sbset_id         IN  NUMBER
    ,test_date          IN  DATE
    ,test_timestamp     IN  TIMESTAMP
    ,test_timestamptz   IN  TIMESTAMP WITH TIME ZONE
    ,test_timestampltz  IN  TIMESTAMP WITH LOCAL TIME ZONE
    ,out_result         OUT VARCHAR2
)

AS
    l_sql_statement VARCHAR(4000);

BEGIN
    DBMS_OUTPUT.PUT_LINE('Started');
    DBMS_OUTPUT.PUT_LINE('test_date : ' || test_date);
    DBMS_OUTPUT.PUT_LINE('test_timestamp : ' || test_timestamp);
    DBMS_OUTPUT.PUT_LINE('test_timestamptz : ' || test_timestamptz);
    DBMS_OUTPUT.PUT_LINE('test_timestampltz : ' || test_timestampltz);
    l_sql_statement := 'INSERT INTO TEST_TABLE VALUES (
                           '||in_sbset_id||',
                           TO_DATE('''||test_date||''',''DD-MON-YYYY'') ,
                           TO_TIMESTAMP('''||test_timestamp||''') ,
                           TO_TIMESTAMP_TZ('''||test_timestamptz||''') ,
                           CAST('''||test_timestampltz||''' AS TIMESTAMP WITH LOCAL TIME ZONE))';
    COMMIT;
        
    DBMS_OUTPUT.PUT_LINE('l_sql_statement ::  '||l_sql_statement);
    EXECUTE IMMEDIATE l_sql_statement;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Completed');
    out_result := 'SUCCESSFUL';
END TEST_PROC;

CREATE OR REPLACE PROCEDURE PROC_WITH_CUSTOM_IN_PARAM
(
    pPairsCount     IN  NUMBER
    ,pPrefix        IN  VARCHAR2
    ,pINMUIDS       IN  STRARRAY32
    ,pMUIDs         OUT STRARRAY32
    ,pAuthKeys      OUT STRARRAY49
    ,pDemoPeriod    OUT NUMBER
    ,pTransID       OUT NUMBER
    ,pErrorCd       OUT NUMBER
    ,pErrorMsg      OUT VARCHAR2
)

AS BEGIN
    select COUNT('s') Into pTransID from ACTRESS a;
    pMUIDs := STRARRAY32();
    pMUIDs.EXTEND(1);
    pAuthKeys := STRARRAY49();
    pAuthKeys.EXTEND(1);
    pMUIDs(1) := inMUIDS(1);
    pAuthKeys(1) := 'ABCD-r2mi-7vax-xn3m-hahj-2bqa-ev6f-dn3r-225x-ABCD';
    pErrorCd := 0;

    EXCEPTION 
    WHEN OTHERS THEN
        pErrorCd := 1;
        pErrorMsg := 'Some Error Happened.';

END PROC_WITH_CUSTOM_IN_PARAM;

CREATE TABLE employee (

id NUMBER(6),
title VARCHAR2(128),
salary NUMBER(8,2),
hire_date DATE,
constraint pk_employee primary key (id)

);

CREATE TABLE people(

id NUMBER(6),
first_name VARCHAR2(128),
last_name VARCHAR2(128),
employee_id NUMBER(6),
constraint pk_people primary key (id),
foreign key (employee_id) references employee(id)

);

CREATE OR REPLACE TYPE employee_t IS OBJECT(

title VARCHAR2(128),
salary NUMBER(8,2),
hire_date DATE

);
/

CREATE OR REPLACE TYPE employee_tbl IS TABLE OF employee_t;
/

CREATE OR REPLACE PACKAGE payroll_pkg AS

FUNCTION employee_info(employee_id IN NUMBER) RETURN employee_tbl;

END payroll_pkg; 
/

CREATE OR REPLACE PACKAGE BODY payroll_pkg AS

FUNCTION employee_info(employee_id IN NUMBER)

RETURN employee_tbl
AS
   l_employee_tbl employee_tbl := employee_tbl();
BEGIN
  SELECT employee_t(e.title, e.salary, e.hire_date)
    BULK COLLECT INTO l_employee_tbl
   FROM employee e 
WHERE e.id = employee_id;

return l_employee_tbl;
END employee_info;

END payroll_pkg;
/

-- seed data
INSERT INTO employee VALUES (100, 'manager', 50000, TO_DATE('2020-01-02', 'YYYY-MM-DD')); 
INSERT INTO employee VALUES (200, 'clerk', 12345, TO_DATE('2018-10-17', 'YYYY-MM-DD'));

INSERT INTO people VALUES (1, 'Mary', 'Weller', 100);
INSERT INTO people VALUES (2, 'Frank', 'Smith', 200);