StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBinding Parameters to Oracle Dynamic SQL
text
Body
copied!<p>I have a stored procedure that accepts multiple parameters (i.e. pName, pHeight, pTeam)</p> <p>I have the query built up like this:</p> <pre><code>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; </code></pre> <p>If I execute the procedure passing all parameters, it runs properly.</p> <p>But if I only passed one or two of the parameters, then the procedure errors out:</p> <pre><code>ORA-01006: bind variable does not exist </code></pre> <p>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:</p> <pre><code>OPEN TestCursor FOR SQLQuery USING pName; </code></pre> <p>Or if both pName and pTeam was passed, then:</p> <pre><code>OPEN TestCursor FOR SQLQuery USING pName, pTeam; </code></pre> <p>Hope someone can shed more ways to resolve this. Thanks.</p> <p>Edit: I could actually use the following:</p> <p>-- Build the query based on the parameters passed. IF pName IS NOT NULL SQLQuery := SQLQuery || 'AND Name LIKE ''' || pName || ''' '; END IF;</p> <pre><code>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; </code></pre> <p>But this would be VERY vulnerable to SQL Injection...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload