7/31/2019 Block by Procedure

1/3

Solution: Forms 6i- Data block using Stored ProcedureYou can read this Note:52778.1 from Metalink.

This document is intended to provide sample code for creating a set of procedures upon which an Oracle Formsdata block can be based. The code makes use of the demonstration table Emp to illustrate this concept.

SCOPE AND APPLICATION:

The code can be used as an example to create different procedures on which the Oracle Forms 5.0 data block isbased. This works for Developer/2000 Release 2.0 and higher only.

INTRODUCTION:Developer/2000 Release 2.0 contains a number of new features and wizards, each described within the online helpsystem.One of the distinct features that this release of Developer/2000 Forms introduces is the Data Block Wizard, in whichusers can easily and quickly create data blocks based upon tables, views, or stored procedures. This enables you touse the 'Ref Cursor' variable (a new version 7.2 feature) which allows you to perform fetches from the results of astored procedure. Another new feature introduced in version 7.3 is the 'Table of Records'.If your data block has multiple validation lookups or has derived fields that are database intensive, this easy methodof partitioning the application logic onto the server can vastly improve the performance of your applications.

Ref Cursor:A Ref Cursor defines a SELECT statement that is the source of the records. The ability to use a Ref Cursor variableis a new 7.2 feature. You can use it to perform array fetches of SELECT statements opened by a server-sideprocedure.

Table of Records:A Table of Records is a PL/SQL v2.3 variable that is essentially an array of rows. You can think of it as an image ofa table contained in memory. The server-side procedure constructs this array, usually based on parameters passedto it, then passes back the resulting set of records to a data block. Forms treats the resulting table of records exactlyas if it were fetching a series of rows from a table.

Below is a sample of code to create a set of stored procedures upon which a Forms data block can be based(performing the functions: SELECT, INSERT, UPDATE, DELETE, LOCK).

PACKAGE emp_pkg ASTYPE emprec IS RECORD(

empno emp.empno%type,ename emp.ename%type,job emp.job%type,sal emp.sal%type,deptno emp.deptno%type);

TYPE empnorec IS RECORD(empno emp.empno%type);

TYPE empcur IS REF CURSOR RETURN emprec;

TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;TYPE empnotab IS TABLE OF empnorec INDEX BY BINARY_INTEGER;

PROCEDURE empquery_refcur(block_data IN OUT empcur, p_deptno IN NUMBER);

PROCEDURE empquery(block_data IN OUT emptab, p_deptno IN NUMBER);PROCEDURE empinsert(block_data IN emptab);PROCEDURE empupdate(block_data IN emptab);PROCEDURE empdelete(block_data IN empnotab);PROCEDURE emplock(block_data IN empnotab);END;

PACKAGE BODY emp_pkg ASPROCEDURE empquery_refcur(block_data IN OUT empcur, p_deptno IN NUMBER) ISBEGINOPEN block_data FOR SELECT empno, ename, job, sal, deptno FROM emp WHERE deptno = NVL(p_deptno,

7/31/2019 Block by Procedure

2/3

deptno) ORDER BY empno;END;

PROCEDURE empquery(block_data IN OUT emptab, p_deptno IN NUMBER) ISi NUMBER;CURSOR empselect IS SELECT empno, ename, job, sal, deptno FROM emp WHERE deptno = NVL(p_deptno,deptno) ORDER BY empno;

BEGINOPEN empselect;i := 1;LOOPFETCH empselect INTO block_data(i).empno, block_data(i).ename,block_data(i).job,

block_data(i).sal,block_data(i).deptno;EXIT WHEN empselect%notfound;i := i + 1;

END LOOP;END;

PROCEDURE empinsert(block_data IN emptab) ISi NUMBER;cnt NUMBER;

BEGINcnt := block_data.count;FOR i IN 1..cnt LOOP

INSERT INTO emp(empno, ename, job,sal,deptno) VALUES(block_data(i).empno,block_data(i).ename,block_data(i).job, block_data(i).sal,block_data(i).deptno);

END LOOP;END;

PROCEDURE empupdate(block_data IN emptab) ISi NUMBER;cnt NUMBER;BEGIN

cnt := block_data.count;FOR i IN 1..cnt LOOP

UPDATE emp SET ename = block_data(i).ename, job = block_data(i).job,sal = block_data(i).sal, deptno =

block_data(i).deptno WHERE empno = block_data(i).empno;END LOOP;

END;

PROCEDURE empdelete(block_data IN empnotab) ISi NUMBER;cnt NUMBER;BEGIN

cnt := block_data.count;FOR i IN 1..cnt LOOPDELETE FROM emp WHERE empno = block_data(i).empno;

END LOOP;END;

PROCEDURE emplock(block_data IN empnotab) ISi NUMBER;cnt NUMBER;block_rec emprec;BEGINcnt := block_data.count;FOR i IN 1..cnt LOOPSELECT empno, ename, job,sal,deptno INTO block_rec FROM emp WHERE empno = block_data(i).empno FOR

UPDATE OF ename NOWAIT;END LOOP;

END;

7/31/2019 Block by Procedure

3/3

END;

After creating the package, perform the following steps from within the Oracle Forms Builder:

1. Invoke the Data block Wizard.Choose 'Stored Procedure' as the type to create the data block.Press 'Next' button.

2. Enter the names of the procedures which correspond to:QUERY(emp_pkg.empquery)INSERT(emp_pkg.empinsert)UPDATE(emp_pkg.empupdate)DELETE(emp_pkg.empdelete)LOCK(emp_pkg.emplock)for the data block.

Next, create the layout.

Note: It is not required to include all of these procedures; thus, if you omit any of these procedures, that particularaction is not performed.

3. Run the form. You can Query, Insert, Delete, Update and Lock the records that resulted using stored procedures.

REFERENCES: See Forms Online Documentation.