#!/bin/ksh

$1=10;               #runtime input value
$2=30;               #runtime input value
$3=50;               #runtime input value
$4='20150114'; #yyyymmdd input value

echo "running Oracle procedure"
(
$ORACLE_HOME/bin/sqlplus -s -l

set linesize 100

var v_empid  number,
var v_name   varchar2;
var v_last   varchar2;
var v_dt     varchar2;

alter session set nls_date_format = 'mm-dd-yyyy hh24:mi:ss';
exec pkg.proc($1, $2, $3, $4,:v_empid, :v_name, :v_last, :v_dt);

) > $logFile

=== Oracle package/procedure ===
CREATE OR REPLACE PACKAGE pkg
AS

   procedure proc (lFirst    IN number,
                   lSecond   IN number,
                   lThird    IN number,
                   lFour     IN date,
                     v_empid   OUT number,
                     v_name    OUT varchar2,
                     v_last    OUT varchar2,
                     v_dt      OUT date);

END pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg
AS

PROCEDURE proc (lFirst    IN number,
                lSecond   IN number,
                lThird    IN number,
                lFour     IN date,
                  v_empid   OUT number,
                  v_name    OUT varchar2,
                  v_last    OUT varchar2,
                  v_dt      OUT date)
  
IS
BEGIN

SELECT dt, emp_id, first_name, last_name
INTO v_dt, v_empid, v_name, v_last
FROM employees
WHERE emp_id in (lFirst, lSecond)
AND dt >= to_date(lFour, 'yyyymmdd') - 1
AND date < date + INTERVAL '(lThird)' MINUTE;

END proc;

END pkg;
/