Oracle stored procedure: FOR LOOP over a column from a variable

Question

I want to save a list of column values into the variable input_cols and then loop over the values

CREATE OR REPLACE PROCEDURE map_values (mapping_table VARCHAR2(64)) AS
TYPE col_names IS TABLE OF VARCHAR2(64);
input_cols col_names;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT COLUMN_NAME FROM SYS.DBA_TAB_COLUMNS 
        WHERE TABLE_NAME = '' ' || mapping_table || ' '' '
    BULK COLLECT INTO (input_cols);
    FOR in_col IN input_cols
    LOOP 
        dbms_output.put_line ('test');
    END LOOP;
END;

I am getting the error

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: * & - + / at mod remainder rem .. <an exponent (**)> ||

Duplicate of https://stackoverflow.com/questions/50601918/iterate-over-table-of-objects-in-oracle ? — Caius Jard, Apr 03 '21 at 07:44

William Robertson · Answer 1 · 2021-04-03T14:46:45.667

Although you can construct dynamic queries by concatenating values, it's generally better to use bind variables where possible, for example:

execute immediate
    'select column_name from user_tab_columns where table_name = :b1'
    bulk collect into input_cols
    using p_table;

I recommend getting into the habit of anchoring types in your code to the corresponding database object, when there is one. For example, this:

mapping_table dba_tab_columns.table_name%type

instructs the compiler to look up the type of dba_tab_columns.table_name and use that. However, I would generally avoid the dba_ views in procedures like this and stick to user_ views, e.g. user_tab_columns, to limit them to objects you own. If you must use dba_ views, you should also include the table owner, as there may be more than one table with the same name.

I also prefer to name my parameters in a way that separates them from column names etc. There are various conventions (camelCase, prefixing with i_ for in or p_ for parameter, prefixing with the procedure name e.g. map_values.mapping_table), so pick one you like.

Putting that together, you get something like this:

create or replace procedure map_values
    ( p_table user_tab_columns.table_name%type )
as
    type col_names is table of user_tab_columns.column_name%type;
    input_cols col_names;
begin
    execute immediate
        'select column_name from user_tab_columns where table_name = :b1 order by column_id'
        bulk collect into input_cols
        using p_table;

    for i in 1..input_cols.count loop 
        dbms_output.put_line(input_cols(i));
    end loop;
end map_values;

Or, if you don't specifically need a collection and just want to loop through a result set:

create or replace procedure map_values
    ( p_table user_tab_columns.column_name%type )
as
    columns_cur sys_refcursor;
    colname user_tab_columns.column_name%type;
begin
    open columns_cur for
        'select column_name from user_tab_columns where table_name = :b1 order by column_id'
        using p_table;

    loop 
        fetch columns_cur into colname;
        exit when columns_cur%notfound;
        dbms_output.put_line(colname);
    end loop;
    
    close columns_cur;
end;

As Koen pointed out in the comments, though, there is no need for dynamic SQL in this example, so a much simpler version could be just:

create or replace procedure map_values
    ( p_table user_tab_columns.column_name%type )
as
begin
    for r in (
        select column_name from user_tab_columns
        where  table_name = p_table
        order by column_id
    )
    loop
        dbms_output.put_line(r.column_name);
    end loop;
end map_values;

Personally I prefer a cursor for loop with a select statement in favour of the last example. Don't have to fetch, exist or close. — Koen Lostrie, Apr 03 '21 at 10:49
That's an excellent point - this doesn't need to use dynamic SQL at all. I kind of assumed that the OP wanted help with dynamic SQL syntax and this was just a simplified example, but you are right, maybe they just didn't think of it. — William Robertson, Apr 03 '21 at 14:42

score 1 · Answer 2 · edited Apr 03 '21 at 09:29

Word of advice: use a tool like SQL Developer to create your procedures. They show the compilation errors in a much clearer way. If you're new to PL/SQL, start with the very basics (empty procedure), compile, fix error if any and add code. There are 3 blocking issues in your code - debugging that is pretty hard.

I added a comment for each of the errors

create or replace PROCEDURE map_values 
(mapping_table VARCHAR /* just define the datatype, not the precision */
)
AS
TYPE col_names IS TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
input_cols col_names;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT COLUMN_NAME FROM SYS.DBA_TAB_COLUMNS 
        WHERE TABLE_NAME = ''' ||mapping_table|| ''' '
    BULK COLLECT INTO input_cols; /* no brackets needed */
        dbms_output.put_line ('test:');

    FOR in_col IN 1 .. input_cols.COUNT /* this is not a implicit cursor but a collection - you need to iterate over it.*/
    LOOP 
        dbms_output.put_line ('test:'||input_cols(in_col));
    END LOOP;
END;
/

score 0 · Answer 3 · answered Apr 03 '21 at 12:35

0

Please do not use dynamic SQL for this use case, static SQL is sufficient and will be more efficient.

For looping, you have to use indexes and get the element from collection by index. There is no functionality that will enable you to directly assign collection element to a variable (like you are trying).

create or replace procedure map_values(mapping_table sys.dba_tab_columns%table_name)
as
  type col_names is table of sys.dba_tab_columns.column_name%type;
  input_cols col_names;
begin
  select column_name
  bulk collect into input_cols
  from sys.dba_tab_columns
  where table_name = mapping_table;
  
  for i in 1 .. input_cols.count
  loop
    dbms_output.put_line(input_cols(i));
  end loop;
end;

answered Apr 03 '21 at 12:35

Petr

540
1
3
9

The new [`for x in values of y` iterator syntax in 21c](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-control-statements.html) simplifies collection navigation somewhat and might be closer to what the OP dreamt of. – William Robertson Apr 03 '21 at 15:12
@WilliamRobertson you're right, that's exactly it, thanks for pointing this new feature out! Maybe I'll get to using it in 10-15 years (when we finally upgrade) :) – Petr Apr 03 '21 at 18:05
Yes, same here. I'm just playing with a 21c Free Cloud instance to check out the new features and save my old laptop the work of running 19c locally. There's no way they'll have it at my work for a few years yet, sadly. – William Robertson Apr 04 '21 at 15:49

Oracle stored procedure: FOR LOOP over a column from a variable

3 Answers3