Using an array inside of a select statement

Jason Bourne 0
Viewed 28

I'm currently trying to create a dynamic action inside of Oracle APEX using a PL/SQL script that counts the amount of Yes and N/A responses in a table and return a percentage of the progress towards every cell being a Yes or N/A. Since the table is pretty large I was thinking that putting all of the column headers into an array and running the select statement in a for loop would be the most efficient way to do this. Here is what I have so far:

DECLARE
  type array IS VARRAY(20) OF VARCHAR2(1000);
  columns_array array := array('...','...',...);
  arr_Size number(4, 2) := 20;
  Counter number(5, 2);
  Progress number(5, 2);

BEGIN

FOR i in 1..arr_Size
 LOOP
    select COUNT(columns_array(i))
    into Counter
    from Table
    where array(i) = 'Yes' OR array(i) = 'N/A';
    Progress := Progress + Counter;
END LOOP;

END;

The program compiles without any errors but the code outputs zero for progress after running when it shouldn't. I think the problem is that I used an array inside of the select statement, but I don't know any other way to accomplish what I'm trying to do.

oracle-apex plsql
1 Answers

You can't use a variable as an identifier - table name, column name, alias etc. - in a static SQL statement. At the moment you're comparing the value of the array element - which happens to be a column name but could be anything - with the fixed values 'Yes' and 'N/A', so unless you have column with those names (which is unlikely) it will not match.

You can compare a variable with another values (as you are doing here), but you can't use a variable as a column name (as you intended).

You could use dynamic SQL, constructing a statement by concatenating in the column names from the array; a basic version could be:

DECLARE
  type array IS VARRAY(20) OF VARCHAR2(1000);
  columns_array array := array('COL1','COL2','COL3');
  counter pls_integer;
  progress pls_integer;
  statement varchar2(4000);

BEGIN

  -- need to initialise this
progress := 0;

FOR i in 1..columns_array.count
 LOOP
    statement := 'select COUNT(' || columns_array(i) || ') '
    || 'from your_table '
    || 'where ' || columns_array(i) || ' = ''Yes'''
      || ' or ' || columns_array(i) || ' = ''N/A''';

    -- just to debug
    dbms_output.put_line(statement);
    execute immediate statement into counter;

    progress := progress + counter;
    dbms_output.put_line('Counter: ' || counter || ' Progress: ' || progress);
END LOOP;

dbms_output.put_line('Progress: ' || progress);

END;
/

db<>fiddle

Which is a bit inefficient as you repeatedly query the same table. You could make the dynamic more complicated and only hit the table once, or use an XML trick to avoid PL/SQL entirely (similar to this).

But it would be simpler and more efficient to just use conditional aggregation - added to this db<>fiddle

Alex Poole0
Related