Hi everybody,
I am looking for a way of creating any kind of collections (PL/SQL TABLE, VARRAY, ARRAY, etc.) so that every element is of type REF CURSOR.
Something like
TYPE r_ref_cursor IS REF CURSOR
TYPE tab_of_ref_cursor IS TABLE OF type_ref_cursor INDEX BY BINARY_INTEGER;
The goal is to write a PL/SQL Script, so that I can parametrize the creation of several REF CURSOR.
Actually, for N Oracle DB, identical, same Datamodel, set of tables, same structure, only that they are physically distributed, I want to minimize the code, in a way that I create a REF CURSOR for each table, in a PROCEDURE, and then I can re-use that PROCEDURE when I want to read every remote DB (via DBLINK).
Example
IF DBLINK = 'USA' THEN
OPEN rr FOR ( select * from T1@USA );
tab_of_ref_cursor(i) := rr;
i:= i+1;
OPEN rr FOR ( select * from T2@USA );
tab_of_ref_cursor(i) := rr;
i:= i+1;
and so on
END IF;
IF DBLINK = 'SAF' THEN
OPEN rr FOR ( select * from T1@SAF );
tab_of_ref_cursor(i) := rr;
i:= i+1;
OPEN rr FOR ( select * from T2@SAF );
tab_of_ref_cursor(i) := rr;
i:= i+1;
and so on
END IF;
Your help and input will be appreciated
Best Regards
Gabriel Lazcano