Dynamic sql statement - depending on numbers of variables

Question

my problem is that I am trying to build a SELECT statement, that will depend on numbers of variables that I will pass to them.

Important thing to know that I use Oracle database.

Is there any efficent way to build such statement?

for example I have three varaibles (Connected with "AND' keyword) that I can pass to database and it should give me one unique result. opt1 - (WHERE var1=x AND var2=y AND var3=z)

But also there is an option that I will pass only two varaibles and get results opt 2 - WHERE var1=x AND var2=y) one variables or none and get every record from database.

I don't want to build countless number of different select statement. There must be a way of doing this elegant.

PS. This is a problem connected with JDBC adapter from webmethods. Maybe anyone know how to solve this in this enviroment?

Answer 1

You can use an associated array to store and retrieve the elements.Here I have shown you to construct the query. You can use a cursor or other options to execute it. You can extend this as procedure to pass the array of variables as argument. Refer Passing an associative array as a parameter between packages for such implementation.

SET SERVEROUTPUT ON;
DECLARE
TYPE where_elems
IS
  TABLE OF VARCHAR2 (100) INDEX BY VARCHAR2 (30);
  wh where_elems;
  v_query VARCHAR2 (4000) := 'SELECT col , col2 FROM yourtable where 1=1 ';
  v_idx   VARCHAR2 (30);
BEGIN
  wh ('col3')  := 'value3'; --you can initialize and call a procedure with array as argument.
  wh ('col4')  := 'value4';
  v_idx        := wh.FIRST;

  WHILE (v_idx IS NOT NULL)
  LOOP
    v_query := v_query || ' AND ' || v_idx || ' = ' || wh (v_idx);
    v_idx   := wh.NEXT (v_idx);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (v_query);
END;
/

Output

SELECT col , col2 FROM yourtable where 1=1  AND col3 = value3 AND col4 = value4