CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-types
DKCroat
DKCroat

Reputation: 355

Associative array issue

I have created an associative array, I understand it can be used different way of writing but however just need tips how to make this work. Currently when I compile this block I would receive no data found. Thank you!

    DECLARE
        TYPE type_state IS TABLE OF VARCHAR(50)
        INDEX BY VARCHAR2(50);
        tbl_state type_state;
        lv_statecity1_txt VARCHAR2(30):= 'TAMPA';
        lv_statecity2_txt VARCHAR2(30):= 'ATLANTA';
        lv_statecity3_txt VARCHAR2(30):= 'NYC';
        lv_cnt_num NUMBER(5) := 0;
    BEGIN
        tbl_state('FLORIDA') := lv_statecity1_txt;
        tbl_state('GEORGIA') := lv_statecity2_txt;
        tbl_state('New_York') := lv_statecity3_txt;
        FOR i IN 1..tbl_state.count loop
            IF tbl_state(i) IS NOT NULL THEN
              LV_CNT_NUM := LV_CNT_NUM + 1;
              dbms_output.put_line(tbl_state(i));
            END IF;
        END LOOP;
        dbms_output.put_line('That''s it folks');
    END;

Upvotes: 0

Views: 560

Answers (1)

user5683823
user5683823

Reputation:

tbl_state is a table of strings indexed by strings - passing in index values 1, 2, 3 (numbers) won't work.

It is true that array pairs are still ordered (first, second etc.), but accessing them in a loop is a bit more complicated. You will need a WHILE loop, and the index (I kept the name i to match your code as closely as possible) must be declared to be the same data type and length as the keys in the array.

DECLARE
        TYPE type_state IS TABLE OF VARCHAR(50)
        INDEX BY VARCHAR2(50);
        tbl_state type_state;
        lv_statecity1_txt VARCHAR2(30):= 'TAMPA';
        lv_statecity2_txt VARCHAR2(30):= 'ATLANTA';
        lv_statecity3_txt VARCHAR2(30):= 'NYC';
        lv_cnt_num NUMBER(5) := 0;                 --  WHAT IS THIS FOR? NEEDED??
        i varchar2(50);                            --  Notice this line
    BEGIN
        tbl_state('FLORIDA') := lv_statecity1_txt;
        tbl_state('GEORGIA') := lv_statecity2_txt;
        tbl_state('New_York') := lv_statecity3_txt;
        i := tbl_state.first;                      --  And this line
        while (i is not null) loop                 --  And this one
              LV_CNT_NUM := LV_CNT_NUM + 1;
              dbms_output.put_line(tbl_state(i));
              i := tbl_state.next(i);              --  And this one
        END LOOP;
        dbms_output.put_line('That''s it folks');
    END;
/

Upvotes: 3

Related Questions