Solved

Referencing a VARRAY within a VARRAY

Posted on 2014-02-26
6
314 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 77

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 77

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

690 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