Use Custom Type to Create in Memory Array - PLS-00306 Wrong Number of Arguments

Question

I am trying to create an in memory array of all the tables which have spatial metadata and a spatial index.

*I have run into an error,

PLS-00306 Wrong Number of Arguments

, in the code below on the line:**

v_idx_info_arr(v_array_counter) := 
                 idx_info_arr('THE_TABLE','THE_OWNER','THE_INDEX_NAME');

Why is this the wrong number of arguments when the type "index_info" has 3 fields of type varchar2(100)?

I do plan to change the code from that^ to idx_info_arr(rec.index_name, rec.table_owner, rec.table_name); but for testing I've left it with just hard coded strings.

I also am not sure about how the custom types and arrays work here in PL/SQL because I've been piecing it together from documentation and stack overflow questions. Any corrections on those declarations and usage is welcome.

DECLARE
    TYPE index_info IS RECORD(
        table_name varchar2(100),
        table_owner varchar2(100),
        index_name varchar2(100)
    );

    CURSOR all_geom_tables IS
    SELECT gt.owner, gt.table_name, gt.column_name, gt.srid
        FROM all_sdo_geom_metadata gt
        WHERE SRID = 8311            
        ORDER BY TABLE_NAME;

    type idx_info_arr is table of index_info;
    array v_idx_info_arr := idx_info_arr();
    v_array_counter integer := 0;

    v_table_geom sdo_geometry;
    v_spt_index varchar2(100);
    v_view_count integer;
    v_index_count integer;
BEGIN 
-- Start Loop Through all tables in database
    FOR db_table IN all_geom_tables LOOP  
        -- Loop through all tables which have a spatial index
        FOR rec IN (select table_name, table_owner, index_name 
   from ALL_SDO_INDEX_INFO WHERE 
 TABLE_OWNER = db_table.owner
    AND TABLE_NAME = db_table.table_name) LOOP                    
            DBMS_OUTPUT.PUT_LINE('Index name is: ' || rec.index_name);                    
            -- record the index here
      v_idx_info_arr(v_array_counter) := idx_info_arr('THE_TABLE','THE_OWNER','THE_INDEX_NAME');            
            v_idx_info_arr.extend();
            v_array_counter := v_array_counter + 1;
        END LOOP;
    END LOOP;
END;

Kaushik Nayak · Accepted Answer · 2019-04-11 03:18:15Z

1

For versions below Oracle 18c, You cannot assign all the record element at once. They must be assigned individually. Also, v_idx_info_arr.extend is required before the loop as even after initialisation without specifying elements, the collection would still be zero sized.

Code for versions upto Oracle 12.2

DECLARE
     TYPE index_info IS RECORD ( table_name        VARCHAR2(100),
     table_owner       VARCHAR2(100),
     index_name        VARCHAR2(100) );
     TYPE idx_info_arr IS
          TABLE OF index_info;
     v_idx_info_arr    idx_info_arr := idx_info_arr ();
     v_array_counter   INTEGER := 1;

BEGIN
     v_idx_info_arr.extend;
     FOR i IN 1..10 LOOP
          v_idx_info_arr(v_array_counter).table_name := 'THE_TABLE';
          v_idx_info_arr(v_array_counter).table_owner := 'THE_OWNER';
          v_idx_info_arr(v_array_counter).index_name := 'THE_INDEX_NAME';
          v_idx_info_arr.extend ();
          v_array_counter := v_array_counter + 1;
     END LOOP;
END;
/

Oracle 18c introduced Qualified expressions which lets you to do so.

v_idx_info_arr(v_array_counter)  := 
                  index_info('THE_TABLE','THE_OWNER','THE_INDEX_NAME');

DEMO for 18c

answered Apr 11, 2019 at 3:18

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1567453 Over a year ago

Thanks Kaushik, that solved my problem and made my code a lot better

Collectives™ on Stack Overflow

Use Custom Type to Create in Memory Array - PLS-00306 Wrong Number of Arguments

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related