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