Thanks for the question, Adrian.

Asked: August 09, 2002 - 11:28 am UTC

Last updated: March 27, 2003 - 1:31 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Tom

A couple of "when" questions for you, the first of them highly theoretical...

a) Associative Arrays
---------------------
It's good to have index-by PL/SQL tables indexed by varchar2 at last. They will be of great application to lookup tables, as were the index-by binary_integer for lookup tables with numeric PK columns. However, we must loop through these lookup tables to load the arrays. Wouldn't it be nice if we could do something like the following (pseudo-code)?

SELECT pk_col, description_col
BULK COLLECT INTO l_lookup
USING pk_col AS INDEX
FROM lookup_table;

...where l_lookup would be a table of description_col%type indexed by pk_col%type...

I don't know if this is possible or if anything like it is being developed for later release? It looks good in pseudo-code ;)

b) FORALL with table of records
-------------------------------
As of 9.2 we still can't reference individual elements of a table of records in FORALL. I know we can insert an entire record which is good and pdate using an entire record (but this is bad as we would update the primary keys and we need to limit the updates to their respective target rows using extra arrays for the FORALL WHERE clause). However, it would be nice to be able to do the following:-

FORALL i IN myCompositeArray.FIRST .. myCompositeArray.LAST
UPDATE my_table
SET my_col = myCompositeArray(i).my_col
WHERE my_pk = myCompositeArray(i).my_pk;

Do you have any idea if and when this will be available? I use the TABLE method at the moment (and TABLE(CAST( on 8i)...

Many thanks

Adrian

and Tom said...

a) it would be syntatically nice, yes, but until then -- see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3239385496862 <code>

it shows how to do this in a two step

b) you would have to use a record of arrays instead of an array of records. It has to do with how the data is physically organized (row major or column major) in the array. Array operations work on contigous arrays.

For example:

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int, y int );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
2 type foo is record
3 ( x int,
4 y int
5 );
6
7 type foo_array is table of foo index by binary_integer;
8
9 l_data foo_array;
10 begin
11 for i in 1 .. 2
12 loop
13 l_data(i).x := i;
14 l_data(i).y := i;
15 end loop;
16
17 forall i in 1 .. 2
18 insert into t values l_data(i);
19 end;
20 /

PL/SQL procedure successfully completed.

so, in some cases, we can use arrays of records -- when the interface is expecting a record at a time.... (using new 9ir2 feature to insert/update using RECORDS

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
2 type foo is record
3 ( x int,
4 y int
5 );
6
7 type foo_array is table of foo index by binary_integer;
8
9 l_data foo_array;
10 begin
11 for i in 1 .. 2
12 loop
13 l_data(i).x := i;
14 l_data(i).y := i;
15 end loop;
16
17 forall i in 1 .. 2
18 update t set y = l_data(i).y where x = l_data(i).x;
19 end;
20 /
update t set y = l_data(i).y where x = l_data(i).x;
*
ERROR at line 18:
ORA-06550: line 18, column 26:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 18, column 35:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 18, column 9:
PL/SQL: SQL Statement ignored

but because of the row major organization of the array here -- the elements of y are not contigous, hence we cannot use the array on Y or X..

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
2 type intArray is table of int index by binary_integer;
3 type foo is record
4 ( x intArray,
5 y intArray
6 );
7
8 l_data foo;
9 begin
10 for i in 1 .. 2
11 loop
12 l_data.x(i) := i;
13 l_data.y(i) := i;
14 end loop;
15
16 forall i in 1 .. 2
17 update t set y = l_data.y(i) where x = l_data.x(i);
18 end;
19 /

PL/SQL procedure successfully completed.

but here we can..

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Some reservations on record updates

Adrian Billington, August 14, 2002 - 10:19 am UTC

Tom

Thanks for workaround you pointed me to in a). Hadn't thought of that - I would have just looped through the target table and assigned the associative array directly row by row.

b) As far as using records of arrays goes, nothing has changed there. But as 9.2 now supports bulk collecting into an array of records it would have been useful to be able to forall using them as well?

Being able to insert using records in 9.2 is very good also, but the update needs a couple of major caveats mentioning.

First, you will update the PK if you have one (accepted as bad practice generally, especially when you have foreign keys).

Second, unless I'm missing a feature, the update using an entire record will not be targetted unless you maintain a separate array of the PK values to ensure each update only hits its own record:-

For example:-

FORALL i in array.FIRST .. array.LAST
UPDATE my_table
SET ROW = array(i);

...will update every row in the table with every array record.

Whereas:-

FORALL i in array.FIRST .. array.LAST
UPDATE my_table
SET ROW = array(i)
WHERE my_pk_column = array(i).pk_col;

...is not supported as identified in my original question.

So we have to:-

FORALL i in array.FIRST .. array.LAST
UPDATE my_table
SET ROW = array(i)
WHERE my_pk_column = separate_array_of_pk(i);

Am I missing something? Or is the update using record not as good as it could be?

Regards

Adrian

August 14, 2002 - 12:47 pm UTC

your observations on the UPDATE set row are mostly correct, yes.

table of record containing a table

Paul Ukken, November 19, 2002 - 4:51 pm UTC

Hi Tom,

i have a table of pl/sql records that i need pass back to java . But The pl/sql record itself needs to have a table of number.
i tried the following

declare
TYPE prev_membr_id IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
TYPE prgm_rec_type IS RECORD
(
program_id NUMBER
,l_prev_membr_id prev_membr_id
);
TYPE programDtails IS TABLE OF prgm_rec_type INDEX BY BINARY_INTEGER;
x_dtail_tbl programDtails;
begin
null;
end;

but i get the following error
PLS-00507: a PLSQL Table may not contain a table or a record with composite fields

How can i achieve this in pl/sql?. i heard that theres some new feature in Oracle Collections to achieve this. Also is there any specific way i need to handle this in java, if i am using the collections

November 19, 2002 - 5:32 pm UTC

You will never be passing a TABLE OF RECORDS back from PLSQL to any other language. Never.

If you use collection types (by defining a new SQL type) this becomes as easy as returning a REF CURSOR (result set) back to the client. See

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:246014735810

for an example of what I mean and if you've never used a ref cursor see

http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

Bulk collect on record of arrays?

Kashif, March 26, 2003 - 9:06 pm UTC

Hi Tom,

Can a record of arrays be bulk collected into? I'm trying that in my procedure but I keep getting a table is of wrong type error. Just need to reconfirm. Thanks.

Kashif

March 27, 2003 - 7:31 am UTC

you can bulk collect into arrays.

You can bulk collect into arrays of records in 9iR2.

You can have a record that is filled with arrays and bulk collect into each array.

not having a small, concise, simple example (and version, etc) -- cannot really comment further.

Actually...

Kashif, March 27, 2003 - 1:31 pm UTC

Thanks Tom, your response indicated that I had to populate the array elements of the record of arrays individually, as opposed to all in one shot. For example:

"declare
cursor c1 is select col1, col2
from table1;
charArray is table of varchar2 (2000) index by binary_integer;
type recType is record
(array1 charArray,
array2 charArray);
l_tabrec recType;

--instead of..
begin
open c1;
loop
fetch c1 bulk collect into l_tabrec;
end loop;

-- I am now doing...
open c1;
loop
fetch c1 bulk collect into l_tabrec.array1, l_tabrec.array2;
end loop;

end;"

I guess I hadn't read the docs close enough, bit of a rush. Thanks for the tip.

Kashif

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Some reservations on record updates

table of record containing a table

Bulk collect on record of arrays?

Actually...

More to Explore

PL/SQL demos

PL/SQL docs

9.2 associative arrays and forall frustration...

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Some reservations on record updates

table of record containing a table

Bulk collect on record of arrays?

Actually...

More to Explore

PL/SQL demos

PL/SQL docs