Please find the solution as follows
Step1: Create a table named Bonus
---------------------------------
CREATE TABLE BONUS(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(50),
JOB VARCHAR2(20),
SAL NUMBER,
COMM NUMBER);
Step2: Create a package spec at the database level
---------------------------------------------------
PACKAGE bonus_pkg IS
TYPE bonus_rec IS RECORD(
empno bonus.empno%TYPE,
ename bonus.ename%TYPE,
job bonus.job%TYPE,
sal bonus.sal%TYPE,
comm bonus.comm%TYPE);
TYPE b_cursor IS REF CURSOR RETURN bonus_rec;
-- Statement below needed if block is based on Table of Records
TYPE bontab IS TABLE OF bonus_rec INDEX BY BINARY_INTEGER;
-- Statement below needed if using Ref Cursor
PROCEDURE bonus_refcur(bonus_data IN OUT b_cursor);
-- Statement below needed if using Table of Records
PROCEDURE bonus_query(bonus_data IN OUT bontab);
--Statements below needed for both Ref Cursor and Table of Records
PROCEDURE bonus_insert(r IN bonus_rec);
PROCEDURE bonus_lock(s IN bonus.empno%TYPE);
PROCEDURE bonus_update(t IN bonus_rec);
PROCEDURE bonus_delete(t IN bonus_rec);
-- If this last function is not included you cannot use the
-- Query -> count hits from the default menu of the forms and
-- will get error frm-41003 Function cannot be performed here.
FUNCTION count_query_ RETURN number;
END bonus_pkg;
Step 3. Create the package body
--------------------------------
PACKAGE BODY bonus_pkg IS
PROCEDURE bonus_query(bonus_data IN OUT bontab) IS
ii NUMBER;
CURSOR bonselect IS
SELECT empno, ename, job, sal, comm FROM bonus;
BEGIN
OPEN bonselect;
ii := 1;
LOOP
FETCH bonselect INTO
bonus_data( ii ).empno,
bonus_data( ii ).ename,
bonus_data( ii ).job,
bonus_data( ii ).sal,
bonus_data( ii ).comm;
EXIT WHEN bonselect%NOTFOUND;
ii := ii + 1;
END LOOP;
END bonus_query;
PROCEDURE bonus_refcur(bonus_data IN OUT b_cursor) IS
BEGIN
OPEN bonus_data FOR SELECT empno, ename, job, sal, comm FROM bonus;
END bonus_refcur;
PROCEDURE bonus_insert(r IN bonus_rec) IS
BEGIN
INSERT INTO bonus VALUES(r.empno, r.ename, r.job, r.sal, r.comm);
END bonus_insert;
PROCEDURE bonus_lock(s IN bonus.empno%TYPE) IS
v_rownum NUMBER;
BEGIN
SELECT empno INTO v_rownum FROM bonus WHERE empno=s FOR UPDATE OF ename;
END bonus_lock;
PROCEDURE bonus_update(t IN bonus_rec) IS
BEGIN
UPDATE bonus SET ename=t.ename, job=t.job, sal=t.sal, comm=t.comm
WHERE empno=t.empno;
END bonus_update;
PROCEDURE bonus_delete(t IN bonus_rec) IS
BEGIN
DELETE FROM bonus WHERE empno=t.empno;
END bonus_delete;
FUNCTION count_query_ RETURN NUMBER IS
r NUMBER;
BEGIN
SELECT COUNT(*) INTO r FROM bonus;
RETURN r;
END count_query_;
END bonus_pkg;
-----
creation of form
Step 1:
Database Source Name:bonus_pkg.bonus_refcur
For Insert/update/delete/lock tab :
Procedure Name:BONUS_PKG.BONUS_QUERY
create a form by mentioning the procedure name in the specified column
After creating form,
code appropriate triggers ON-INSERT,ON-UPDATE,ON-DELETE,ON-LOCK
Compile the form and run the form
--All the Best
