Creating or simulating two dimensional arrays in PL/SQL
Here's an example of using an multidimensional array in pl/sql. Here I use an array containing an array.
declare
type t_features is table of varchar(100) index by pls_integer;
type t_car_rec is record
(
make varchar2(50),
model varchar2(50),
features t_features
);
type t_car_tab is table of t_car_rec index by pls_integer;
car_tab t_car_tab;
procedure show_detail is
car_idx pls_integer;
features_idx pls_integer;
begin
car_idx := car_tab.first;
loop
exit when car_idx is null;
dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);
features_idx := car_tab(car_idx).features.first;
loop
exit when features_idx is null;
dbms_output.put_line(' =>' || car_tab(car_idx).features(features_idx));
features_idx := car_tab(car_idx).features.next(features_idx);
end loop;
car_idx := car_tab.next(car_idx);
end loop;
end;
begin
-- using sequential index values
car_tab(1).make := 'Ferrari';
car_tab(1).model := 'Testarossa';
car_tab(1).features(1) := 'Fast';
car_tab(1).features(2) := 'Looks cool';
car_tab(1).features(3) := 'Expensive';
-- using random index values (sparse)
car_tab(2).make := 'Acura';
car_tab(2).model := 'TSX';
car_tab(2).features(14) := 'Small';
car_tab(2).features(200) := 'Good MPG';
car_tab(2).features(36) := 'Inexpensive';
show_detail;
end;
Output would be:
Details for Ferrari Testarossa
=>Fast
=>Looks cool
=>Expensive
Details for Acura TSX
=>Small
=>Inexpensive
=>Good MPG
Hope that helps
How to create a multidimensional array
You can think of the following code as each row in table2 has a collection of type table1. You can insert any number of values as is shown in BEGIN.
DECLARE
TYPE table1 IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE table2 IS TABLE OF table1
INDEX BY PLS_INTEGER;
var_i table2
BEGIN
var_i (1) (1) := 1;
var_i (1) (2) := 12;
END;
Oracle PL/SQL - How to create a simple array variable?
You can use VARRAY for a fixed-size array:
declare
type array_t is varray(3) of varchar2(10);
array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
for i in 1..array.count loop
dbms_output.put_line(array(i));
end loop;
end;
Or TABLE for an unbounded array:
...
type array_t is table of varchar2(10);
...
The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.
With either of these you need to both initialise and extend the collection before adding elements:
declare
type array_t is varray(3) of varchar2(10);
array array_t := array_t(); -- Initialise it
begin
for i in 1..3 loop
array.extend(); -- Extend it
array(i) := 'x';
end loop;
end;
The first index is 1 not 0.
oracle pl/sql arrays
There are array type in PL/SQL but we can create those ourselves using the table
declare
type NumberArray is table of number index by binary_integer;
myArray NumberArray;
begin
myArray(0) := 1
myArray(1) := 2
--or use a for loop to fill
end;
The explanation article
EDIT :
or as Adam Musch said if we know the data size of data, that we are operating on, we can use VARRAYs that are length fixed, this is oracle environment, so subscripts start from 1,
Alternative is using VARRAY, where array subscript starts from 1 and the length of VARRAYs is fixed.
Semantic:
declare type VarrayType is varray(size) of ElementType;
Example :
declare
type NumberVarray is varray(100) of NUMERIC(10);
myArray NumberVarray;
BEGIN
myArray := NumberVarray(1,10,100,1000,10000);
myArray(1) = 2;
for i in myArray.first..myArray.last
loop
dbms_output.put_line('myArray(' || i || '): ' || myArray(i));
end loop;
end;
END;
Output :
myArray(1) : 2
myArray(2) : 10
myArray(3) : 100
myArray(4) : 1000
myArray(5) : 10000
Create Dynamic Objects in PL/SQL
Maybe this helps.
declare
TYPE SIMPLE_LIST IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR(30);
TYPE LIST_OF_LISTS IS TABLE OF SIMPLE_LIST INDEX BY VARCHAR2(30);
ll LIST_OF_LISTS;
key_ll VARCHAR2(30);
key_sl VARCHAR2(30);
BEGIN
--dynamic
for i in 1 .. 10 loop
for j in 1..10 loop
ll('i='||i)('j='||j) := 'value='||j;
end loop;
end loop;
-- static
ll('A')('1'):='A1';
ll('A')('2'):='A2';
ll('A')('3'):='A3';
ll('A')('4'):='A4';
ll('A')('5'):='A5';
ll('B')('1'):='B1';
ll('B')('2'):='B2';
ll('B')('3'):='B3';
ll('B')('4'):='B4';
ll('B')('5'):='B5';
-- and how to iterate it.
key_ll := ll.first;
while (key_ll is not null)
loop
key_sl := ll(key_ll).first;
dbms_output.put_line(key_ll);
while (key_sl is not null)
loop
dbms_output.put_line(' key sl: '||key_sl||' value sl: '||ll(key_ll)(key_sl));
key_sl := ll(key_ll).next(key_sl);
end loop;
key_ll := ll.next(key_ll);
end loop;
END foo;
How to add values to a VARRAY using a loop
As the error message says, you need to initialise the collection variable:
...
BEGIN
codes_ := code_array_();
...
But you also need to size it, either with a single extension each time around the loop:
FOR i IN 1..26 LOOP
codes_.extend;
...
Or a one-off extension before you start:
...
BEGIN
codes_ := code_array_();
...
codes_.extend(26);
FOR i IN 1..26 LOOP
...
You could also use the post-extend size to control the loop, to save hard-coding 26 again:
DECLARE
TYPE code_array_ IS VARRAY(26) OF VARCHAR2(6);
codes_ code_array_;
BEGIN
codes_ := code_array_();
codes_.extend(26);
FOR i IN 1..codes_.count LOOP
codes_(i) := dbms_random.string('U',6);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Read more about collections.
PLS-00355 error whike creating a new table type
You can define a nested table collection type in the SQL scope using:
CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR(250);
You can define an associative array collection type in a PL/SQL scope using:
DECLARE
TYPE DAYS_T IS TABLE OF VARCHAR(250) INDEX BY BINARY_INTEGER;
BEGIN
NULL;
END;
/
You could also locally define a nested-table collection type in a PL/SQL scope using:
DECLARE
TYPE DAYS_T IS TABLE OF VARCHAR(250);
BEGIN
NULL;
END;
/
However, you cannot define an associative array collection type in the SQL scope as it is a PL/SQL only data type.
You then asked in comments:
But how can I add an index?
They both have an index.
For example, after declaring the type in SQL, you can use the nested table collection in PL/SQL like this:
DECLARE
v_days DAYS_T;
BEGIN
v_days := DAYS_T(); -- Initialise the collection.
v_days.EXTEND(3); -- Extend the collection by 3 elements.
v_days(1) := 'Monday'; -- Set the first element.
v_days(3) := 'Wednesday'; -- Set the third element.
FOR i IN 1 .. v_days.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( i || ' = ' || v_days(i) );
END LOOP;
END;
/
Which outputs:
1 = Monday
2 =
3 = Wednesday
db<>fiddle here
ORA-06533: Subscript beyond count ORA-06512 in PL/SQL
A second look.
Creating multiple dimensional arrays (10 x 10) are tricky things. They are actually a collection of a collection. Further, each has the same definition and existence requirements: the element must exist before referenced, either by extend or array initialization. The following initializes each array before referencing it. Also it uses a FOR loop letting Postgres handle the setting, incrementing subscripts and loop exiting, rather than manually. See demo.
declare
-- PL SQL code to create and fill a two-dimensional array
-- create VARRAY type of 10 integers
type array_10_int is varray(10) of pls_integer;
-- create VARRAY type of array_10_int
type grid_100_int is varray(10) of array_10_int ;
-- declare a variable of the grid_100_int type
grid_var grid_100_int;
begin
grid_var := grid_100_int(null,null,null,null,null,null,null,null,null,null); -- initialize outer array
-- TO DO : use nested loop to fill grid_var with numbers 1- 100
/* YOUR CODE HERE */
for m in 1 .. 10
loop
grid_var(m) := array_10_int(null,null,null,null,null,null,null,null,null,null); -- initialize the inner array
for n in 1 .. 10
loop
grid_var(m)(n) := 10*(m-1)+ n;
end loop ;
end loop;
-- Print the grid with nested loop
for m in 1 .. 10
loop
for n in 1 .. 10
loop
dbms_output.put_line ('grid_var(' || to_char(m)
|| ')(' || to_char(n)
|| ') = ' || to_char(grid_var(m)(n))
);
end loop ;
end loop;
end;
Take away: Creating and using multiple dimensional arrays in Oracle is doable. But use them only when there is no other option. They add considerable complexity, usually unnecessary, and are poorly understood. (The above one is vary simple.)
Take away 2 Let Postgres control your loops. Less error prone, less code, easier to read.
PL/SQL array index begin with 1. But in the following code the local variable n is 0 when first used as an index.
Loop
M:=M+1;
N:=0;
LOOP
J:=j+1;
If grid_var(M)(N)<100 THEN --<<< n is 0 which throws your exception.
...
EXIT WHEN (N =100); --<<< NEVER occurs, N is not
Unfortunately, this is not your only issue (in this logic). You exit statement is condition EXIT WHEN (N =100); will never be met. You initialize the variable n before entering the loop, but never increment it in the loop.
Adjust the above to:
Loop
M:=M+1;
N:=1; -- <<< change here
LOOP
J:=j+1;
If grid_var(M)(N)<=100 THEN
...
EXIT WHEN (N >100); --<<< NEVER occurs, N is not incremented in the loop;
END LOOP;
Your other loop does not appear to have the same issue, but you should check it.
Related Topics
Why Can't I Use an Alias in a Delete Statement
Show All Rows That Have Certain Columns Duplicated
Repeat Rows N Times According to Column Value
Select Without a from Clause in Oracle
Postgresql - Dynamic Value as Table Name
How to Call an Oracle Stored Procedure from an Excel Vba Script
Checking If a String Is Found in One of Multiple Columns in MySQL
How to Format Date and Time on Ssrs Report
What Is the Purpose for Using Option(Maxdop 1) in SQL Server
Hql Query to Check If Size of Collection Is 0 or Empty
Postgresql Tables Exists, But Getting "Relation Does Not Exist" When Querying
Sqlite Order by Date1530019888000
Microsoft T-SQL to Oracle SQL Translation
How to Improve Performance for Datetime Filtering in SQL Server
How to Join Two Recordset Created from Two Different Data Source in Excel Vba
How to Expand Comma Separated Values into Separate Rows Using SQL Server 2005