Skip to Main Content
  • Questions
  • Binding variables to dynamic SQL for a cursor

Breadcrumb

Easter

Question and Answer

Chris Saxon

Thanks for the question, Aaron.

Asked: March 12, 2020 - 4:58 pm UTC

Answered by: Chris Saxon - Last updated: March 17, 2020 - 10:10 am UTC

Category: PL/SQL - Version: 12c

Viewed 100+ times

You Asked

Looking at examples on https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/11_dynam.htm I am trying to create a simple example of a procedure that returns a ref cursor and safely handles optional parameters. The two things I'm not clear on is how the bind variables in the query string are matched to the variables supplied in USING, and how to build a USING dynamically.

So given the following example from https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/11_dynam.htm:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;


Let's say we have a :k and :i in the dynamic query as well. How do I know :j corresponds to my_job? If there were multiple bind variables in the USING, is the pairing based on the alphabetical order of the bind variables, or is it the order in which they appear in the query string? A couple examples use :1 and :2 which implies maybe the alphabetic sort of the bind variable names is significant.

Just to emphasize my thought process here, in MS SQL you would explicitly define the pairings of variables so it isn't ambiguous, assuming pLastName and pFirstName are declared parameters, you tell sp_executesql explicitly which bind variables in the dynamic query correspond to which declared parameters:

EXEC sp_executesql @sql
 ,N'@lastName nvarchar(20), @firstName nvarchar(20)'
 ,@lastName = @pLastName, @firstName = @pFirstName;-- explicetely pair bind variables and declared parameters


For example, if I were doing this, should my using be switched because :firstName is alphabetically before :lastName, or is it correct as is because :lastName appears first in the query?

create or replace PROCEDURE ParameterizedSQL (pFirstName NVARCHAR(20), @pLastName NVARCHAR(20)
, oPeople out sys_refcursor)  
IS
optionalCriteria NVARCHAR2 = N'';
sql NVARCHAR2 = N'';
BEGIN 

IF(pFirstName IS NOT NULL)
 optionalCriteria := N' and FirstName = :firstName';
 
sql := N'SELECT * FROM People WHERE LastName = :lastName' + @optionalCriteria;

OPEN oPeople FOR sql USING pLastName, pFirstName; -- How to exclude pFirstName here if it was not appended to the query string?


Additionally, I'm not sure how to dynamically build the using. If pFirstName is null, then I get the error `ORA-01006: bind variable does not exist` because I supply two variables in the USING but the SQL string only contains one.

A full example demonstrating what I have so far, and the error:

https://livesql.oracle.com/apex/livesql/s/jsp26njlnr0hf5ix2u3hwc2el

with LiveSQL Test Case:

and we said...

OPEN ... FOR ... USING uses positional notation. So you need to bind the same number of parameters in the same order. This applies even if you use the same variable name!

create table t (
  c1 int
);

insert into t values ( 1 );

declare
  cur sys_refcursor;
  v1  pls_integer;
  v2  pls_integer;
begin
  open cur for 
    'select :b1 , :b2 from t where c1 = :b1' 
    using 1, 2, 1;
  
  fetch cur into v1, v2;
  
  SYS.dbms_output.put_line ( v1 || ' - ' || v2 );
end;
/

1 - 2


Clearly this can be a bit of a faff if you have lots of optional parameters. You may need complex IF/CASE statements to ensure you open the cursor with the correct bind values.

To get around this you can use dbms_sql to construct, bind, and execute your query. This uses named notation for binding values, with:

dbms_sql.bind_variable(curid, 'bind_name', 'bind_value' );


If necessary you can then call dbms_sql.to_refcursor to convert this to a ref cursor to process the results:

declare
  cur sys_refcursor;
  v1  pls_integer;
  v2  pls_integer;
  ret number;
  curid number;
begin
  
  curid := dbms_sql.open_cursor;

  dbms_sql.parse(curid, 'select :b1 , :b2 from t where c1 = :b1', dbms_sql.native);

  dbms_sql.bind_variable(curid, 'b1', 1 );
  dbms_sql.bind_variable(curid, 'b2', 2 );

  ret := dbms_sql.execute(curid);

  cur := dbms_sql.to_refcursor(curid);
  
  fetch cur into v1, v2;
  
  SYS.dbms_output.put_line ( v1 || ' - ' || v2 );
  
end;
/

1 - 2

and you rated our response

  (9 ratings)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

with WITH?

March 13, 2020 - 2:16 pm UTC

Reviewer: Dieter from Germany

How about:

CREATE OR REPLACE PROCEDURE parameterizedsql(pfirstname VARCHAR2,
                                             plastname  VARCHAR2,
                                             opeople    OUT SYS_REFCURSOR) IS
  optionalcriteria VARCHAR2(2000) := '';
  l_sql            VARCHAR2(2000) := '';
BEGIN

  IF pfirstname IS NOT NULL THEN
    optionalcriteria := ' and FirstName = c_firstName';
  END IF;

  l_sql := 'with all_criteria as (select :lastName c_lastname, :firstName as c_firstname from dual)
          SELECT * FROM People, all_criteria WHERE LastName = c_lastName' || optionalcriteria;

  OPEN opeople FOR l_sql
    USING plastname, pfirstname; -- Positional arguments same order as in all_criteria
END parameterizedsql;

with WITH 2nd Edition

March 13, 2020 - 2:21 pm UTC

Reviewer: Dieter from Germany

CREATE OR REPLACE PROCEDURE parameterizedsql(pfirstname VARCHAR2,
                                             plastname  VARCHAR2,
                                             opeople    OUT SYS_REFCURSOR) IS
  l_sql VARCHAR2(2000) := 'with all_criteria as (select :lastName c_lastname, :firstName as c_firstname from dual)
          SELECT * FROM People, all_criteria WHERE LastName = c_lastName 
           AND (c_firstName IS NULL or FirstName = c_firstName)';
BEGIN

  OPEN opeople FOR l_sql
    USING plastname, pfirstname; -- Positional arguments same order as in all_criteria
END parameterizedsql;

Chris Saxon

Followup  

March 13, 2020 - 4:20 pm UTC

Neat idea. Though it may not always be feasible to do this.

March 13, 2020 - 2:38 pm UTC

Reviewer: A reader


March 13, 2020 - 2:48 pm UTC

Reviewer: A reader

Thanks, very helpful.

I'll note regarding other examples, I certainly could have implemented this in static SQL using (pFirstName is null or pFirstName =...) pattern, which I would always favor static SQL over dynamic. But that doesn't always perform well since it can cause indexes to be ignored.
Chris Saxon

Followup  

March 13, 2020 - 4:19 pm UTC

But that doesn't always perform well since it can cause indexes to be ignored.

I'm not sure what you mean - could you clarify?

March 13, 2020 - 5:58 pm UTC

Reviewer: A reader

Chris, a slightly more complicated example regarding performance, where all parameters are optional (usually at least one of any combination enforced by application).

I've used this pattern several times but find in Oracle it never performs well, and looking at explain plans it isn't using indexes.

For example, won't use indexes:
select *
from Employees e
Where 
(e.FirstName is null or e.FirstName = pFirstName)
and (e.LastName is null or e.LastName = pLastName)


If I can negotiate functional requirements I will try to get at least one highly selectable indexed field designated as always required, and then the query can always uses the index for that one field and will perform fine:

select *
from Employees e
Where 
e.LastName = pLastName
and
(e.FirstName is null or e.FirstName = pFirstName)


If business requirements require all fields be optional(but any one provided at a minimum), then I find it performs well to union variations of the query where one parameter is included(so that each query will leverage an index). This gets pretty duplicative with many optional fields(I can usually use a CTE to factor out the base query to try and keep it DRY):

select *
from Employees e
Where 
e.LastName = pLastName)
and (e.FirstName is null or e.FirstName = pFirstName)

Union

select *
from Employees e
Where 
e.FirstName = pFirstName
and (e.LastName is null or e.LastName = pLastName)


With a CTE however, sometimes I find if I have a join it tries to use a bloom filter if it's part of a join if the CTE is shared by two queries, and that will perform poorly. I've found a work around by duplicating the CTE to get it back to hash joins. This is a whole other tangent that I've not tried to reproduce in SQL live.

with someOtherCriteria as (
  select SupervisorId, EmployeeId from Supervisors
)
, someOtherCriteria2 as (
  select SupervisorId, EmployeeId from Supervisors
)
select *
from Employees e
join someOtherCriteria s on e.EmployeeId= s.EmployeeId
Where 
e.LastName = pLastName)
and (e.FirstName is null or e.FirstName = pFirstName)

Union

select *
from Employees e
join someOtherCriteria2 s on e.EmployeeId= s.EmployeeId
Where 
e.FirstName = pFirstName
and (e.LastName is null or e.LastName = pLastName)

Chris Saxon

Followup  

March 16, 2020 - 11:13 am UTC

Yep, the optimizer often struggles to be able to use any indexes when you've got many optional parameters.

I discuss this further at:

https://blogs.oracle.com/sql/optional-search-parameters:-getting-rid-of-those-pesky-or-conditions

A brief recap:

- Changing (e.FirstName is null or e.FirstName = pFirstName) -> e.FirstName = NVL ( pFirstName, e.FirstName ) can get better plans
- The union method can end up doubling the work

Building up the statement so it only contains the non-null values avoids these issues.

Misconception

March 13, 2020 - 7:50 pm UTC

Reviewer: Dieter from Germany

In my example the reference is to the bind variable

AND (<b>c_firstname</b> IS NULL OR ...


and not the table column People.FirstName.

This has no impact on using indexes on the table column ...

March 13, 2020 - 7:57 pm UTC

Reviewer: Dieter from Germany

Hi Chris,

you said:

... it may not always be feasible to do this ...

What do you mean by that?
What are the case you can't do that?
I've been using that for several years and never had a case when it would be impossible, but there may be issues that I haven't considered yet ...

„There are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns – there are things we do not know we don't know.“

Donald Rumsfeld, 12 Feb 2002

Please elaborate.

TIA Dieter
Connor McDonald

Followup  

March 16, 2020 - 9:49 am UTC

I'm curious what the *benefit* here is using WITH over just building the SQL statement

Benefit ...

March 16, 2020 - 12:14 pm UTC

Reviewer: Dieter from Germany

IMHO:
You always have the same order of bind variables in the WITH clause and the USING clause (makes it easier to check for correctness / completeness).

If you need to reference a bind variable several times in the sql statement you still have to enter it only once in the USING clause.

For testing (especially lengthy statements): Only edit the WITH clause instead of finding all the occurencies throughout the statement.

Chris Saxon

Followup  

March 17, 2020 - 10:10 am UTC

Perhaps not infeasible, just impractical if you need to refer to the same variable many times in different subqueries. Leading to many references to all_criteria.

binding for optional parameters

March 17, 2020 - 2:33 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Tom has explained this in some detail at this column

https://blogs.oracle.com/oraclemagazine/on-popularity-and-natural-selection

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.