Solved

Referencing a VARRAY within a VARRAY

Posted on 2014-02-26
6
313 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
Industry Leaders: 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!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
minium over 4 numeric columns for each row in oracle 2 55
Pivoting oracle table 9 90
error doing substr 3 51
DB Shutdown Automatically 11 32
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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