1

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?

0

1 Answer 1

1

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;
/
1
  • That is what I'm looking for. However, you can actually put the entire select statement into a variable and do: open l_rc for l_condition;
    – scuba_mike
    Mar 25, 2016 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.