PL/SQL question: How to access a node in a VARRAY of VARRAYs

Toad for Oracle & DevOps Toolkit
1

I have a function where I have a VARRAY that is a collection of VARRAYs, and Oracle lets me define that. However, I can’t figure out how to reference it to get to the values. It appears that the only option for the index is a single integer, which only gets me to one of the “nested” VARRAY nodes, but not into that inner VARRAY to access the value of one of its nodes.

Here is the (pared-down) PL/SQL pseudo code. I have a variable that is a straight VARRAY (gv_self), which I am able to use with a simple integer to reference the element I want to fetch. But what is the syntax to access an element of a VARRAY that itself is an element of a VARRAY (gv_base)? I can’t find any documentation or code examples that illustrate this and am thinking it can’t actually be done:

CREATE FUNCTION f_func(parm_c_factor NUMBER)
RETURN VARCHAR2 IS
xref_desc VARCHAR2(30);
TYPE reverses_vv IS VARRAY(2) OF VARCHAR2(30);
TYPE line_vr IS VARRAY(2) OF reverses_vv;
gv_self reverses_vv := reverses_vv(‘A’, ‘B’);
gv_base line_vr := line_vr(reverses_vv(‘C’, ‘D’), reverses_vv(‘E’, ‘F’));
BEGIN
CASE
WHEN x THEN
xref_desc := gv_self(parm_c_factor + 1); – returns ‘A’ or ‘B’
WHEN y THEN
xref_desc := gv_base(??); – needs to return ‘C’, ‘D’, ‘E’, or ‘F’
END CASE;
RETURN xref_desc;
END;

2

Evening Ellen,

I am in bed right now and don’t have Oracle handy, :slight_smile: but as far as I remember you do:

Gv_base(1, 1)

which should return ‘C’. (1,2) will return ‘D’, (2,1) should give you ‘E’. I shall leave you to figure out how to get ‘F’. :wink:

I seem to remember a bug either in 10g or fixed in 10g similr to this. As far as I recall, if the above does not work, you might need:

Vv_temp reverses_vv;

Xyz xref_desc;

. . .

Vv_temp := gv_base(2);

Xyz :=vv_temp(2); – should be ‘D’.

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

Alternatively, you might need to use:

Gv_base(1)(1) to get ‘C’ etc.

Cheers,

Norm. [TeamT]

–

Sent from my Android device with K-9 Mail. Please excuse my brevity.