Total Pageviews

August 3, 2015

8/03/2015 12:07:00 PM
ebiztechnics
Oracle Forms Creation Using Database Procedure

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

Oracle Forms 1





For Insert/update/delete/lock  tab :

Procedure Name:BONUS_PKG.BONUS_QUERY

Oracle Forms

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



 
Related Posts Plugin for WordPress, Blogger...