هلا اخواني
هذا كود رائع من موقع ميتالينك يشرح بامثله بسيطه كيفية بناء داتا بيز بلوك بناء علي Procedure
وهوا من المواضيع المهمه جدا
اترككم مع الشرح و في حالة وجود اي استفسارات في انتظاركم

Subject:  Basing a Block on a Stored Procedure - Sample Code 
 Doc ID:  Note:66887.1 Type:  BULLETIN 
 Last Revision Date:  27-JUL-2004 Status:  PUBLISHED 


PURPOSE
=======

This bulletin gives sample coding on using a stored procedure with Forms. 


SCOPE & APPLICATION
===================

This bulletin is intended for the user who has a good knowledge on using 
database packages, procedures and functions.  The user should also have a good 
knowledge on using the Forms Debugger.

Tested with Forms version 5.0.x with Oracle 7.3.4, however the concepts in this
document apply to Forms versions 6.0.X and and the 8.X versions of the database. 
These techniques will not work for forms versions prior to 5.0.X.

                     BLOCK BASED ON A STORED PROCEDURE
                     =================================

Introduction: 
------------

Why base a block on a stored procedure?
---------------------------------------

Basing a block on a stored procedure is an advanced operation to do the 
following:

1. Reduce network traffic through array processing as the sql statements are 
  processed by the pl/sql engine on the server side.
2. Perform complex computations 
3. Update and query multiple tables
4. Perform validation and DML on server-side.


What is a REF Cursor?
--------------------

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings.  
This is an added feature that comes with PL/SQL v2.2.  A REF cursor allows a 
cursor to be opened on the server and passed to the client as a unit rather 
than one row at a time.  One can use a Ref cursor as a target of assignments 
and can be passed as parameters to the Program Units.  Ref cursors are opened 
with an OPEN FOR statement and in all other ways, they are the same as regular 
cursors.


What is a table of records?
--------------------------

A table of records is a new feature added in PL/SQL v2.3.  It is the equivalent 
of a database table in memory.  If you structure the PL/SQL table of records 
with a primary key (an index) you can have array-like access to the rows.  
Table of records differ from arrays in that they are not bound by a fixed lower 
or higher limit.  Nor do they require consecutive index numbers as arrays do. 
Consult a PL/SQL reference manual (version 2.3 or higher) for further 
explanation.  There are three steps involved in creating a table of records.  
The are:

1. Declare a record type that the table is going to contain.
2. Declare a new type for the table of record.
3. Finally, declare a  variable using the new type.


Why base a block on a PL/SQL Table versus a Ref Cursor?
------------------------------------------------------

A table of records fetches all the rows from the table.
A reference cursor fetches only those rows that matches your query criteria.
If you are planning to filter the rows with a where clause or your query
returns only few records out of many, you can choose the ref cursor rather than
table of records.  Note that the block properties for number of records set and
buffered affect blocks based on stored procedures.


CODE EXAMPLES
==============

This note explains how to use Table of Records or Ref Cursors as the data query
source and for DML operations using transactional triggers like On-insert, 
On-update and On-lock triggers.  This note provides two examples of basing a 
block on a stored procedure.  The first example will provide sample code for 
single block operations.  The second example will follow with code for 
performing multi-block operations with a master-detail relationship. 


EXAMPLE A.  Single Block Operations.

Use a table with a Primay key. Avoid using Rowid with any select statement.
(Reason explained later).  Follow through and complete each of the 5 steps
below.


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;



Step 4.  Create the Form Block                            
------------------------------

Build a block using the Data Block Wizard with type of data block as "Table or
View" based on the Bonus table.   Now, open the block property sheet to base 
the block on the stored procedures as follows:

In the block property sheet,
 *  Set the Query Data Source Type as Procedure.
 *  Set the Query Data Source Name with the appropriate stored procedure.
    In this example, for refcur you will enter "bonus_pkg.bonus_refcur" and
    for table of records you will enter "bonus_pkg.bonus_query".
 Note: One can use either Ref Cursor or Table of Records to perform this 
       query operation.  For this example, either use the procedure 
       bonus_pkg.bonus_query or bonus_pkg.bonus_refcur.
       You do not need to set anything in the "Query Data Source Columns"
       property, as Forms has already done this because you began by creating
       the block with data block as a Table.

 *  Set the Query Data Source Arguments with the appropriate argument name for
    that query. 
    In this example, "bonus_data" is the argument name for both refcur and 
    table of records.

 *  Set the Type to "Table" for table of records or "Refcursor" for referenced 
    cursor. 
          
 *  Set a Name.
    In this example, we can use "bonus_pkg.bontab" for table of records
    (packagename.table_name) or you can use either "bonus_pkg.b_cursor" 
    or "bonus_data.b_cursor" for a ref cursor.

 *  Set Mode to "IN/OUT" as the data is flowing between the client and server
    and viceversa.

 *  Set Value (optional)

If you skip to set typename, you will hit an error. The possible compilation 
error will be PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms automatically
creates a trigger like Query-Procedure to populate the values that are sent
from the database through the stored procedure.  

 *  Set the DML target type as "Transactional triggers" under the Advanced 
    Database section.  This step is important.  You must specify
    "transactional triggers" to avoid getting an error:
      FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.
    at runtime.
    Leave all other properties under the Advanced Database section blank.
    Note: You must use transactional triggers to perform all DML processing 
          as your block is based on stored procedures and not a table or view.
          If you do not provide these triggers (see code in Step 5) you will 
          receive runtime error:  Frm-40401 No Changes To Save when after 
          performing DML operations like insert, delete or update.

One more general example of setting the Query Data Source Arguments in the
block property palette could be, 

ARGUMENTNAME      TYPE            TYPENAME          MODE       VALUE
-------------     ----            --------          ----       -----
bonus_data     REFCURSOR      bonus_pkg.b_cursor    IN OUT    (leave blank)
                             Or bonus_data.b_cursor
                        OR
bonus_data     TABLE          bonus_pkg.bontab      IN OUT    (leave blank)


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

* On-insert trigger 

 DECLARE
   r bonus_pkg.bonus_rec;
 BEGIN
   r.empno := :bonus.empno;
   r.ename :=:bonus.ename;
   r.job := :bonus.job;
   r.sal := :bonus.sal;
   r.comm := :bonus.comm;

   bonus_pkg.bonus_insert(r);
 END;


* On_lock trigger

 bonus_pkg.bonus_lock(:bonus.empno);


* On-update trigger

 DECLARE 
   t bonus_pkg.bonus_rec;
 BEGIN
   t.empno :=:bonus.empno;
   t.ename :=:bonus.ename;
   t.job := :bonus.job;
   t.sal := :bonus.sal;
   t.comm := :bonus.comm;

   bonus_pkg.bonus_update(t);
 END;


* On-delete trigger

 DECLARE
   t bonus_pkg.bonus_rec;
 BEGIN
   t.empno :=:bonus.empno;
   t.ename :=:bonus.ename;
   t.job := :bonus.job;
   t.sal := :bonus.sal;
   t.comm := :bonus.comm;

   bonus_pkg.bonus_delete(t);
 END;


* On-count trigger  (optional)
 Note.  Because you have based your block on a stored procedure, Form's
        default processing will not return the number of query hits.  This 
        trigger takes the place of the default processing and will return 
        the number of query hits.

 DECLARE
   recs NUMBER;
 BEGIN
   recs := bonus_pkg.count_query_;
   SET_BLOCK_PROPERTY('bonus', query_hits,recs);
 END;


You now have completed the process for basing a block on a stored procedure for
single block operations.


=============================================================================

EXAMPLE B.  Multi-block opererations with master-detail relationship

Basing a block on a stored procedure (Multi block operation, having master
detail relationship).

Suppose you are passing a value from master block to the detail block to
perform query operation, perform the following steps:


Step1:  verification
--------------------
 BEFORE ATTEMPTING TO DO THIS, VERIFY THE PRESENCE OF INTEGRITY CONSTRAINTS
 FOR THE TABLES INVOLVED IN THIS OPERATION.
 Verify if the tables have Primary key and Foreign Key relationship.
 Your Physical Database design is very important. Otherwise, you will be
 getting errors like 
   ORA-6502  Pl/sql: Numeric value error or 
   ORA-4098  Trigger 'X' is invalid and failed revalidation. 
   ORA-4068  If any column name is not properly defined in the select
             statement of the stored procedure.
 


Step2:  Create a package spec at the database level
---------------------------------------------------

PACKAGE emp_pkg IS
 TYPE emprec is RECORD(
   empno    emp.empno%type,
   ename    emp.ename%type,
   job      emp.job%type,
   mgr      emp.mgr%type,
   hiredate emp.hiredate%type,
   sal      emp.sal%type,
   comm     emp.comm%type,
   deptno   emp.deptno%type);
 TYPE empcur IS REF CURSOR RETURN emprec;
 TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;

-- This procedure uses refcursor for query operation, it sends the data
-- from the stored procedure to the client.
 PROCEDURE empquery_refcur(emp_data IN OUT empcur,
                           v_dno    IN     NUMBER);

-- This procedure uses table of records for query operation.
-- One can use either ref cursor or table of records for query operation.
 PROCEDURE empquery(emp_data IN OUT emptab,
                    v_dno    IN     NUMBER);

-- This procedures inserts the data passed as a record from the emp block
-- to the emp table.
 PROCEDURE emp_insert(r IN emprec);

-- Empno is getting passed from emp block to the stored procedure, this
-- procedure is to lock that specific row that has that empno.
 PROCEDURE emp_lock(s IN emp.empno%TYPE);

 PROCEDURE emp_update(t IN emprec);

 PROCEDURE emp_delete(t IN emprec);

-- 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 emp_pkg;


Step 3.  Create the package body
--------------------------------

PACKAGE BODY emp_pkg IS

 PROCEDURE empquery(emp_data IN OUT emptab,
                    v_dno    IN     NUMBER) IS
   ii NUMBER;
   CURSOR empselect IS 
     SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
       FROM emp 
      WHERE deptno = nvl(v_dno, deptno);
 BEGIN
   OPEN empselect;
   ii := 1;
   LOOP
     FETCH empselect INTO
       emp_data(ii).empno,
       emp_data(ii).ename,
       emp_data(ii).job,
       emp_data(ii).mgr,
       emp_data(ii).hiredate,
       emp_data(ii).sal,
       emp_data(ii).comm,
       emp_data(ii).deptno;
       EXIT WHEN empselect%NOTFOUND;
         ii := ii + 1;
    END LOOP;
  END empquery;

 
 PROCEDURE empquery_refcur(emp_data IN OUT empcur,
                           v_dno    IN     NUMBER) AS
 BEGIN
   OPEN emp_data FOR
     SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
       FROM emp
      WHERE deptno = nvl(v_dno, deptno);
 END empquery_refcur;                                                                                                         
                                                                                                                              
                                                                                                                              
                                                                                                                              
                                                                                                                              
                                                                                                                              
                                                                                                                                                                                                                                            !
                                    
 PROCEDURE emp_insert(r IN emprec) IS
 BEGIN
   INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
   VALUES (r.empno, r.ename, r.job, r.mgr, r.hiredate, r.sal,
           r.comm, r.deptno);
   COMMIT;
 END emp_insert;

 PROCEDURE emp_lock(s IN emp.empno%TYPE) IS
   v_rownum NUMBER;
 BEGIN
   SELECT empno
     INTO v_rownum
     FROM emp
    WHERE empno = s
      FOR UPDATE OF ename;
 END emp_lock;

 PROCEDURE emp_update(t IN emprec) IS
 BEGIN
   UPDATE emp
      SET ename = t.ename,
          job = t.job,
          mgr = t.mgr,
          hiredate = t.hiredate,
          sal = t.sal,
          comm = t.comm,
          deptno = t.deptno
    WHERE empno = t.empno;
 END emp_update;

 PROCEDURE emp_delete(t IN emprec) IS
 BEGIN
   DELETE
     FROM emp
    WHERE empno = t.empno;
 END emp_delete;

 FUNCTION count_query_ RETURN NUMBER IS
   r NUMBER;
 BEGIN
   SELECT count(*)
     INTO r
     FROM emp;
   RETURN r;
 END count_query_;

END emp_pkg;


Step 4.  Create the Form Block                            
------------------------------

Build the block with datablock type as "table or view" for both the master
(dept) and detail block (emp). Later the block property sheet will be adjusted
for the detail block to be based on stored procedure.
The blocks can be built manually also, but, using wizard is preferrable. Here,
you have the choice of having tab canvas.

Create the relationship between these blocks either explicitly or at the time
of creating the block using wizard, refer the later part of this note for more
information on this.

In the detail block propertysheet, (emp block)
 * set the Query Data Source Type as Procedure.
 * set the Query Data Source Name with the appropriate name of the stored
   procedure.
   In this case, One can use either the Ref Cursor or Table of Records to
   perform this query operation. So, in this example, either use the procedure
     emp_pkg.empquery
   or  
     emp_pkg.empquery_refcur

Because you began creating the block with data block as Table or view, you do
not need to set anything in the Query Data Source Columns as the Forms does
that job.

 * set the Query Data Source Arguments with the appropriate argument name for
   that query.
   In this case, emp_data is the argument name for both ref cursor and table
   of records
 * set the Type to table or refcursor, depending on the procedure you have
   chosen. This example uses the "Table".
 * set the Type Name to the appropriate type, this will be emp_pkg.emptab
   If you choose the ref cursor, it would have been emp_pkg.empcur or
   emp_data.refcur
 * set Mode to "IN/OUT" as the data is flowing between the client and server
   and viceversa.
 * set Value (optional)

 * repeat above steps for the other argument of the procedure, deptno.
   The appropriate values can be found in the table below

Yet another general example could be,

ARGUMENTNAME   TYPE            TYPENAME            MODE       VALUE
------------   ----            --------            ----       -----
emp_data       REFCURSOR       emp_pkg.empcur      IN OUT     (leave it blank)
                           OR
emp_data       TABLE           emp_pkg.emptab      IN OUT 
                          
                           AND

v_dno          NUMBER          dept.deptno%type    IN         :dept.deptno

If you skip to set typename, you will hit an error. The possible compilation 
error will be:
 PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms automatically
creates a trigger like Query-Procedure to populate the values that are sent
from the database through the stored procedure.  

 * Set the DML target type as "Transactional triggers" under the Advanced 
   Database section. You must specify "transactional triggers" to avoid
   getting error:
     FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.
   at runtime. Leave all other properties under the Advanced Database section
   blank.
   Note: You must use transactional triggers to perform all DML processing as
         your block is based on stored procedures and not a table or view.
         If you do not provide these triggers (see code in Step 5) you will 
         receive runtime error:
           Frm-40401 No Changes To Save
         after performing DML operations like insert, delete or update.


Step 5.  Create Transactional Triggers
--------------------------------------
Then, from the forms at detail block level (emp), you have to create the
following triggers:

* On-insert trigger

 DECLARE
   r emp_pkg.emprec;
 BEGIN
   r.empno := :emp.empno;
   r.ename :=:emp.ename;
   r.job := :emp.job;
   r.mgr := :emp.mgr;
   r.hiredate := :emp.hiredate;
   r.sal := :emp.sal;
   r.comm := :emp.comm;
   r.deptno := :emp.deptno;
   
   emp_pkg.emp_insert(r);
 END;


* On_lock trigger

 emp_pkg.emp_lock(:emp.empno);


* On-update trigger

 DECLARE
   t emp_pkg.emprec;
 BEGIN
   t.empno := :emp.empno;
   t.ename := :emp.ename;
   t.job := :emp.job;
   t.mgr := :emp.mgr;
   t.hiredate := :emp.hiredate;
   t.sal := :emp.sal;
   t.comm := :emp.comm;
   t.deptno := :emp.deptno;

   emp_pkg.emp_update(t);
 END;


* On-delete trigger

 DECLARE
   t emp_pkg.emprec;
 BEGIN
   t.empno := :emp.empno;
   t.ename := :emp.ename;
   t.job := :emp.job;
   t.mgr := :emp.mgr;
   t.hiredate := :emp.hiredate;
   t.sal := :emp.sal;
   t.comm := :emp.comm;
   t.deptno := :emp.deptno;

   emp_pkg.emp_delete(t);
 END;


* On-count trigger

 DECLARE
   recs NUMBER;
 BEGIN
   recs := emp_pkg.count_query_;
   set_block_property('emp', query_hits, recs);
 END;

-- This On-Count trigger is needed. The forms default
-- processing will not return the query hits as you have 
-- based the block on a stored procedure.


Step 6.  Change the delete record behavoiur
-------------------------------------------

Make sure the "delete record behaviour" property of the relation is set to
isolated (non-isolated is the default in Developer 6).


You now have completed the process for basing a block on a stored procedure for
Master-Detail operations.


===============================================================================
Questions:

1. What will happen if you change the "delete record behaviour" from isolated
  to non-isolated or cascading?

  If you change the "Delete record behaviour" from isolated to non-isolated,
  the On-Check-Delete-Master trigger will be created by forms in the master
  block. This will not understand the stored procedure you have used as a
  query data source for the detail block. As a result, you will get a
  Compilation error in the On-Check-Delete-Master trigger that will be like:
    Pls-201 procedure name must be declared.

  Similarly, if you change the "Delete record behaviour" from isolated to
  cascading, the forms generates the Pre-Delete trigger in the master block
  which will give a compilation error as well, as the master block will not
  understand the procedure for the query data source on which the detail block
  is based on. 
  It will be costly to have integrity constraint at form level also as the
  pre-delete trigger repeats the same job as the constraints declared at
  database side.
  So, do not try to change the "Delete Record Behaviour".

  If you have the proper integrity constraints added to your tables, it will
  be automatically taken care of at the time of committing the record.
  For example, when you delete a master record while child records are there,
  at the time of saving this change, the form will provide an error message:
    Frm-40510: Oracle error: Unable to delete record.

  If you do not have a foreign key constriant at all, and attempt to set the
  "delete behaviour" to cascading, it will give you:
    Frm-30409 Delete record behaviour is invalid.

2. Why use Primary key and not rowid ?

  The On_Lock trigger replaces the default forms locking, as a side effect
  prevents Forms from obtaining the ROWID for the row. In consequence you must
  define a Primary Key for the block and use this PK to be passed as a
  parameter to the stored procedure.
  And also, your block must have a PK, otherwise, you will hit:
    Frm-30100 Block must have atleast one Primary Key item.


3. How to set the query criteria for detail block passing a value from the
  master block?

  If you have chosen the tab canvas as the canvas type and if you want to set
  the query criteria in the detail block the same as master block, you can
  create a Key-Exeqry trigger at block level for the master block and call
  execute_query from there;
  Also, you can create a When-Tab-Page-Changed trigger at form level to pass
  the query criteria from the master block to the detail and type the
  following:

    DECLARE
      page_name varchar2(10);
      tab_id tab_page;
      tab_id2 tab_page;
    BEGIN
      page_name := get_canvas_property('CANVAS8', TOPMOST_TAB_PAGE);
      IF page_name = 'PAGE11' THEN
        go_item('dept.deptno');
      ELSE
        go_item('empno');
        execute_query;
      END IF;
    END;

  You can set the "Copy value from item" of the item's property sheet in the
  detail block of the item that has relationship with the master.
  Here, in this example you can set the "copy value from item" property of the
  deptno in the emp block to "dept.deptno".

  Reference:
  Note 1078147.6 How to Filter Rows on Block Based on Stored Procedure

4. What to remember if you build the block manually?

  In case of Master-Detail blocks, if you are passing a column to the stored
  procedure that inturn returns data to the detail block and if the detail
  block also has the same column present, you have to set the Column Name
  Property of that specific text item to null or blank. Otherwise, you will be
  getting:
    Frm-40350 Query Caused no records to be retrieved.
  
  In this case, if you build the block manually for the detail block, the
  Column Name Property of the deptno in the dept block must be blank as it is
  taken care of by the stored procedure that uses a table of records or a ref
  cursor.


5. Getting the error "Wrong no. of arguments to populate_block in
  query_procedure". What should I do?

  If you attempt to change the block properties manually to use refcursor from
  table of records, the Query data source name property and Query data source
  arguments should be set properly. If you feel you set it right and are still
  getting this compilation error, drop that procedure and let the forms
  builder generate a new one for you.


6. My query is performing extremely slow?

  If your query matches or retrieves only small amount of records or if you
  are using where clause to filter the query results, then use a refcursor
  rather than using a table of records. As a ref cursor returns only the
  records that match the query condition. But, a table of records tries to
  fetch all the records. So, performance will be slower with table of records.


Tips
----

 Provide an exceptional handler for all the possible ora errors like
 ORA-4098 and ORA-4068.
 If you get:
   ORA-6502 Pl/sql: Numeric or Value error
 your table definition of column lengths do not go with the datatype or length
 of the text items in the forms.
 Use the debugger to see what values are getting passed from client to server
 and viceversa.


Known Problems
--------------

The block which is based on the ref cursor should have all the items which are
there in the ref cursor type. If the number of items does not match the 
individual items in the ref cursor type then execute query gives the error
Frm-40350 Query caused no records to be retrieved. 
This is because the ref cursor needs to know which columns in the ref cursor 
type map to which items in the form's block.

وهذا كود اخر لشرح كيفية البحث بناء علي شرط معين

Subject:  How to Filter Rows on Block Based on Stored Procedure in Oracle Forms? 
 Doc ID:  Note:1078147.6 Type:  PROBLEM 
 Last Revision Date:  22-JUN-2004 Status:  PUBLISHED 


Problem Description: 
==================== 

Whilst in ENTER QUERY mode you would like to specify a search criteria to 
restrict the results of your query.  The block on which this query resides
is based on a stored procedure. 

Problem Explanation: 
==================== 

If you attempt setting the where clause in the block it is totally ignored. 
This happens with both the 'Ref Cursor' or 'Table of Records' variable.    


Problem References: 
=================== 

[search Words: filter ]

Solution Summary: 
=================

With Forms 5.0 and above, you can use the Tools--> Datablock Wizard menu 
in the Object Navigator to enter the datablock wizard in a re-enterant mode.
You can then specify the query criteria there. 

Alternatively, you can pass the query criteria from Forms to the stored 
procedure as an IN argument. You can then use the parameter with the 
select statement (in the procedure you have created using the 'Table of Records
or 'Ref Cursor').


Solution Description: 
===================== 

If the blocks were created using the Datablock Wizard with the
"stored procedure" radio button, you can always re-enter the datablock wizard
for that block by clicking on the Tools-->Datablock Wizard. If you go to the 
Query tab on the bottom of the tab page you will notice that it requests the 
name of the parameter. This is where you enter -

  :parameter.<parameter_name> 

This method relies on having a parameter in the form that passes the 
value, for example p_deptno, into the stored procedure. This is done in order
to get the corresponding records from the emp table for all employees who work
in that deptartment.

Additionally create a PRE-QUERY trigger and assign the value of the deptno you 
are passing through this parameter using the following:

  (:parameter.<parameter_name>:= :dept.deptno).

If the blocks were created manually, however, YOU CANNOT use the re-enterant 
option to go to the Datablock Wizard. For these type of blocks, use the method
suggested below.
 

METHOD FOR MANUALLY CREATED BLOCKS:
===================================

Re-enter the Datablock Wizard by using the Tools--> Datablock Wizard menu of the 
Object Navigator.  You will notice that you will not be able to see any 
procedure in the Datablock Wizard in re-enterant mode.

To use this method, you will need to include one more parameter to your query
procedure as an IN parameter. For example, if you are passing deptno as query
criteria, click on the "Query Data Source Arguments" of your block property 
sheet and include the following:

   ARGUMENT NAME: set to  P_DEPTNO or (any variable name)
   TYPE: set to the appropriate datatype, for example, NUMBER
   TYPENAME: set to dept.deptno%type, for example
   MODE: set to IN
   VALUE: set to :dept.deptno(the :blockname.itemname you are passing
                              as query criteria.).

In the query procedure, include this parameter as an IN argument, and as the 
2nd parameter.  For example:

   Procedure  empquery_refcur(emp_data IN OUT empcur, v_dno IN number) AS ....

Finally, include this paramter in your select statement of either 'Table of 
Records' or 'Ref Cursor' as
 
   select empno, ename
    from emp
     where deptno=nvl(v_dno, deptno);

وهذا شرح لحل مشكلة Dml Returning Value

Subject:  Dml Returning Value Does Not Work For Blocks Based On Stored Procedures 
 Doc ID:  Note:275911.1 Type:  PROBLEM 
 Last Revision Date:  30-JUL-2004 Status:  PUBLISHED 


The information in this article applies to: 
Oracle Forms - Version: 9.0.4.0
This problem can occur on any platform.

Symptoms
Dml Returning Value Doesn't Work For Blocks Based On Stored Procedures 
Cause
This feature not implemented for blocks based on stored procedures

Fix
1) Putting a re-query in code after the commit if any rows where inserted
2) Having an alternative Function call for inserting records sourced from 
Forms, that does the same (and reuses all the same code) as the standard 
function but which relies on the Sequence number being generated in the Form 
rather than in the Stored Function. 
3) Use Transactional Triggers to handle the block, then your Insert Call could 
actually have an OUT variable that populates the Sequence number field in the 
form. Your own DML returning values if you like.

مع تمنياتي بالتوفيق الدائم

مشكور اخي الكريم
ودائما مميز بمشاركاتك

u r always the best
thank u so much

و الله با باشمهندس عبد الله .. الواحد مش عارف يقولك اية بس على اية ولا اية .. كل الى نقدر نقولهولك .. جزاك الله عنا خيرا

Oracle ITself

جزاك الله كل خير وجعله فى ميزان حسناتك

والله موضوع ممتاز
الله يجزيك الخير
ونتمنى منك مواضيع ممتازة أخرى ومتقدمة (Advanced)

والله موضوع ممتاز
الله يجزيك الخير
ونتمنى منك مواضيع ممتازة أخرى ومتقدمة (Advanced)

68299[/snapback]

بارك الله فيك

جزاك الله خيرا وبارك لك وفيك وعليك يا black and white

من فرج كربة عن أخية المسلم فرج الله علية يو القيامة

شكراااااااا كتيييييييييير

تشكر
وتحية لك من السيباتي

شكرا علي هذا المجهود الرائع و لكن أتمني أن يكون الشرح بالغة العربية لسهولة و سرعة الفهم و لكم مني جزيل الشكر

جمال صلاح الدين

عمل ممتاز

ما شاء الله عليك

مشكور اخوي الكريم

فتح الله عليك ونفع بك

[جزاك الله كل خير

بداية : جزاكم الله خيرا


بعد اذنكم سؤال صغير : كيف يمكنني استقبال القيمة العائدة من lOV في متغير ، انا لا أريد تحديد عنصر في الفورم يتم فيه ارجاع القيمة فيه أثناء (design time)فقط اريد استقبال القيمة التي يختارها المستخدم في متغير أثناء (run time) ؟
وشكرا

مشكور أخوي على الشرح الرائع

يعطيك الف عافية

مشكور اخي العزيز وإن شاء الله تكون في ميزان حساناتك

بارك الله فيك اخ عبد الله على هذا الكود الرائع

جزاكم الله خيرا وجعله في ميزان حسناتك

الله يعطبك الف عافية

مشاركه رائعه حقا..... اكثر الله من امثالك وجعله فى ميزان حسناتك