?
Solved

Referencing a VARRAY within a VARRAY

Posted on 2014-02-26
6
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 77

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 77

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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 77

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

764 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