Oracle 11g – How to Create a Where Clause Programmatically

oracleoracle-11gwhere

Suppose I have a table like:

TBL_CONDITIONS

| ID | WHERE_CLAUSE                          |
|----|---------------------------------------|
| 1  | fruit = 'apple'                       |
| 2  | delicious = 'T'                       |
| 3  | fruit = 'apple' AND gluten_free = 'T' |
| 4  | delicious = 'F'                       |

I'd like to be able to select the where clause on this table based on user input. Say the user selected "3". The stored procedure is written thusly:

BEGIN
    SELECT * FROM SAMPLE WHERE (SELECT WHERE_CLAUSE 
                  FROM TBL_CONDITIONS WHERE ID = 3);
END;

Unfortunately, this doesn't work. Is there a way to run this type of query? Or another way to accomplish the same task?

Best Answer

PL/SQL Dynamic SQL

Below is a simple example:

set serveroutput on

declare
  l_condition varchar2(100);
  l_rc sys_refcursor;
  l_record sample%rowtype;
begin
  select where_clause into l_condition from tbl_conditions where id = 3;
  open l_rc for 'select * from sample where ' || l_condition;
  loop
    fetch l_rc into l_record;
    exit when l_rc%notfound;
    dbms_output.put_line(l_record.column1 || ', ' || l_record.column2);
  end loop;
end;
/
Related Question