Home » SQL & PL/SQL » SQL & PL/SQL » Passing Dynamic Collection to SubRoutines (RDBMS 9iR2 on Windows NT 2003 Server)
Passing Dynamic Collection to SubRoutines [message #317107] Tue, 29 April 2008 09:44 Go to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Good Day -

I am not sure if this is a newbie question or not, but I figured this was a good place to start. I am facing the following scenario and was wondering what the best solution would be to my problem. I have a package that houses a MAIN procedure that will call various sub-routines (procedures) within the same package. I am utilizing a record type variable definition as a global package variable and an indexed table variable that references the record type, also as a global package variable (both defined within the body, not the package spec). What I need to do is execute a dynamic fetch before passing the value-filled PL/SQL table to each sub-routine. What I had been doing is coding within each sub a dynamic OPEN <variable> FOR 'SELECT....' using a sys_refcursor variable type. This is becoming cumbersome to maintain as the package grows and would like to house all dynamic references in the MAIN procedure. Here is an example of my current setup:

PROCEDURE populate_initial_reqs (errbuf         OUT VARCHAR2,
                                 retcode        OUT NUMBER,
                                 p_fiscal_yr    IN  VARCHAR2
                                )
AS
/****************************************************************************************************
						POPULATE_INITIAL_REQS Local Procedure Variables/Cursors 																			 
****************************************************************************************************/

    TYPE r_linked_req_rec IS RECORD (REQ_PREPARER_ID            PO.po_requisition_headers_all.preparer_id%TYPE,
                                     REQ_HEADER_ID              PO.po_requisition_headers_all.requisition_header_id%TYPE,
                                     REQ_NUMBER                 PO.po_requisition_headers_all.segment1%TYPE,
                                     REQ_HEAD_ATT1              PO.po_requisition_headers_all.attribute1%TYPE,
                                     REQ_HEAD_ATT2              PO.po_requisition_headers_all.attribute2%TYPE,
                                     REQ_HEAD_ATT3              PO.po_requisition_headers_all.attribute3%TYPE,
                                     REQ_HEAD_ATT4              PO.po_requisition_headers_all.attribute4%TYPE,
                                     REQ_HEAD_ATT5              PO.po_requisition_headers_all.attribute5%TYPE,
                                     REQ_HEAD_ATT6              PO.po_requisition_headers_all.attribute6%TYPE,
                                     REQ_HEAD_ATT7              PO.po_requisition_headers_all.attribute7%TYPE,
                                     REQ_LINE_ID                PO.po_requisition_lines_all.requisition_line_id%TYPE,
                                     REQ_LINE_NUM               PO.po_requisition_lines_all.line_num%TYPE,
                                     REQ_LINE_ATT1              PO.po_requisition_lines_all.attribute1%TYPE,
                                     REQ_DIST_ID                PO.po_req_distributions_all.distribution_id%TYPE,
                                     REQ_DIST_NUM               PO.po_req_distributions_all.distribution_num%TYPE
                                    );
                                
    TYPE c_linked_req_rec IS TABLE OF r_linked_req_rec INDEX BY BINARY_INTEGER;
    l_req_record          	   		  c_linked_req_rec;
    l_linked_req_cur      			  sys_refcursor; 
    
/****************************************************************************************************
				       END POPULATE_INITIAL_REQS Local Procedure Variables/Cursors 																			 
****************************************************************************************************/

BEGIN

    OPEN l_req_record FOR
    'SELECT'||
    ' h.preparer_id                PREPARER_ID,'||
    ' h.requisition_header_id      REQ_HEADER_ID,'||
    ' h.segment1                   REQ_NUMBER,'||
    ' h.attribute1                 REQ_HEAD_ATT1,'||
    ' h.attribute2                 REQ_HEAD_ATT2,'||
    ' h.attribute3                 REQ_HEAD_ATT3,'||
    ' h.attribute4                 REQ_HEAD_ATT4,'||
    ' h.attribute5                 REQ_HEAD_ATT5,'||
    ' h.attribute6                 REQ_HEAD_ATT6,'||
    ' h.attribute7                 REQ_HEAD_ATT7,'||
    ' l.requisition_line_id        REQ_LINE_ID,'||
    ' l.line_num                   REQ_LINE_NUM,'||
    ' l.attribute1                 REQ_LINE_ATT1,'||
    ' d.distribution_id            REQ_DIST_ID,'||
    ' d.distribution_num           REQ_DIST_NUM '||
    'FROM'||
    ' PO.po_requisition_headers_all          h,'||
    ' PO.po_requisition_lines_all            l,'||
    ' PO.po_req_distributions_all            d,'||
    ' XXRI.XXRI_PO_FY'||p_fiscal_yr||'_DEBUG p' ||
    'WHERE'||
    ' h.requisition_header_id = l.requisition_header_id'||
    ' AND l.requisition_line_id = d.requisition_line_id'||
    ' AND p.from_po_req_dist_id = d.distribution_id '||
    'ORDER BY'||
    ' REQ_NUMBER,'||
    ' REQ_LINE_NUM,'||
    ' REQ_DIST_NUM'
    ;
                     
    FETCH l_linked_req_cur BULK COLLECT
    INTO  l_req_record;
    CLOSE l_linked_req_cur;


My first question is, is this the appropriate approach given my DB version? Is there a method, other than using SYS_REFCURSOR to do this, such as VARRAY maybe?

My second question is, if I utilize this same logic within MAIN (including the bulk fetch), what variable would I need to pass to my subroutines so that I can make a reference in the sub-routine such as:
FOR variable.FIRST .. variable.LAST or variable (n).REQ_HEADER_ID
Re: Passing Dynamic Collection to SubRoutines [message #317126 is a reply to message #317107] Tue, 29 April 2008 11:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you are going to pass a collection from a main procedure to a sub procedure, then the type that the collection is based on needs to be declared in the package, not the package body. So, if you have something like this:

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    PROCEDURE main
  4  	 (p_tab IN VARCHAR2);
  5    --
  6    PROCEDURE sub1
  7  	 (p_tab IN VARCHAR2);
  8  END your_pkg;
  9  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    TYPE r_your_rec IS RECORD
  4  	 (deptno	  dept.deptno%TYPE,
  5  	  dname 	  dept.dname%TYPE,
  6  	  loc		  dept.loc%TYPE);
  7    TYPE c_your_tab IS TABLE OF r_your_rec INDEX BY BINARY_INTEGER;
  8    --
  9    PROCEDURE main
 10  	 (p_tab IN VARCHAR2)
 11    IS
 12    BEGIN
 13  	 sub1 (p_tab);
 14    END main;
 15    --
 16    PROCEDURE sub1
 17  	 (p_tab IN VARCHAR2)
 18    IS
 19    v_your_tab	  c_your_tab;
 20    v_ref		  SYS_REFCURSOR;
 21    BEGIN
 22  	 OPEN v_ref FOR 'SELECT * FROM ' || p_tab;
 23  	 FETCH v_ref BULK COLLECT INTO v_your_tab;
 24  	 CLOSE v_ref;
 25  	 FOR i in 1 .. v_your_tab.COUNT LOOP
 26  	   DBMS_OUTPUT.PUT_LINE (v_your_tab(i).deptno || ',' || v_your_tab(i).dname || ',' || v_your_tab(i).loc);
 27  	 END LOOP;
 28    END sub1;
 29  END your_pkg;
 30  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.main ('dept')
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>


Then you can change it something like this:

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    TYPE r_your_rec IS RECORD
  4  	 (deptno	  dept.deptno%TYPE,
  5  	  dname 	  dept.dname%TYPE,
  6  	  loc		  dept.loc%TYPE);
  7    TYPE c_your_tab IS TABLE OF r_your_rec INDEX BY BINARY_INTEGER;
  8    --
  9    PROCEDURE main
 10  	 (p_tab IN VARCHAR2);
 11    --
 12    PROCEDURE sub1
 13  	 (p_your_tab IN c_your_tab);
 14  END your_pkg;
 15  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    PROCEDURE main
  4  	 (p_tab IN VARCHAR2)
  5    IS
  6  	 v_your_tab	    c_your_tab;
  7  	 v_ref		    SYS_REFCURSOR;
  8    BEGIN
  9  	 OPEN v_ref FOR 'SELECT * FROM ' || p_tab;
 10  	 FETCH v_ref BULK COLLECT INTO v_your_tab;
 11  	 CLOSE v_ref;
 12  	 sub1 (v_your_tab);
 13    END main;
 14    --
 15    PROCEDURE sub1
 16  	 (p_your_tab IN c_your_tab)
 17    IS
 18    BEGIN
 19  	 FOR i in 1 .. p_your_tab.COUNT LOOP
 20  	   DBMS_OUTPUT.PUT_LINE (p_your_tab(i).deptno || ',' || p_your_tab(i).dname || ',' || p_your_tab(i).loc);
 21  	 END LOOP;
 22    END sub1;
 23  END your_pkg;
 24  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.main ('dept')
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


Re: Passing Dynamic Collection to SubRoutines [message #317129 is a reply to message #317107] Tue, 29 April 2008 11:54 Go to previous messageGo to next message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Thank you very much for the clarification Barbara. I appreciate you taking the time. As a follow up, if I define the record type and the table type as globals in the package body, shouldn't all the procedures be able to see them? If so, then shouldn't I be able to do something like this:

PROCEDURE main (p_fiscal_yr         IN   VARCHAR2)
AS
/****************************************************************************************************
			     	        MAIN Local Procedure Variables/Cursors 																			 
****************************************************************************************************/  
                               
    p_debug_record      	   			XXRI_PO_FY_EXPORT_PKG.c_debug_tbl; 
    l_debug_cur         				sys_refcursor; 

BEGIN
OPEN l_debug_cur FOR
     'SELECT'||
     ' SYSDATE                                CREATION_DATE,'||
     ' SYSDATE + 1                            FUTURE_DATE '||
     'FROM'||
     ' dual'
   ;

FETCH l_debug_cur BULK COLLECT
    INTO  p_debug_record;
    CLOSE l_debug_cur;
    
    sub1(p_debug_record); 


edit - Sub1 acceptance

PROCEDURE sub1 (p_debug_record XXRI_PO_FY_EXPORT_PKG.c_debug_tbl)
AS...

[Updated on: Tue, 29 April 2008 13:15]

Report message to a moderator

Re: Passing Dynamic Collection to SubRoutines [message #317168 is a reply to message #317129] Tue, 29 April 2008 15:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You can, but if you do so then you cannot have your sub1 procedure in your package, only in your package body, because the parameter type is not in the package and you also must have the sub1 procedure in your package body before your main procedure, otherwise it is out of scope, unless you use a forward declaration. Here is another demo using another variation of my prior example.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    PROCEDURE main
  4  	 (p_tab IN VARCHAR2);
  5  END your_pkg;
  6  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    TYPE r_your_rec IS RECORD
  4  	 (deptno	  dept.deptno%TYPE,
  5  	  dname 	  dept.dname%TYPE,
  6  	  loc		  dept.loc%TYPE);
  7    TYPE c_your_tab IS TABLE OF r_your_rec INDEX BY BINARY_INTEGER;
  8    --
  9    PROCEDURE sub1
 10  	 (p_your_tab IN c_your_tab)
 11    IS
 12    BEGIN
 13  	 FOR i in 1 .. p_your_tab.COUNT LOOP
 14  	   DBMS_OUTPUT.PUT_LINE (p_your_tab(i).deptno || ',' || p_your_tab(i).dname || ',' || p_your_tab(i).loc);
 15  	 END LOOP;
 16    END sub1;
 17    --
 18    PROCEDURE main
 19  	 (p_tab IN VARCHAR2)
 20    IS
 21  	 v_your_tab	    c_your_tab;
 22  	 v_ref		    SYS_REFCURSOR;
 23    BEGIN
 24  	 OPEN v_ref FOR 'SELECT * FROM ' || p_tab;
 25  	 FETCH v_ref BULK COLLECT INTO v_your_tab;
 26  	 CLOSE v_ref;
 27  	 sub1 (v_your_tab);
 28    END main;
 29  END your_pkg;
 30  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.main ('dept')
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 


Re: Passing Dynamic Collection to SubRoutines [message #317169 is a reply to message #317129] Tue, 29 April 2008 15:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following is what I am saying that you cannot do if the definition of the c_debug_tbl is in your package body only and not your package.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE XXRI_PO_FY_EXPORT_PKG
  2  AS
  3    PROCEDURE main
  4  	 (p_fiscal_yr IN VARCHAR2);
  5    PROCEDURE sub1
  6  	 (p_debug_record XXRI_PO_FY_EXPORT_PKG.c_debug_tbl);
  7  END XXRI_PO_FY_EXPORT_PKG;
  8  /

Warning: Package created with compilation errors.

SCOTT@orcl_11g> SHOW ERRORS
Errors for PACKAGE XXRI_PO_FY_EXPORT_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: Declaration ignored
6/43     PLS-00302: component 'C_DEBUG_TBL' must be declared
SCOTT@orcl_11g> 

Re: Passing Dynamic Collection to SubRoutines [message #317170 is a reply to message #317107] Tue, 29 April 2008 15:41 Go to previous message
Steve Corey
Messages: 336
Registered: February 2005
Location: RI
Senior Member
Barbara -

Excellent explanation, as usual. Thank you very much for your input. Given the constraints, I think I will move the record and table types to the package spec. This will probably serve better for future revisions as there may be components that have yet to be built that will need to reference the table type.

Again, thank you for taking the time to help me out.

Regards,
Steve
Previous Topic: Design Document for stored procedure
Next Topic: Alter table Modify Datatype
Goto Forum:
  


Current Time: Wed Dec 07 04:40:47 CST 2016

Total time taken to generate the page: 0.14061 seconds