Converting a dynamic query to use bind variables in Oracle 11g

Question

I have a dynamic search query that I would like to convert to make use of bind variables. The dynamic portion of the of query is in the where clause, and using a series of if statements to build a string that is concatenated to the rest of the query string. That query is then used in the for clause of a openstatement, which the result set is the return parameter. I'm not really sure how to accomplish this.

Here is the stored procedure:

PROCEDURE run_search(i_unit_id           IN lu_unit.fsu_id%TYPE,
                     i_equipment         IN tbl_component.component%TYPE,
                     i_equipment_status  IN tbl_component.equipment_status%TYPE,
                     i_equipment_type    IN tbl_component.equipment_type%TYPE,
                     i_equipment_subtype IN tbl_component.equipment_sub_type%TYPE,
                     i_system_id         IN tbl_component.system_id%TYPE,
                     i_association_code  IN tbl_component_assc_code.assc_code%TYPE,
                     i_manufacturer      IN lu_component_manu_model.equipment_manufacturer%TYPE,
                     i_manumodel         IN lu_component_manu_model.equipment_model%TYPE,
                     o_results           OUT sys_refcursor)  AS

  v_query VARCHAR2(32767) := '';
  v_where VARCHAR2(32767) := ' 1= 1';

BEGIN

  IF i_unit_id IS NOT NULL THEN 
    v_where := v_where || ' AND unit_id=''' || i_unit_id ||''' ';
  END IF;
  IF i_equipment IS NOT NULL THEN 
    v_where := v_where || ' AND lower(component) LIKE ''%' || lower(i_equipment) ||'%'' ';
  END IF;
  IF i_equipment_status IS NOT NULL THEN 
    v_where := v_where || ' AND equipment_status=''' || i_equipment_status ||''' ';
  END IF;
  IF i_equipment_type IS NOT NULL THEN 
    v_where := v_where || ' AND equipment_type=''' || i_equipment_type ||''' ';
  END IF;
  IF i_equipment_subtype IS NOT NULL THEN 
    v_where := v_where || ' AND equipment_sub_type=''' || i_equipment_subtype ||''' ';
  END IF;
  IF i_system_id IS NOT NULL THEN
    v_where := v_where || ' AND system_id=''' || i_system_id || ''' ';
  END IF;
  IF i_association_code IS NOT NULL THEN
    v_where := v_where || ' AND EXISTS ( select null from tbl_component_assc_code where assc_code = ''' || i_association_code || ''' and component_id = vcs.component_id )';
  END IF;
  IF i_manufacturer IS NOT NULL THEN
    v_where := v_where || ' AND equipment_manufacturer=''' || i_manufacturer || ''' ';
  END IF;
  IF i_manuModel IS NOT NULL THEN
    v_where := v_where || ' AND equipment_model=''' || i_manuModel || ''' ';
  END IF;

  v_query := 
    '     SELECT rownum, results.* '
  ||'     FROM '
  ||'       ( SELECT '
  ||'           count(*) OVER () ' || ' as total_results, '
  ||''
  ||'           site_id, site_display_name, '
  ||'           unit_id, unit_display_name, '
  ||'           system_id, system_display_name, '
  ||'           component_id, component, component_description, equipment_description, '
  ||'           equipment_status, equipment_model, equipment_serial_number, equipment_type, equipment_sub_type, '
  ||'           template_ids '
  ||''
  ||'         FROM vw_component_search '
  ||'         WHERE ' || v_where 
  ||'         ORDER BY unit_display_name, component '
  ||'       ) results '
  ;
  OPEN o_results FOR v_query;
END run_search;

See this http://stackoverflow.com/questions/29774122/are-pl-sql-variables-in-cursors-effectively-the-same-as-bind-parameters/29774973#29774973 — Lalit Kumar B, Apr 23 '15 at 13:09

score 1 · Answer 1 · answered Apr 23 '15 at 13:18

The best way is to avoid such headache at all. 'SP returning resultset' is a usual practise in a poor legacy things such as MSSQL2000, but in unnecessary and doubtful in Oracle.

If you wish to do this, I'd advice you to do something like this:

procedure MakeGarbage(value_mask varchar2) return sys_refcursor is
  cur integer;
  stmt varchar2(32000 byte);
  type TParamTable is table of varchar2(1000) index by varchar2(20);
  params TParamTable;
  i varchar2(20);
begin
  stmt := 'select * from table where 1 = 1 ';
  if value_mask is not null then
    stmt := stmt || ' and value like :value_mask ';
    params('value_mask') := value_mask;
  end if;
  ...
  cur := dbms_sql.create_cursor;
  dbms_sql.open_cursor(cur, stmt, dbms_sql.native);
  i := params.first;
  while i is not null loop
    dbms_sql.bind_variable(i, params(i));
    i := params.next(i);
  end loop;
  return dbms_sql.to_ref_cursor(cur);
end;

Finally a guy who properly understands how to create dynamic statements in Oracle. You don't find so many here. — Wernfried Domscheit, Apr 23 '15 at 13:23
Could you possibly explain this answer a little better, because I'm having a hard time following. — Rob M, Apr 23 '15 at 13:31
Seems like a brilliant answer . I hope the writer of this answer could explain a little bit more so that I (as a beginner ) can understand better. — , Jul 28 '20 at 07:57

score 1 · Accepted Answer · answered Apr 23 '15 at 13:47

You can write the query without dynamically creating it so you include all the parameters and just ignore those which are NULL (please profile it to test whether there are any performance issues compared to a dynamic query):

PROCEDURE run_search(i_unit_id           IN lu_unit.fsu_id%TYPE,
                     i_equipment         IN tbl_component.component%TYPE,
                     i_equipment_status  IN tbl_component.equipment_status%TYPE,
                     i_equipment_type    IN tbl_component.equipment_type%TYPE,
                     i_equipment_subtype IN tbl_component.equipment_sub_type%TYPE,
                     i_system_id         IN tbl_component.system_id%TYPE,
                     i_association_code  IN tbl_component_assc_code.assc_code%TYPE,
                     i_manufacturer      IN lu_component_manu_model.equipment_manufacturer%TYPE,
                     i_manumodel         IN lu_component_manu_model.equipment_model%TYPE,
                     o_results           OUT sys_refcursor) 
AS
BEGIN
  OPEN o_results FOR
  SELECT rownum,
         results.*
  FROM   ( SELECT count(*) OVER () as total_results,
                  site_id,
                  site_display_name,
                  unit_id,
                  unit_display_name,
                  system_id,
                  system_display_name,
                  component_id,
                  component,
                  component_description,
                  equipment_description,
                  equipment_status,
                  equipment_model,
                  equipment_serial_number,
                  equipment_type,
                  equipment_sub_type,
                  template_ids
           FROM   vw_component_search
           WHERE (   i_unit_id IS NULL
                 OR  unit_id= i_unit_id )
           AND   (   i_equipment IS NULL
                 OR  lower(component) LIKE '%' || lower(i_equipment) || '%' )
           AND   (   i_equipment_status IS NULL
                 OR  equipment_status= i_equipment_status )
           AND   (   i_equipment_type IS NULL
                 OR  equipment_type= i_equipment_type )
           AND   (   i_equipment_subtype IS NULL
                 OR  equipment_sub_type= i_equipment_subtype )
           AND   (   i_system_id IS NULL
                 OR  system_id= i_system_id )
           AND   (   i_association_code IS NULL
                 OR  EXISTS ( select null
                              from   tbl_component_assc_code
                              where  assc_code = i_association_code
                              and component_id = vcs.component_id ) )
           AND   (   i_manufacturer IS NULL
                 OR  equipment_manufacturer= i_manufacturer )
           AND   (   i_manuModel IS NULL
                 OR  equipment_model= i_manuModel )
           ORDER BY unit_display_name, component
         ) results;
END run_search;

(I've not compiled the above code - so there may be some errors).

score 0 · Answer 3 · answered Apr 24 '15 at 09:47

every reference to a PL/SQL variable is in fact a bind variable.

you can check this asktom link https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177

and check "Dynamic SQL" from this link http://www.akadia.com/services/ora_bind_variables.html

Converting a dynamic query to use bind variables in Oracle 11g

3 Answers3