Home » SQL & PL/SQL » SQL & PL/SQL » Adding array values to an array
Adding array values to an array [message #262948] Tue, 28 August 2007 10:21 Go to next message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Is it possible to add array entries as in:

TYPE COLOR_AT IS TABLE OF VARCHAR2(10); --INDEX BY BINARY_INTEGER
----------------------------------------------------------------
v_set_color_a COLOR_AT;
----------------------------------------------------------------

v_set_color_a := COLOR_AT('Red','Green','Blue');

What is the syntax for adding more entries to my Color array set. Is it possible to use:


IF statment
THEN
v_set_color_a := COLOR_AT('Yellow');
END IF;


So that my array will look like this
v_set_color_a := COLOR_AT ('Red','Green','Blue','Yellow');


Is this possible in PL/SQL?






[Updated on: Tue, 28 August 2007 10:22]

Report message to a moderator

Re: Adding array values to an array [message #262960 is a reply to message #262948] Tue, 28 August 2007 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> declare
  2    TYPE COLOR_AT IS TABLE OF VARCHAR2(10); --INDEX BY BINARY_INTEGER
  3    v_set_color_a COLOR_AT := COLOR_AT('Red','Green','Blue');
  4    v_set_color_b COLOR_AT := COLOR_AT('Yellow');
  5  begin
  6    v_set_color_a := v_set_color_a MULTISET UNION v_set_color_b;
  7    for i in v_set_color_a.first..v_set_color_a.last loop
  8      dbms_output.put_line (v_set_color_a(i));
  9    end loop;
 10  end;
 11  /
Red
Green
Blue
Yellow

PL/SQL procedure successfully completed.

Regards
Michel
Re: Adding array values to an array [message #262965 is a reply to message #262948] Tue, 28 August 2007 11:35 Go to previous messageGo to next message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Thanks Michel
Re: Adding array values to an array [message #262975 is a reply to message #262960] Tue, 28 August 2007 12:42 Go to previous messageGo to next message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Is this legal?
array

FOR i in 1..v_length
LOOP
v_color_a := v_color_a || COLOR_AT (v_i);

END LOOPl



This does not look possible but is there a way to append an input or variable to the array?
Re: Adding array values to an array [message #262977 is a reply to message #262975] Tue, 28 August 2007 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What are the type of your variables?
What are their values?
Post a full code like I did.

Regards
Michel
Re: Adding array values to an array [message #263005 is a reply to message #262977] Tue, 28 August 2007 14:13 Go to previous messageGo to next message
ronin10
Messages: 17
Registered: July 2007
Junior Member
Thanks for your help always Michel. I believe I have it. I need to use

v_color_a.extend;
v_color_a(v_color_a.count) := variable.


Re: Adding array values to an array [message #263180 is a reply to message #262960] Wed, 29 August 2007 05:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
v_set_color_a := v_set_color_a MULTISET UNION v_set_color_b;


Well, that's today's learning experience taken care of.
I've used Multiset to convert XML fragments into selectable data sources before, but I've never seen it used like this.

Aha - 10g adds a raft of new Multiset conditions and operators
Previous Topic: Dynamic SQL ??
Next Topic: skipping errors with insert select
Goto Forum:
  


Current Time: Mon Apr 28 03:04:36 CDT 2025