Experts,
I pass some arguments through script to Oracle procedure, use input value and pass it on to procedure.
Oracle procedure gets input value, run query and logs everything in the logfile.
I'm facing with few challenges, seems to be syntax and/or datatype error
1. Even though I pass all required parameter still script run complains about wrong number or type of arguments.
There are three numbers and one date variable
2. How to pass variable value with INTERVAL function, it should take lThird variable value e.g. `AND date >= date + INTERVAL '30' MINUTE;` but somehow after trying different variations still get error as invalid interavl.
3. Can anybody see more changes to this?
$1=10;
$2=30;
$3=50
$4='20150113';
echo "running Oracle procedure"
(
set linesize 100
exec pkg.proc($1, $2, $3, $4);
) > $logFile
if [ $? == 0 ]
then
EXIT_VAL=0
=== Oracle package/procedure ===
CREATE OR REPLACE PACKAGE pkg
AS
procedure proc (lFirst IN number,
lSecond IN number,
lThird IN number,
lFour IN date,
curReturn OUT sys_refcursor);
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
AS
PROCEDURE proc (lFirst IN number,
lSecond IN number,
lThird IN number,
lFour IN date,
curReturn OUT sys_refcursor)
IS
BEGIN
OPEN curReturn FOR
SELECT date,
emp_id,
first_name,
last_name
FROM employees
WHERE emp_id in (lFirst, lSecond)
AND date >= date + INTERVAL '(lThird)' MINUTE
AND date = TO_DATE(lFour, 'yyyymmdd') -1;
END proc;
END pkg;
/
Error:
PLS-306: wrong number or types of argument in call to proc