السلام عليكم ورحمة الله وبركاته

الرجاء المساعده بخصوص موضوع في report
اريد ان عمل عن طريق Ref Cursor Query
و هذه اول مره سوف استخدمها الرجاء المساعد مع الشكر...
اذا وجد امثله عليها او خطوات عملها اكون لكم شاكره
و جزاكم الله خير

See this Note captured from metalink:
---------------------------------------------
How to Dynamically Query a Table [iD 221651.1]

--------------------------------------------------------------------------------

Modified 03-APR-2008 Type BULLETIN Status PUBLISHED


"Checked for relevance on 03-Apr-2008"

PURPOSE
-------
In a two query (q1, q2) report, each row returned in q1 defines the table
to be used in the from clause of q2.

How to define this?


SCOPE & APPLICATION
-------------------
Developers

Dynamic querying of table
-----------------------------
I. Create the following example database tables and insert records.


1. CREATE TABLE dept_all ( 
 deptno NUMBER (2), 
 dname VARCHAR2 (20), 
 in_usa CHAR (1) DEFAULT 'Y') 

 / 
 INSERT INTO dept_all VALUES (10, 'DEPT 10', 'Y'); 
 INSERT INTO dept_all VALUES (20, 'DEPT 20', 'N'); 
 INSERT INTO dept_all VALUES (30, 'DEPT 30', 'Y'); 

2. CREATE TABLE emp_usa ( 
 empno NUMBER (4), 
 ename VARCHAR2 (20), 
 deptno NUMBER (2)) 
 / 
 INSERT INTO emp_usa VALUES (1001, 'EMP 1001', 10); 
 INSERT INTO emp_usa VALUES (1002, 'EMP 1002', 10); 
 INSERT INTO emp_usa VALUES (3001, 'EMP 3001', 30); 
 INSERT INTO emp_usa VALUES (3002, 'EMP 3002', 30); 

3. CREATE TABLE emp_non_usa ( 
 empno NUMBER (4), 
 ename VARCHAR2 (20), 
 deptno NUMBER (2)) 
 / 
 INSERT INTO emp_non_usa VALUES (2001, 'EMP 2001', 20); 
 INSERT INTO emp_non_usa VALUES (2002, 'EMP 2002', 20); 

II. Create Database package 

 Note, that Oracle Reports 6i needs 'static' ref cursor type for building Report Layout. 
 So, in the package specification one must have both ref cursor types, static for Report Layout and dynamic for ref cursor query. 


1. CREATE OR REPLACE PACKAGE example IS 
 TYPE t_dept_static_rc IS REF CURSOR RETURN dept_all%ROWTYPE; 
 TYPE t_dept_rc IS REF CURSOR; 
 FUNCTION dept_query (p_where VARCHAR2) RETURN t_dept_rc; 

 TYPE t_emp_rec IS RECORD ( 
 empno emp_usa.empno%TYPE, 
 ename emp_usa.ename%TYPE); 
 TYPE t_emp_static_rc IS REF CURSOR RETURN t_emp_rec; 
 TYPE t_emp_rc IS REF CURSOR; 
 FUNCTION emp_query ( 
 p_in_usa dept_all.in_usa%TYPE, 
 p_deptno dept_all.deptno%TYPE) 
 RETURN t_emp_rc; 

 END; 
 / 

2. CREATE OR REPLACE PACKAGE BODY example IS 
   
 FUNCTION dept_query (p_where VARCHAR2) RETURN t_dept_rc IS 
 l_dept_rc t_dept_rc; 
 BEGIN OPEN l_dept_rc FOR 
 'SELECT * FROM dept_all WHERE ' || NVL (p_where, '1 = 1') || ' ORDER BY deptno'; 

 RETURN l_dept_rc; 
 END; 

 FUNCTION emp_query ( 
 p_in_usa dept_all.in_usa%TYPE, 
 p_deptno dept_all.deptno%TYPE) 
 RETURN t_emp_rc 
 IS 
 l_emp_rc t_emp_rc; 
 l_table VARCHAR2 (30); 
 BEGIN IF p_in_usa = 'Y' THEN 
 l_table := 'emp_usa'; 
 ELSE 
 l_table := 'emp_non_usa'; 
 END IF; 

 OPEN l_emp_rc FOR 
 'SELECT * FROM ' || l_table || ' WHERE deptno = _deptno ORDER BY empno' 
 USING p_deptno; 

 RETURN l_emp_rc; 
 END; 

 END; 
 / 


III. In the Report's Data Model layout click on the 
    PL/SQL Ref Cursor query icon and create the following queries.

1. FUNCTION q_dept RETURN example.t_dept_static_rc IS 
 BEGIN -- "p_where" is a User Parameter 
 RETURN example.dept_query (_where); 
 END; 
 
 This will create
 QR_1 with DEPTNO, DNAME, IN_USA


2. FUNCTION q_emp RETURN example.t_emp_static_rc IS 
 BEGIN -- "in_usa" and "deptno" are columns from Parent Group (G_DEPT) 
 RETURN example.emp_query (:in_usa, :deptno); 
 END; 

 This will create  
 QR_2 with EMPNO, ENAME 

3. Create a link for 
  QR_1 to QR_2 


IV. Create a default Group Left or Group Above layout .
   Run the Report without entering any value for the P_where parameter.
   The output will be:


    Deptno Dname     In Usa  EmpnoEname
    10     DEPT 10    Y      1001 EMP 1001
                             1002 EMP 1002
    20     DEPT 20    N      2001 EMP 2001 -> Notice
                             2002 EMP 2002
    30     DEPT 30    Y      3001 EMP 3001
                             3002 EMP 3002

Notice that the data for Deptno 20 is taken from the table emp_non_usa