Question: Hello I have an array of strings (CompTblNameArry) that I want to contain certain usernames in database based on select query within cursor.

Code is shown below:

```
DECLARE
  TYPE SegTblTyp IS TABLE OF mto_kgpr.pdtable_12%rowtype;
  TYPE TblNameStringArry IS TABLE OF varchar2(61) INDEX BY PLS_INTEGER;
  --Type TblNameStringArry is varray(100) of varchar2(61);
  LoopIteration pls_integer;

  CURSOR GetUserNamesCursor is
  SELECT username FROM DBA_USERS WHERE USERNAME LIKE 'MTO_K%';

  SegCollectionTbl SegTblTyp;
  --CompTblNameArry CompTblNameArryTyp;
  CompTblNameArry TblNameStringArry;

    BEGIN
       LoopIteration := 1;

        FOR Username in GetUserNamesCursor
        LOOP
          DBMS_OUTPUT.PUT_LINE('Iteration: ' || LoopIteration || ' username ' || Username.Username);
          -- Row Below is causing Error
          --CompTblNameArry(i) = Username.Username;
          LoopIteration := LoopIteration + 1;

        END LOOP;
    END;

```

What is proper syntax for populating the CompTblNameArry? The method that I am trying is not working:

CompTblNameArry(i) = Username.Username;

Thanks,
Pat

Answer: 
There are few issues to your code. 
1) Fetch is missing into collection. 
2) Loop variable is wrong. 

See below:

```
DECLARE
   TYPE SegTblTyp IS TABLE OF mto_kgpr.pdtable_12%ROWTYPE;

   TYPE TblNameStringArry IS TABLE OF VARCHAR2 (61)
      INDEX BY PLS_INTEGER;

   --Type TblNameStringArry is varray(100) of varchar2(61);
   LoopIteration      PLS_INTEGER;

   CURSOR GetUserNamesCursor
   IS
      SELECT username
        FROM DBA_USERS
       WHERE USERNAME LIKE 'MTO_K%';

   SegCollectionTbl   SegTblTyp;
   --CompTblNameArry CompTblNameArryTyp;
   CompTblNameArry    TblNameStringArry;
BEGIN
   LoopIteration := 1;

   --fetch records of cursor to collection 
   OPEN GetUserNamesCursor;
   FETCH GetUserNamesCursor BULK COLLECT INTO CompTblNameArry;
   CLOSE GetUserNamesCursor;

   FOR Username IN 1..CompTblNameArry.count
   LOOP
      DBMS_OUTPUT.PUT_LINE (
         'Iteration: ' || LoopIteration || ' username ' || CompTblNameArry(Username));
      -- Row Below is causing Error
      --CompTblNameArry(i) = Username.Username;
      LoopIteration := LoopIteration + 1;
   END LOOP;
END;

```
Thanks to all for your kind support!

I found a working solution based on this support site and through internet searching.

I ended up with just using PL/SQL to generate a long union all statement, then I copy the union all statement from dbms\_output.put\_line into a regular sql and then run it from there. Of course I have to delete the first union all but other than that it is running perfectly!

\*\*\* Begin PL/SQL

```
DECLARE

   TYPE SegTblTyp IS TABLE OF mto_kgpr.pdtable_12%ROWTYPE;

   TYPE TblNameStringArry IS TABLE OF VARCHAR2 (61)

      INDEX BY PLS_INTEGER;

  SqlStatement VARCHAR2(32767);

   --Type TblNameStringArry is varray(100) of varchar2(61);

   LoopIteration      PLS_INTEGER;

   CURSOR GetUserNamesCursor

   IS

      select OWNER from all_tables where table_name = 'PDTABLE_12';

   SegCollectionTbl   SegTblTyp;

   CompTblNameArry TblNameStringArry;

   UserNameArry    TblNameStringArry;

BEGIN

   LoopIteration := 1;

   --fetch records of cursor to collection 

   OPEN GetUserNamesCursor;

   FETCH GetUserNamesCursor BULK COLLECT INTO UserNameArry;

   CLOSE GetUserNamesCursor;

   FOR Username IN 1..UserNameArry.count

   LOOP

    SqlStatement := SqlStatement || ' union all select ''' || UserNameArry(Username) || ''' as username, inst.instrument_comp_no, mdl.model_no, mdl.path_name, mdl.model_file_spec from pd' || substr(UserNameArry(Username),4) || '.pdtable_113 mdl, '|| UserNameArry(Username) ||'.pdtable_67 inst where instrument_comp_no like ''%-%V-%'' and inst.partition_no = mdl.partition_no';

      LoopIteration := LoopIteration + 1;

   END LOOP;

   DBMS_OUTPUT.PUT_LINE(SqlStatement);

END;

```

\*\*\* End PL/SQL