creating a dynamic where clause in oracle apex form

0
votes

I am creating a parameterized apex form where I take column name and its value from user via a select list. When I use it in where clause like

select columnnames 
from table 
where :P592_column = :P592_value ;

It is not returning any output, but when I hardcode column name in place of :P592_column it is showing output.

1
sqloracleoracle-apexdynamic-sql

1 Answers

2
votes

You cannot use a bind variable as a dynamic column name. Instead, whitelist the columns in a CASE statement:

SELECT columnnames 
FROM   table 
WHERE  CASE UPPER( :P592_column )
       WHEN 'COLUMN1' THEN column1
       WHEN 'COLUMN2' THEN column2
       WHEN 'COLUMN3' THEN column3
       WHEN 'COLUMN4' THEN column4
       END = :P592_value;

Or for multiple columns:

SELECT columnnames 
FROM   table 
WHERE  CASE UPPER( :P592_column )
       WHEN 'COLUMN1' THEN column1
       WHEN 'COLUMN2' THEN column2
       WHEN 'COLUMN3' THEN column3
       WHEN 'COLUMN4' THEN column4
       END LIKE '%' || :P592_value || '%'
AND    CASE UPPER( :P592_column1 )
       WHEN 'COLUMN1' THEN column1
       WHEN 'COLUMN2' THEN column2
       WHEN 'COLUMN3' THEN column3
       WHEN 'COLUMN4' THEN column4
       END LIKE '%' || :P592_value1 || '%'
AND    CASE UPPER( :P592_column2 )
       WHEN 'COLUMN1' THEN column1
       WHEN 'COLUMN2' THEN column2
       WHEN 'COLUMN3' THEN column3
       WHEN 'COLUMN4' THEN column4
       END LIKE '%' || :P592_value2 || '%';