Apex select list vales as column names-CodePudding

I have a select list (:P31_LIST) with static values (column1, column2, column3) and a table with the same columns names.

How to select and insert data into a column of the table selected by the user, using the select list? How can I use the value of the select list as column table name ? I tried this query but no data was inserted or selected:

-- Select
select column1, :P31_LIST,
---Tried also this
case when :P31_LIST= 'column1' then (select column1 from my_table) END as test,

from my_table

-- Insert Insert into my_table(:P31_LIST, column2) SELECT A, B FROM source_table

my_table looks like this:

column1 | column2 | column3
   1    |     2   |     3

CodePudding user response：

For a SELECT you can either, use a CASE expression and whitelist the column names:

select column1,
       CASE :P31_LIST
       WHEN 'column1' THEN column1
       WHEN 'column2' THEN column2
       WHEN 'column3' THEN column3
       ELSE NULL
       END AS value
from   my_table

Or, using dynamic SQL in a PL/SQL block:

DECLARE
  v_sql CLOB := 'select column1, ' || :P31_LIST || ' AS value from my_table';
BEGIN
  FOR r IN v_sql LOOP
    DBMS_OUTPUT.PUT_LINE( r.column1 || ', ' || r.value);
  END LOOP;
END;
/

For an INSERT, use PL/SQL:

BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO my_table(' || :P31_LIST || ', column2) SELECT A, B FROM source_table';
END;
/

or whitelist the columns:

BEGIN
  IF :P31_LIST = 'column1' THEN
    INSERT INTO my_table(column1, column2) SELECT A, B FROM source_table;
  ELSIF :P31_LIST = 'column3' THEN
    INSERT INTO my_table(column3, column2) SELECT A, B FROM source_table;
  END IF;
END;
/