Forms Developer : Basing a block on a database stored procedure

Benefits :

---

a. Reduce network traffic b. Using many tables c. Facilate migration to other solutions

How to perform this task :

---

1: Create a new table client

 CREATE TABLE CLIENT(  ID  NUMBER PRIMARY KEY,  NAME  VARCHAR2(40)); 

2: Create a new package spec

PACKAGE client_pkg IS

 Record declaration          TYPE client_rec IS RECORD(    id           client.id%TYPE,    name         client.name%TYPE); 

-- create the table of records

 TYPE clienttab IS TABLE OF client_rec INDEX BY BINARY_INTEGER; 

-- create the procedure on witch the block will be based on

 PROCEDURE client_query(client_data IN OUT clienttab); 

In this case, the transactional operations will be handled by explicit --procedures and triggeres

 PROCEDURE p_client_insert (r IN client_rec);  PROCEDURE p_client_lock        (s IN client.clid%TYPE);  PROCEDURE p_client_update      (t IN client_rec);  PROCEDURE p_client_delete      (t IN client_rec); 

-- You need to include the following function to use the -- Query -> count hits from the default menu of the

 FUNCTION count_query_ RETURN number; 

END client_pkg;

3. Create the package body

PACKAGE BODY client_pkg IS

 PROCEDURE client_query(client_data IN OUT bontab) IS    ii NUMBER;    CURSOR clientselect IS      SELECT id,name FROM client;  BEGIN    OPEN clientselect;    ii := 1;    LOOP      FETCH clientselect INTO        client_data( ii ).empno,        client_data( ii ).ename,        client_data( ii ).job,        client_data( ii ).sal,        client_data( ii ).comm;      EXIT WHEN clientselect%NOTFOUND;      ii := ii + 1;    END LOOP;  END client_query; 

 PROCEDURE client_insert(r IN client_rec) IS  BEGIN    INSERT INTO client VALUES(r.id, r.name);  END bonus_insert; 

 PROCEDURE client_lock(s IN client.id%TYPE) IS    v_rownum NUMBER;  BEGIN    SELECT id INTO v_rownum FROM client WHERE id=s FOR UPDATE OF name;  END bonus_lock; 

 PROCEDURE client_update(t IN client_rec) IS  BEGIN    UPDATE client SET name=t.name                   WHERE id=t.id;  END client_update; 

 PROCEDURE client_delete(t IN client_rec) IS  BEGIN    DELETE FROM client WHERE id=t.id;  END client_delete; 

 FUNCTION count_query_ RETURN NUMBER IS    r NUMBER;  BEGIN    SELECT COUNT(*) INTO r FROM client;    RETURN r;  END count_query_; 

END client_pkg;

4. Create the Forms module

Build a form and a block object using the Data Block Wizard with type of data block as "Table or View" based on the client table. Now, open the block property palete and base it on the stored procedure as follows:

In the block property ,

 -  Set the Query Data Source Type as Procedure.  -  Set the Query Data Source Name with the appropriate stored procedure :  "client_pkg.client_query".  -  Set the Query Data Source Arguments with the appropriate argument name for     that query : "client_data"   -  Set the Type to "Table"   -  Set a Name :  "client_pkg.clienttab"   -  Set Mode to "IN/OUT" as the data is flowing between the client and server     and viceversa.  -  Set Value (optional)  -  Set the DML target type as "Transactional triggers" 

5. Create Transactional Triggers Transactional triggers must be created a the block level as follows:

• On-insert trigger

 DECLARE    r client_pkg.client_rec;  BEGIN    r.id := :client.id;    r.name :=:client.name;    client_pkg.client_insert(r);  END; 

• On_lock trigger

 client_pkg.client_lock(:client.id); 

• On-update trigger

 DECLARE     t client_pkg.client_rec;  BEGIN    t.id :=:client.id;    t.name :=:client.name;    client_pkg.client_update(t);  END; 

• On-delete trigger

 DECLARE    t client_pkg.client_rec;  BEGIN    t.client :=:client.empno;    t.client :=:client.ename;     client_pkg.client_delete(t);  END; 

• On-count trigger (optional)

 DECLARE    recs NUMBER;  BEGIN    recs := client_pkg.client_query_;    SET_BLOCK_PROPERTY('client', query_hits,recs);  END; 

Good Luck