Organizational Research By

Surprising Reserch Topic

Experts Most Trusted Topic


binding parameters to oracle dynamic sql


binding parameters to oracle dynamic sql  using -'oracle,parameters,dynamic-sql'

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

asked Oct 6, 2015 by yashwantpinge
0 votes
17 views



Related Hot Questions



Government Jobs Opening


...