Using a BULK COLLECT variable in a procedure and referencing it's columns without declaring it all again

Question

I am using a bulk collect to collect the values from a big selection of address fields (shortened to make the code smaller) and I want to pass that variable into a different procedure in the package, then reference those columns. What's the type for the procedure? I've got the bulk collect part set up fine, it's just consuming it (the procedure create_location( part) without having to rewrite and redefine all the variables again I'm having trouble with. Or can I not do this?

procedure create_adrx(
            v_sub_id                                                                in number
        ) AS

            TYPE adrx_info_rectype is RECORD(
                ADRX1                                                               VARCHAR(1000)
                , ADRX2                                                             VARCHAR2(1000)
                , CITY                                                              VARCHAR2(200)
                , COUNTY                                                            VARCHAR2(50)
                , STATE                                                             VARCHAR2(40)
                , ZIP                                                               VARCHAR2(50)
                , SITE_NAME                                                         VARCHAR2(30)
                , GEO_LAT                                                           NUMBER
                , GEO_LONG                                                          NUMBER
            );

            TYPE adrx_info_tbl is TABLE OF adrx_info_rectype;

            adrx_info                                                               adrx_info_tbl;
            v_location_id_result                                                    NUMBER;
            v_party_site_id_result                                                  NUMBER;
            v_account_number_result                                                 NUMBER;

        BEGIN
        
            SELECT
                NVL(sl.ADDRESS1, asi.ADDRESS1)                                      as ADRX1
                , NVL(sl.ADDRESS2, asi.ADDRESS2)                                    as ADRX2
                , NVL(sl.CITY, asi.CITY)                                            as CITY
                , NVL(sl.COUNTY, asi.COUNTY)                                        as COUNTY
                , NVL(sl.STATE, asi.STATE)                                          as STATE
                , NVL(sl.ZIP, asi.ZIP)                                              as ZIP
                , NVL(sl.SITE_NAME, esi.ALT_SITE_NAME)                              as SITE_NAME
                , NVL(sl.LATITUDE, asi.LATITUDE)                                    as GEO_LAT
                , NVL(sl.LONGITUDE, asi.LONGITUDE)                                  as GEO_LONG
            BULK COLLECT INTO
                adrx_info
            FROM
                TBL_SUBS ts
                LEFT JOIN TBL_SUB_LEVEL sl on sl.sub_id = ts.sub_id
                LEFT JOIN TBL_ALT_SUB_LEVEL asl on asl.sub_id = ts.sub_id
                LEFT JOIN TBL_ALT_SITE_INFO asi on asi.ALT_SITE_ID = asl.ALT_SITE_ID
                LEFT JOIN TBL_ALT_EXT_SITE_INFO esi on esi.ALT_EXT_SITE_ID = asi.ALT_EXT_SITE_ID and esi.CUST_ID = ts.CUST_ID
            WHERE
                ts.sub_id = v_sub_id;
            
            -- CREATE LOCATION
            create_location(adrx_info);
        
procedure create_location(
        adrx_info                                                                   in TABLE (??)
    ) as
    
    BEGIN
        
        run statements using adrx_info.ADRX1, adrx_info.STATE, etc...
        
    END;

Answer 1

If I understood you correctly, then: instead of creating types within the PL/SQL procedure (so that they are "visible" only in it), create them on SQL level (using the CREATE TYPE statement). Then you can use it wherever you want, in all your PL/SQL procedures, without having to re-type everything again.

Or, if it is a package, create type in package specification and it will then be used in all procedures within that package.