I'm trying to use a standalone external procedure to be ran from within a cursor, take a variable and throw it back to the procedure, where the procedure will insert the values into another table. Getting errors.
PROCEDURE:
CREATE OR REPLACE PROCEDURE proc_test
(p_inn newtable.totalhours%TYPE) AS
BEGIN
p_inn := p_inn - p_inn + 1;
INSERT INTO proceduretest (value)
VALUES (p_inn);
END proc_test;
/
The code with cursors
SET SERVEROUTPUT ON
DECLARE
v_idno paydata1.idno%TYPE;
v_name paydata1.name%TYPE;
v_hrs paytran1.hourswk%TYPE;
v_hrsT paytran1.hourswk%TYPE;
v_code paydata1.jobcode%TYPE;
v_payh paydata1.payhr%TYPE;
v_sal paydata1.salary%TYPE;
v_OT NUMBER(6,2);
CURSOR payday IS
SELECT idno, name FROM paydata1
ORDER BY idno;
CURSOR paytray IS
SELECT hourswk FROM paytran1
WHERE v_idno = idno
ORDER BY idno;
CURSOR total IS
SELECT idno, name, jobcode, salary, payhr
FROM paydata1
WHERE v_idno = idno;
BEGIN
OPEN payday;
LOOP
FETCH payday INTO v_idno, v_name;
EXIT WHEN payday%NOTFOUND;
IF paytray%ISOPEN THEN
CLOSE paytray;
END IF;
OPEN paytray;
v_hrsT := 0;
LOOP
FETCH paytray INTO v_hrs;
EXIT WHEN paytray%NOTFOUND;
v_hrsT := v_hrsT + v_hrs;
END LOOP;
OPEN total;
FETCH total INTO v_idno, v_name, v_code, v_sal, v_payh;
WHILE total%FOUND LOOP
IF v_code = 'S' THEN
v_sal := v_sal / 52;
DBMS_OUTPUT.PUT_LINE(v_name || ' - Total hours worked is: ' || v_hrsT);
proc_test (v_hrsT); <------------------
ELSE
IF v_hrsT > 40 THEN
v_OT := v_hrsT - 40;
v_OT := v_OT * (v_payh * 1.5);
v_OT := v_OT + (40 * v_payh);
v_sal := v_OT;
ELSE
v_sal := v_hrsT * v_payh;
END IF;
DBMS_OUTPUT.PUT_LINE(v_name || ' - Total hours worked is: ' || v_hrsT);
proc_test (v_hrsT); <---------------
END IF;
FETCH total INTO v_idno, v_name, v_code, v_sal, v_payh;
END LOOP;
CLOSE total;
CLOSE paytray;
END LOOP;
CLOSE payday;
END;
/
SET SERVEROUTPUT OFF
I apologize, some of the code got messy towards the end, but I've drawn arrows to where the procedure is called.
ERRORS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: Statement ignored
5/1 PLS-00363: expression 'P_INN' cannot be used as an assignment
target
Parameters with undefined direction default to IN. We cannot assign (change) the value of an IN a parameter. You have not defined the direction of P_INN so it's IN by default, therefore you cannot do this
p_inn := p_inn - p_inn + 1;
That's what the error message is telling you.
If you don't need the manipulated value for anything other than inserting it just do that....
CREATE OR REPLACE PROCEDURE proc_test
(p_inn newtable.totalhours%TYPE) AS
BEGIN
INSERT INTO proceduretest (value)
VALUES (p_inn - p_inn + 1);
END proc_test;
/
Otherwise use a local variable:
CREATE OR REPLACE PROCEDURE proc_test
(p_inn IN newtable.totalhours%TYPE)
AS
l_inn newtable.totalhours%TYPE;
BEGIN
l_inn := p_inn - p_inn + 1;
INSERT INTO proceduretest (value)
VALUES (l_inn);
END proc_test;
/