OraFAQ Forum: Forms » Calling a procedure from another procedure

Home » Developer & Programmer » Forms » Calling a procedure from another procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calling a procedure from another procedure [message #149051]

Tue, 29 November 2005 20:01

anton_pm
Messages: 1
Registered: November 2005

Junior Member

Hi Guys,

I have created a package to base a form on. The package contains some procedures, one of which is an Update procedure which can be seen below. I have a procedure in another package that I am trying to call. Basically the second procedure is called to implement a constraint. However, I am unsure as to what I should pass into this procedure, as a package/procedure for basing a form on is different to what I have come across before.

CREATE OR REPLACE package staff_dml IS
TYPE staff_rec IS RECORD (staff_id NUMBER(7),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
tel_no NUMBER(11),
manager_id NUMBER(7),
position VARCHAR2(20),
rest_id NUMBER(7),
grade VARCHAR2(1),
wage NUMBER(7,2));
TYPE staff_cursor IS REF CURSOR RETURN staff_rec;
TYPE staff_table IS TABLE OF staff_rec INDEX BY BINARY_INTEGER;
PROCEDURE staff_update (data IN OUT staff_table);
PROCEDURE staff_find (data IN OUT staff_cursor);
PROCEDURE staff_lock (data IN OUT staff_table);
END;
/

CREATE OR REPLACE PACKAGE BODY staff_dml IS
PROCEDURE staff_lock (data IN OUT staff_table)
IS
temp NUMBER;
BEGIN
SELECT staff_id into temp
FROM staff
WHERE staff_id = data(1).staff_id

FOR UPDATE;
END staff_lock;

PROCEDURE staff_find (data IN OUT staff_cursor)
IS
BEGIN
OPEN data FOR SELECT staff_id, first_name, last_name, tel_no, manager_id, position, rest_id, grade, wage
FROM staff;
END staff_find;

PROCEDURE staff_update
(data IN OUT staff_table)
IS
BEGIN

IF data(1).wage != data(1).wage THEN
special_pkg.staff_chk(data(1).wage,data(1).grade);
END IF;

IF data(1).grade != data(1).grade THEN
special_pkg.staff_chk(data(1).wage,data(1).grade);
END IF;

UPDATE staff
SET first_name = data(1).first_name, last_name = data(1).last_name, tel_no = data(1).tel_no, manager_id = data(1).manager_id,
position = data(1).position, rest_id = data(1).rest_id, grade = data(1).grade, wage = data(1).wage
WHERE staff_id = data(1).staff_id;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END staff_update;
END;
/

The area I an concerned about is:

IF data(1).wage != data(1).wage THEN
special_pkg.staff_chk(data(1).wage,data(1).grade);
END IF;

IF data(1).grade != data(1).grade THEN
special_pkg.staff_chk(data(1).wage,data(1).grade);
END IF;

I do not know what I should be passing into the special_pkg.staff_chk procedure which accepts two number inputs. I want to use this package to compare the new inputs on this to the existing ones (this is carried out by the procedure. I have tried using :new. but this is not accepted. If I leave in the EXCEPTION then i get no errors from the form, but it will not save any changes I make on the database. If I remove the EXCEPTION then I get an error from the form - FRM-40735 UPDATE-PROCEDURE trigger raised unhandled exception ORA-04098. I am pretty sure this is because I am not supplying the correct variables to the called procedure,

The called procedure is below (tested and working):

PROCEDURE staff_chk
(p_wage IN NUMBER,
p_grade IN NUMBER)
IS
BEGIN
IF p_wage BETWEEN 0 AND 7.00 AND p_grade != 'C' THEN
RAISE_APPLICATION_ERROR(-20002, 'The Incorrect grade has been applied to this employee');

ELSIF p_wage BETWEEN 7.01 AND 10 AND p_grade != 'B' THEN
RAISE_APPLICATION_ERROR(-20003, 'The Incorrect grade has been applied to this employee');

ELSIF p_wage BETWEEN 10.01 AND 20 AND p_grade != 'A' THEN
RAISE_APPLICATION_ERROR(-20004, 'The Incorrect grade has been applied to this employee');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Staff member does not exist');

END staff_chk;
END;
/

Any help would be greatly appreciated.

Thanks,

Anton

p.s sorry if i have made any mistakes in my posting, am new to this forum, might have neglected something, if so apols!

Report message to a moderator