Home » SQL & PL/SQL » SQL & PL/SQL » FORALL and Associative Arrays (Oracle 10g2)
| FORALL and Associative Arrays [message #628757] |
Fri, 28 November 2014 04:08  |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Hi,
I have a quick question.. Can FORALL work with Associative Arrays?
I use a cursor to fetch data from the Database into a Nested Table. I then loop through the retrieved data and update one of two Associative Arrays (type1prods or type2prods). Each Array holds a count value for each Unique Product Id (The Index).
The data from these Arrays is then loaded into a Database table that includes the product Id and Count. The current code looks something like the below
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
type2prods prod_ids_table_type;
idx NUMBER;
BEGIN
-- type1prods is populated
-- 14545, 45
-- 14677, 1
-- 18900, 3
-- type2prods is populated and has a structure like:
-- 12345, 153
-- 14456, 134556
-- 18933, 56454
IF type1prods.count > 0 THEN
idx := type1prods.first;
WHILE idx IS NOT NULL LOOP
INSERT
INTO dwh.product_bands
(type,
product_id,
records,
currency,
closed)
VALUES
('P1TUK',
idx,
type1prods (idx),
'GBP',
to_number(NULL));
idx := type1prods.next(idx);
END LOOP;
END IF;
END;
But I would like to BULK INSERT the data, so something like:
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
type2prods prod_ids_table_type;
idx NUMBER;
BEGIN
-- type1prods is populated
-- 14545, 45
-- 14677, 1
-- 18900, 3
-- type2prods is populated and has a structure like:
-- 12345, 153
-- 14456, 134556
-- 18933, 56454
IF type1prods.count > 0 THEN
FORALL idx IN type1prods.FIRST..type1prods.LAST
INSERT
INTO dwh.product_bands
(type,
product_id,
records,
currency,
closed)
VALUES
('P1TUK',
idx,
type1prods (idx),
'GBP',
to_number(NULL));
idx := type1prods.next(idx);
END IF;
END;
But I receive an error here because the IDX is not allowed in this context for the FORALL statement. I could move from Associative Arrays to a Nested Table but before I do so I was wondering if, in general, FORALL statements can be made to work with Assciative Arrays.
|
|
|
|
| Re: FORALL and Associative Arrays [message #628762 is a reply to message #628757] |
Fri, 28 November 2014 04:37  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
It is not clear how you assign values to type1prods:
-- type1prods is populated
-- 14545, 45
-- 14677, 1
-- 18900, 3
Does it mean 14545 is the value and 45 is the index? Or 45 is the value and 14545 is the index? In both cases index values are sparse while FORALL requires dense index values:
SQL> create table tbl (name varchar2(20),id number);
Table created.
SQL> DECLARE
2 TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 type1prods prod_ids_table_type;
4 idx NUMBER;
5 BEGIN
6 type1prods(14545) := 45;
7 type1prods(14677) := 1;
8 type1prods(18900) := 3;
9 IF type1prods.count > 0
10 THEN
11 FORALL idx IN type1prods.FIRST..type1prods.LAST
12 INSERT
13 INTO tbl(
14 name,
15 id
16 )
17 VALUES(
18 'P1TUK',
19 type1prods (idx)
20 );
21 END IF;
22 END;
23 /
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [14546] does not exist
ORA-06512: at line 11
SQL>
Other than that, it should work:
SQL> DECLARE
2 TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 type1prods prod_ids_table_type;
4 idx NUMBER;
5 BEGIN
6 type1prods(14545) := 45;
7 type1prods(14546) := 1;
8 type1prods(14547) := 3;
9 IF type1prods.count > 0
10 THEN
11 FORALL idx IN type1prods.FIRST..type1prods.LAST
12 INSERT
13 INTO tbl(
14 name,
15 id
16 )
17 VALUES(
18 'P1TUK',
19 type1prods (idx)
20 );
21 END IF;
22 END;
23 /
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
| Re: FORALL and Associative Arrays [message #628774 is a reply to message #628757] |
Fri, 28 November 2014 05:54 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Firstly, thank to Solomon for really applicable test case.
It is obvious (see links to documentation below), that assignment of NEXT element of TYPE1PRODS to IDX is called after the FORALL LOOP.
You can either use LOOP ... END LOOP; (not FOR or FORALL) with that manual assignment to IDX inside that LOOP
or you can use INDICES OF in bounds_clause:
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
idx NUMBER;
BEGIN
type1prods(14545) := 45;
type1prods(14677) := 1;
type1prods(18900) := 3;
IF type1prods.count > 0 THEN
FORALL idx IN indices of type1prods
INSERT
INTO tbl(
name,
id
)
VALUES(
'P1TUK',
type1prods (idx)
);
END IF;
END;
/
When using some language feature, your first option would be studying it in appropriate documentation book.
FORALL statement is described in PL/SQL User's Guide and Reference for 10gR2 here: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm#sthref2742
(plus you would benefit from reading the chapter about PL/SQL control structures in the same book too)
|
|
|
|
| Re: FORALL and Associative Arrays [message #628778 is a reply to message #628774] |
Fri, 28 November 2014 06:38 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
flyboy wrote on Fri, 28 November 2014 06:54It is obvious (see links to documentation below), that assignment of NEXT element of TYPE1PRODS to IDX is called after the FORALL LOOP.
I perfectly understand that FORALL and assignment of NEXT element are two separate and completely unrelated statements. I guess I should be more specific: "FORALL with specified bounds requires dense index values between bounds".
SY.
[Updated on: Fri, 28 November 2014 06:40] Report message to a moderator |
|
|
|
| Re: FORALL and Associative Arrays [message #628819 is a reply to message #628778] |
Fri, 28 November 2014 10:51 |
mjm22
Messages: 54
Registered: January 2010
Location: Singapore
|
Member |
|
|
Thanks guys..
Just to clarify Solomon, the 45 is the value and 14545 is the INDEX.
But what I am wanting is to also insert the value of the Index. Using your example I can insert the VALUE easily enough as you have shown..
create table mjm_tbl (name number, entries number);
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
idx NUMBER;
BEGIN
type1prods(14545) := 45;
type1prods(14546) := 1;
type1prods(14547) := 3;
IF type1prods.count > 0
THEN
FORALL idx IN type1prods.FIRST..type1prods.LAST
INSERT
INTO mjm_tbl(
name,
entries
)
VALUES(
type1prods (idx),
type1prods (idx)
);
END IF;
END;
/
select * from mjm_tbl;
drop table mjm_tbl;
produces..
NAME ENTRIES
---------- ----------
45 45
1 1
3 3
But, I want to put the INDEX value into the table along with the VALUE..
create table mjm_tbl (name number, entries number);
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
idx NUMBER;
BEGIN
type1prods(14545) := 45;
type1prods(14546) := 1;
type1prods(14547) := 3;
IF type1prods.count > 0
THEN
FORALL idx IN type1prods.FIRST..type1prods.LAST
INSERT
INTO mjm_tbl(
name,
entries
)
VALUES(
idx,
type1prods (idx)
);
END IF;
END;
/
select * from mjm_tbl;
drop table mjm_tbl;
produces...
idx,
*
ERROR at line 18:
ORA-06550: line 18, column 18:
PLS-00430: FORALL iteration variable IDX is not allowed in this context
If I use a simple WHILE LOOP rather than the FORALL statement, I can insert the INDEX directly into the table..
create table mjm_tbl (name number, entries number);
DECLARE
TYPE prod_ids_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
type1prods prod_ids_table_type;
idx NUMBER;
BEGIN
type1prods(14545) := 45;
type1prods(14546) := 1;
type1prods(14547) := 3;
IF type1prods.count > 0
THEN
idx := type1prods.first;
WHILE idx IS NOT NULL LOOP
INSERT
INTO mjm_tbl(
name,
entries
)
VALUES(
idx,
type1prods (idx)
);
idx := type1prods.NEXT(idx);
END LOOP;
END IF;
END;
/
select * from mjm_tbl;
drop table mjm_tbl;
produces.....
NAME ENTRIES
---------- ----------
14545 45
14546 1
14547 3
So, can I get the FORALL statement to insert the INDEX value into mjm_tbl.name?
|
|
|
|
|
|
| Re: FORALL and Associative Arrays [message #628827 is a reply to message #628819] |
Fri, 28 November 2014 15:28 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mjm22 wrote on Fri, 28 November 2014 11:51So, can I get the FORALL statement to insert the INDEX value into mjm_tbl.name?
You could use associative array of records:
SQL> create table mjm_tbl (name number, entries number);
Table created.
SQL> DECLARE
2 TYPE prod_ids_table_type IS TABLE OF mjm_tbl%rowtype INDEX BY BINARY_INTEGER;
3 type1prods prod_ids_table_type;
4 idx NUMBER;
5 BEGIN
6 type1prods(1).name := 14545;
7 type1prods(1).entries:= 45;
8 type1prods(2).name := 14546;
9 type1prods(2).entries:= 1;
10 type1prods(3).name := 14547;
11 type1prods(3).entries:= 3;
12 IF type1prods.count > 0
13 THEN
14
15 FORALL idx IN type1prods.FIRST..type1prods.LAST
16 INSERT
17 INTO mjm_tbl
18 VALUES type1prods(idx);
19 END IF;
20 END;
21 /
PL/SQL procedure successfully completed.
SQL> select * from mjm_tbl;
NAME ENTRIES
---------- ----------
14545 45
14546 1
14547 3
SQL> drop table mjm_tbl purge;
Table dropped.
SQL>
SY.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 17:15:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
