[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Referencing a VARRAY within a VARRAY

I have a function where I want to create 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.

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 when I need 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:

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);
      WHEN y THEN
            xref_desc   := gv_base(??);
   END CASE;
   RETURN xref_desc;
END;
0
prinprog
Asked:
prinprog
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Interesting question.

Worst case, create a temp instance of  reverses_v then reference the individual member.

You would think there has to be a way without the tmp one.  I'll keep working on it but here is the TMP example (I moved from a function to an anonymous pl/sql block for ease):
declare
   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'));
   gv_tmp     reverses_vv;
BEGIN
	gv_tmp := gv_base(2);
        dbms_output.put_line(gv_tmp(2));
END; 
/

Open in new window

0
 
prinprogAuthor Commented:
Well that's a possible work-around I'd not thought of. Thanks for the idea so far.
0
 
slightwv (䄆 Netminder) Commented:
I apologize for not getting back to this.  It was one of those days.  I have not forgotten this.

I'll try to get a little time later this evening or first thing tomorrow.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Not sure why I didn't try this last night.

It seems to be just like any other two-dimensional array: (parent_offset)(child_offset)

declare
   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'));
   gv_tmp     reverses_vv;
BEGIN
        --get the C
        dbms_output.put_line(gv_base(1)(1));

        --get the D
        dbms_output.put_line(gv_base(1)(2));

        --get the F
        dbms_output.put_line(gv_base(2)(2));
END; 
/

Open in new window

0
 
prinprogAuthor Commented:
It works! Wow, I can't believe I didn't try that. Or maybe I did and had something else wrong. Anyway, thank you so much for spending your time on this!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now