CODEWITHSUNDEEP
plsqloracle10gassociative-arraydatabase-cursor
shearichard
shearichard

Reputation: 1105

Iterating over 'table of' to produce ref cursor

I have a number of old SP's which have output arguments of type 'table of', like this ...

TYPE tblSAM_DD_TEXT         IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
TYPE tblSAM_TYPE            IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE tbl_CONSOLIDATED_ID    IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;


PROCEDURE Get_Associated_Unicorns(
    UNIID_IN IN ABC_UNICORNS.UNI_AUTOID%TYPE,
    ID_OUT OUT tbl_CONSOLIDATED_ID,
    SAMDDTEXT_OUT OUT tblSAM_DD_TEXT,
    SAMTYPE_OUT OUT tblSAM_TYPE
);

... I'd like to adapt the existing processing so that the prototype of the sp's looked like this ...

TYPE t_cursor IS REF CURSOR ;
PROCEDURE Get_Associated_Unicorns(
    UNIID_IN IN ABC_UNICORNS.UNI_AUTOID%TYPE,
    v_cursor OUT t_cursor
);

... so that someone calling Get_Associated_Unicorns would have a ref cursor to consume rather than three 'table of' objects.

Using Oracle 10g PL/SQL how can I adapt the existing SP's so that I iterate over the 'table of' objects to populate a ref cursor which is then the sole output argument ?

(the three 'table of' arguments are always of the same length).

Upvotes: 1

Views: 1532

Answers (1)

steve godfrey
steve godfrey

Reputation: 1234

If you first create 3 types in your database 

CREATE TYPE tblSAM_DD_TEXT         IS TABLE OF VARCHAR2(50);
CREATE TYPE tblSAM_TYPE            IS TABLE OF NUMBER;
CREATE TYPE tbl_CONSOLIDATED_ID    IS TABLE OF VARCHAR2(32);

You can then refer to them in a procedure

CREATE or REPLACE PROCEDURE Get_Associated_Unicorns 
                                 ( p_tblSAM_DD_TEXT      IN  tblSAM_DD_TEXT
                                  ,p_tblSAM_TYPE         IN  tblSAM_TYPE
                                  ,p_tbl_CONSOLIDATED_ID IN  tbl_CONSOLIDATED_ID
                                  ,pc_refcursor          OUT sys_refcursor)
BEGIN

OPEN pc_refcursor FOR
SELECT tblSAM_DD_TEXT SAM_DD_TEXT
      ,tblSAM_TYPE    SAM_TYPE
      ,tbl_CONSOLIDATED_ID CONSOLIDATED_ID
FROM   (SELECT column_value tblSAM_DD_TEXT
              ,rownum       r1
        FROM   TABLE(p_tblSAM_DD_TEXT))
      ,(SELECT column_value tblSAM_TYPE
              ,rownum       r2
        FROM   TABLE(p_tblSAM_TYPE))
      ,(SELECT column_value tbl_CONSOLIDATED_ID
              ,rownum       r3
        FROM   TABLE(p_tbl_CONSOLIDATED_ID))
WHERE  r1 = r2
AND    r2 = r3;


END;

Upvotes: 1

Related Questions