Dynamically add where clauses to a cursor in oracle

Question

I have plsql procedure which accepts certain parameters e.g. v_name, v_country, v_type.

I wish to have a cursor with a select statement like this:

select column from table1 t1, table2 t2
where t1.name = v_name
and t1.country = v_country
and t1.id = t2.id
and t2.type = v_type

If certain parameters are empty can I only add the relevant where clauses to the cursor? Or is there a better way to accomplish this?

Answer 1

One way would be to build up your query as a string then use execute immediate

Answer 2

The best way to use this is with DBMS_SQL.

You create a string that represents your SQL statement. You still use bind variables. It's painful.

It goes something like this (I haven't compiled this, but it should be close) :-

CREATE OR REPLACE FUNCTION find_country( v_name  t1.country%TYPE,
                                         v_type  t2.type%TYPE)  /* Hmm, column called type? */
DECLARE
  v_SQL     varchar2(2000);
  v_select       INTEGER;   /* "Pointer" to a DBMS_SQL select statement */
  v_execute      INTEGER;

BEGIN
  v_SQL := 'select column from table1 t1, table2 t2 ||
           'where t1.id = t2.id';

  IF v_name IS NOT NULL THEN
    v_SQL := v_SQL || ' AND t1.country = :v_name'
  END IF;

  IF v_type IS NOT NULL THEN
    v_SQL := v_SQL || ' AND t2.type = :v_type';
  END IF;

  /* Setup Cursor */
  v_select := dbms_sql.open_cursor;     
  dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);

  IF v_name IS NOT NULL THEN
    dbms_sql.bind_variable( v_select, ':v_name', v_name );
  END IF;

  IF v_type IS NOT NULL THEN
    dbms_sql.bind_variable( v_select, ':v_type', v_type );
  END IF;

  DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column);  /* This is what we have selected */

  /* Return value from EXECUTE is undefined for a SELECT */     
  v_execute := DBMS_SQL.EXECUTE( v_select );

  IF DBMS_SQL.FETCH_ROWS( v_select ) > 0 THEN

    /* A row was found  
    DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);

    /* Tidy Up */
    DBMS_SQL.CLOSE_CURSOR(v_select);

    RETURN v_ID_address;

  ELSE

     DBMS_SQL.CLOSE_CURSOR(v_select);

     /* No row */
     RETURN NULL;
  END IF;

  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_open(v_select) THEN
        DBMS_SQL.CLOSE_CURSOR(v_select);
      END IF;
      RAISE;
END;

This approach is so painful compared to just writing the SQL inline that unless you have heaps of columns sometimes it's just easier writing a couple of different versions using this syntax:

FOR r IN (SELECT blah FROM blah WHERE t1 = v_t1) LOOP
   func( r.blah );
END LOOP;

Answer 3

It's not directly what you're asking, but it may be an acceptable solution:

select column from table1 t1, table2 t2
where
    (v_name is null or t1.name = v_name)
and (v_country is null or t1.country = v_country)
and t1.id = t2.id
and (v_type is null or t2.type = v_type)

Answer 4

The best way to do this would be to use Oracle's Application Context feature, best defined as best performance and security.

The faster way would be what hamishmcn suggested, using EXECUTE IMMEDIATE. I'd choose that over WW's suggestion of DBMS_SQL every time.

Another way that's quickest to write but won't perform as well would be something like this:

select column from table1 t1, table2 t2
where t1.name = nvl(v_name, t1.name)
and t1.country = nvl(v_country, t1.country)
and t1.id = t2.id
and t2.type = nvl(v_type, t2.type)

Answer 5

You do not have to use dbms_sql to solve this problem and you can still use normal cursor by using a ref cursor.

Sample: DECLARE TYPE cursor_ref IS REF CURSOR; c1 cursor_ref; r1 table1.column%type; BEGIN l_sql := 'select t1.column from table1 t1, table2 t2 where t1.id = t2.id '; if v_name is not null then l_sql := l_sql||' and t1.name = '||v_name ; end if; if v_country is not null then l_sql := l_sql||' and t1.country = '||v_country'; end if; if v_type is not null then
l_sql := l_sql||' and t2.type = '||v_type'; end if; open c1 for l_sql; loop fetch c1 into r1; exit when c1%notfound; -- do something end loop; close c1; end; /

You can make this better by binding the variables with the command 'using' like this: open c1 for l_sql using v_name, v_country;