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...

Candlewood

Answer 1

This is not hugely elegant but it would mean that you could always supply all three bind variables even if some of them are null. You only add the extra WHERE clauses if needed.

(I've tried to format the dynamic SQL to make it more readable, you could just supply it as one long string).

FUNCTION myFunc (
   pName   IN VARCHAR2,
   pHeight IN VARCHAR2,
   pTeam   IN VARCHAR2
)
   RETURN T_CURSOR
IS
   -- Local Variables
   SQLQuery   VARCHAR2(6000);
   TestCursor T_CURSOR;
BEGIN
   -- Build SQL query
   SQLQuery := 'WITH t_binds '||
                ' AS (SELECT :v_name AS bv_name, '||
                           ' :v_height AS bv_height, '||
                           ' :v_team AS bv_team '||
                      ' FROM dual) '||
               ' SELECT id, '||
                      ' name, '||
                      ' height, '||
                      ' team '||
                 ' FROM MyTable, '||
                      ' t_binds '||
                ' WHERE id IS NOT NULL';

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

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

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

   OPEN TestCursor 
    FOR SQLQuery 
  USING pName, 
        pHeight, 
        pTeam;

   -- Return the cursor
   RETURN TestCursor;
END myFunc;

I'm not in front of a workstation with DB access so I can't test the function but it should be close (please forgive any syntax errors, it's been a long day!)

Hope it helps...

Montemayor

Where was t_binds declared? Or do I need to declare it somewhere? – Candlewood

It's declared in the WITH clause in the SQL statement, it's a dummy table to hold the bind variables. See here: orafaq.com/node/1879 – Montemayor

Tried this but still says ORA-01008: not all variables bound – Candlewood

You'll have to give me more information than "Tried this but still says ORA-01008: not all variables bound" if you want help in resolving it. I use this method myself for almost exactly the same situation and it works fine, the syntax might be a little off as my answer explains but the basic premise is sound. – Montemayor

I essentially executed the proc you have provided... and it says the variables are not bound. – Candlewood

"essentially"? What did you change? – Montemayor

let us continue this discussion in chat – Candlewood

Thanks to Ollie. Works like a charm. Although I would love to know/hear if there's any downside to this solution so as to add to my learning. – Candlewood

Answer 2

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.)

Raspy

+1, I like using DBMS_SQL as it's more "supported" but a bit more involved than the method I suggested. – Montemayor

Answer 3

The approach I use is to include in the dynamic SQL an ELSE case the states the reverse of the IF. Your code tests that pName is not null, so I would add a clause to the generated query testing that pName IS Null. That way you can pass the same parameters every time without affecting the results of the query.

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 ';
ELSE 
  SQLQuery := SQLQuery || 'AND :pName IS NULL';
END IF;

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

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


OPEN TestCursor FOR SQLQuery USING pName, pHeight, pTeam;

Inesinescapable

Answer 4

How about

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

SQLQuery := SQLQuery || 'AND Name LIKE :pName ';
SQLQuery := SQLQuery || 'AND Team LIKE :pTeam ';
SQLQuery := SQLQuery || 'AND (Height = :pHeight OR :pHeight = 0)';

OPEN TestCursor FOR SQLQuery USING nvl(pName, '%'), nvl(pTeam, '%'), nvl(pHeight, 0), nvl(pHeight, 0);

?

Ow

like '%' doesnt affect null. so if some of these fields is null, u will get false – Bladdernut