Binding Parameters to Oracle Dynamic SQL

Question

I have a stored procedure that accepts multiple parameters (i.e. pName, pHeight, pTeam)

I have the query built up like this:

SQLQuery VARCHAR2(6000);
TestCursor T_CURSOR;

SQLQuery := 'SELECT ID, Name, Height, Team FROM MyTable WHERE ID IS NOT NULL ';


-- Build the query based on the parameters passed.
IF pName IS NOT NULL
  SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
END IF;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = :pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
END IF;


OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;

If I execute the procedure passing all parameters, it runs properly.

But if I only passed one or two of the parameters, then the procedure errors out:

ORA-01006: bind variable does not exist

How do I selectively bind the variable with the parameters based on where the parameter value was used? For example, if only pName was passed, then I would only execute the query:

OPEN TestCursor FOR SQLQuery USING pName;

Or if both pName and pTeam was passed, then:

OPEN TestCursor FOR SQLQuery USING pName, pTeam;

Hope someone can shed more ways to resolve this. Thanks.

Edit: I could actually use the following:

-- Build the query based on the parameters passed. IF pName IS NOT NULL SQLQuery := SQLQuery || 'AND Name LIKE ''' || pName || ''' '; END IF;

IF pHeight IS > 0
  SQLQuery := SQLQuery || 'AND Height = pHeight ';
END IF;

IF pTeam IS NOT NULL
  SQLQuery := SQLQuery || 'AND Team LIKE ''' || pTeam || ''' ';
END IF;


OPEN TestCursor FOR SQLQuery;

But this would be VERY vulnerable to SQL Injection...

Answer 1

You can use the DBMS_SQL package. This provides an alternative way to run dynamic SQL. It is perhaps a little more cumbersome to use, but it can be more flexible, especially with varying numbers of bind parameters.

Here's how you could use it (warning: I haven't tested this):

FUNCTION player_search (
   pName        IN VARCHAR2,
   pHeight      IN NUMBER,
   pTeam        IN VARCHAR2
) RETURN SYS_REFCURSOR
IS 
  cursor_name   INTEGER;
  ignore        INTEGER;
  id_var        MyTable.ID%TYPE;
  name_var      MyTable.Name%TYPE;
  height_var    MyTable.Height%TYPE;
  team_var      MyTable.Team%TYPE;
BEGIN
  -- Put together SQLQuery here...

  -- Open the cursor and parse the query         
  cursor_name := DBMS_SQL.OPEN_CURSOR; 
  DBMS_SQL.PARSE(cursor_name, SQLQuery, DBMS_SQL.NATIVE); 

  -- Define the columns that the query returns.
  -- (The last number for columns 2 and 4 is the size of the
  -- VARCHAR2 columns.  Feel free to change them.)
  DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, id_var); 
  DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, name_var, 30); 
  DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, height_var); 
  DBMS_SQL.DEFINE_COLUMN(cursor_name, 4, team_var, 30); 

  -- Add bind variables depending on whether they were added to
  -- the query.
  IF pName IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':pName', pName);
  END IF;

  IF pHeight > 0 THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':pHeight', pHeight);
  END IF;

  IF pTeam IS NOT NULL THEN
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':pTeam', pTeam);
  END IF;

  -- Run the query.
  -- (The return value of DBMS_SQL.EXECUTE for SELECT queries is undefined,
  -- so we must ignore it.)
  ignore := DBMS_SQL.EXECUTE(cursor_name); 

  -- Convert the DBMS_SQL cursor into a PL/SQL REF CURSOR.
  RETURN DBMS_SQL.TO_REFCURSOR(cursor_name);

EXCEPTION 
  WHEN OTHERS THEN 
    -- Ensure that the cursor is closed.
    IF DBMS_SQL.IS_OPEN(cursor_name) THEN 
      DBMS_SQL.CLOSE_CURSOR(cursor_name); 
    END IF; 
    RAISE; 
END; 

(Note: DBMS_SQL.TO_REFCURSOR is new in Oracle 11g.)