Hi Tom,

 Finally we got oracle 9i in our company.
 We have 9i R2.

 I am testing associative array and BULK COLLECT feature    together.


 ORGANIZATION_NAME                         NOT NULL VARCHAR2(110)

 I am getting errors

  1   declare
  2   type t_org  is table of varchar2(110) index by varchar2(110);
  3   t_org_arr     t_org;
  4   BEGIN
  5      select organization_name BULK COLLECT INTO t_org_arr
  6        from pubs.olv@renpqa1.world@pubs_link
  7      where delete_indicator = 0
  8         and main_point_flag ='Y';
  9       dbms_output.put_line('Oraganization :'||t_org_arr('Baker Rost'));
 10       dbms_output.put_line('Total :'||t_org_arr.count);
 11*  end;
SQL> /
    select organization_name BULK COLLECT INTO t_org_arr
                                               *
ERROR at line 5:
ORA-06550: line 5, column 48:
PLS-00657: Implementation restriction: bulk SQL with associative arrays with VARCHAR2 key is not sup
ORA-06550: line 5, column 48:
PLS-00597: expression 'T_ORG_ARR' in the INTO list is of wrong type
ORA-06550: line 6, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored

--Can you tell me where it is wrong?

-- In oracle 8i BULK COLLECT accross database link was not supported.Is it supported in ORACLE 9i?

-- Above error tells that associative array is not supported with BULK COLLECT.
  Can you explain why it is not supported?.
  
  If it support bulk collect then populating array and   seraching array would be the BEST.

 Thanks,

  

I have collection:
----------------------
CREATE TYPE my_rec_type AS OBJECT(
val_name VARCHAR2(10),
val VARCHAR2(30));

CREATE TYPE my_list_type AS TABLE OF my_rec_type;
-----------------------
my_list have data like:

my_list(1) = ('AGE','10');
my_list(2) = ('AGE','15');
my_list(3) = ('GENDER','M');
...

And I want to assign these data into 2-level associative array in PL/SQL using a single statement (no LOOP - this I know)


-------------------------
TYPE ty_my_val IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE ty_my_indexed_list IS TABLE OF ty_my_val INDEX BY VARCHAR2(10);
-------------------------

So the result will be:

my_indexed_list('AGE')(1) = '10'
my_indexed_list('AGE')(2) = '15'
my_indexed_list('GENDER')(1) = 'M'
...

Is it possible to do it in the way like (pseudocode) :

SELECT my_list(name,val) BULK COLLECT
INTO my_indexed_list(index,val)
from TABLE(CAST(my_list...

Thanks.

Hi Tom,

This is regarding the first question of this thread. I referred your book "Expert One On One Oracle" for the effective use of Hash Table. However I am
not clear whether I can use it for below requirement.

As a daily process, we are loading a inventory files which gives item with its unit. I am giving the dummy tables just for example purposes

The table structure is

create table item_details
(
stock_id number,
item_list varchar2(4000),
scheme_id varchar2(4000)
);

and records are

insert into item_details (stock_id, item_list) values (10, 'books,pen,pencil');
insert into item_details (stock_id, item_list) values (20, 'pen');
insert into item_details (stock_id, item_list) values (30, 'books,pencil');
insert into item_details (stock_id, item_list) values (40, 'books,pen');
insert into item_details (stock_id, item_list) values (50, 'books');
insert into item_details (stock_id, item_list) values (60, 'pencil');
insert into item_details (stock_id, item_list) values (70, 'cd-set,computer books,keyboard.monitor,mouse-pad,pen-drive');
insert into item_details (stock_id, item_list) values (80, 'books,keyboard.monitor,pen');
insert into item_details (stock_id, item_list) values (90, 'brush,paper,pen');
insert into item_details (stock_id, item_list) values (100, 'brush,pen');
commit;

Note - The Item List is always sorted alphabetically.

We have a set process to load this file on monthly basis and then transform the data into main tables.

As per new requirement, we will also get one more file which will have item lists and scheme code. We have designed below table


create table scheme_details
(
scheme_id number,
item_list varchar2(2000)
);

insert into scheme_details values (1, 'cd-set,pen,softwares,keyboard');
insert into scheme_details values (2, 'pencil,pen,books');
insert into scheme_details values (3, 'computer book,keyboard,mouse-pad,pen,pencil');
commit;

Now the requirement is to check the "item_list" from "scheme_details" and if any one item is matched with the "item_details.list" then append the
scheme id.

After the process records in "item_details" should be

STOCK_ID ITEM_LIST SCHEME_ID
-----------------------------------------------------------------------------------------------------------------
10 'books,pen,pencil' 1,2,3
20 'pen' 1,2,3
30 'books,pencil' 2,3
40 'books,pen' 1,2,3
50 'books' 2
60 'pencil' 3
70 'cd-set,computer books,keyboard.monitor,mouse-pad,pen-drive' 1,3
80 'books,keyboard.monitor,pen' 1,2,3
90 'brush,paper,pen' 1,2,3
100 'brush,pen' 1,2,3

The current number rows in "item_details" is > 1.2 M. And the estimated number of records in new "scheme_details" is around 50-60K.

We tried using the direct UPDATE but it is taking lot of time.

Can we use the hash-table for the pattern comparison? Can you please provide just the effectice PL-SQL design for such requirements?

Regards
Shravani

Hi,
When we use a collection of index by varchar2, is there a way to apply like clause rather than exists clause to check for an existance of an index.

eg:
DECLARE
TYPE T1 IS TABLE OF VARCHAR2(5) INDEX BY VARCHAR2(50);
T2 T1;
BEGIN
T2('INSTR-COUNTRY-DOM-US'):='US';
T2('INSTR-COUNTRY-INCORP-US'):='IN';
END;

Now, the requirement is to check for an index like 'INSTR-COUNTRY-INCORP%'. if there is one then thats what the code should reurn 'IN'. How to achieve this?