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?

You need to pass an array and cycle through it. Look at owa_util.ident_arr data structure in oracle. Loop over it from 1..length of array and build up the sql, then execute it. In the array store 'col1=x', 'col2=y' etc — Ab Bennett, Oct 26 '17 at 10:36
Is there any simpler way of doing this? I'm affraid I want be able to pass variables in my webmethods enviroment in array form. maybe there is an option for example: to check is the variable is null and implements appropriate logic then? — MDaniluk, Oct 26 '17 at 11:22
Check documentation of webmethods, usually I would expect that it supports some kind of array parameters. — Wernfried Domscheit, Oct 26 '17 at 11:31
if the number of variables you want to pass is unknown, then it must be an array. I worked at a place that built its queries this exact way. Every pl/sql report took this array — Ab Bennett, Oct 26 '17 at 11:41

score 1 · Accepted Answer · answered Oct 26 '17 at 11:45

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

Dynamic sql statement - depending on numbers of variables

1 Answers1