Solved

Referencing a VARRAY within a VARRAY

Posted on 2014-02-26
6
306 Views
Last Modified: 2014-03-04
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
Comment
Question by:prinprog
  • 4
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39890650
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
 

Author Comment

by:prinprog
ID: 39890795
Well that's a possible work-around I'd not thought of. Thanks for the idea so far.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39893446
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39893608
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
 

Author Comment

by:prinprog
ID: 39903601
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39903606
Glad to help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now