i want to use pl/sql collection type in my package as a lookup table.
I have a small table containing productCode (integer filed) and ProductName.
I am using this small table inside my code multiple time in join to get the productName (using as a lookup)
instead of joining this table multiple times in the code , i want to load the data from this table inside
a collection and use this as join in the sql join.
to achieve this i wrote this below small block.
drop table test11;
create table test11(productCode integer);
create table test12(Product varchar2(200) )
-- Using Associative array
declare
TYPE ProductNameTab IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;
ProductName ProductNameTab; -- Associative array variable
begin
--populate
ProductName(1970311) := 'Shoe';
ProductName(1970324) := 'Shirt';
ProductName(1970325) := 'Trouser';
insert into test12 ( select ProductName(productCode) from test11);
end;
/
I am getting below error
ORA-06550: line 11, column 41:
PLS-00201: identifier 'PRODUCTCODE' must be declared
ORA-06550: line 11, column 29:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 1:
PL/SQL: SQL Statement ignored
Can you please guide how to use a small table as a collection in pl/sql code for lookup purpose.
Thanks in Advance.