How can I use bind variable in plsql function

Question

I have a function like this:

create or replace function params
(
    p_pr     varchar2,
    p_qu_id  varchar2,
    p_date   date := sysdate,
    p_param  varchar2 := null
)
    return   varchar2
as
    ...
    sql_stmt varchar2(4000);
    rc       sys_refcursor;
    ...
BEGIN
    sql_stmt := 'select parameter_name, parameter_value from ' || p_pr | '.v_view where query_id = ''' || p_qu_id || '''';

    IF p_param IS NOT NULL
    THEN
        sql_stmt := sql_stmt || ' and parameter_value=''' || p_param || '''';
    END IF;

    OPEN rc FOR sql_stmt;
    LOOP
        FETCH rc
            INTO v_param_name, v_param_value;
        EXIT WHEN rc%NOTFOUND;
        EXIT WHEN v_param_value is NULL;

        ....

DBA said this function using hard parse, I must use bind variable in this function. How can I do that?

Thanks.

https://docs.oracle.com/database/121/LNPLS/dynamic.htm#GUID-EF9233BB-9570-453D-96EB-E13F43B10596 — , Dec 19 '17 at 08:27
You're missing a pipe here: `... from ' || p_pr |`. You won't be able to bind the p_pr variable into the query since it's part of the table name, but you can use [dbms_assert](https://oracle-base.com/articles/10g/dbms_assert_10gR2) to ensure that it meets the criteria for a valid identifier name. You can bind the parameter_value and open the cursor passing in the p_param value, though. [See here](https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm#BHCGEFCA) for an example of how to do that. — Boneist, Dec 19 '17 at 08:30

score 4 · Accepted Answer · answered Dec 19 '17 at 08:28

4

I must use bind variable in this function.

The solution is to use a placeholder in the template SQL ...

sql_stmt := sql_stmt || ' and parameter_value= :p1';

... then pass the actual value with the USING clause when you open the ref cursor.

Things are slightly tricky because you are executing different statements depending on whether the parameter is populated. So you need to do something like this instead:

sql_stmt := 'select parameter_name, parameter_value from ' || p_pr 
     || '.v_view where query_id =:p1';

IF p_param IS NOT NULL
THEN
    sql_stmt := sql_stmt || ' and parameter_value= :p2';
    OPEN rc FOR sql_stmt using p_qu_id, p_param;
else
   OPEN rc FOR sql_stmt using p_qu_id;
END IF;

LOOP

Note that p_pr - a schema name - cannot be replaced with a bind variable.

answered Dec 19 '17 at 08:28

APC

144,005
19
170
281

1

The conditional bind looks like one of these: http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html – William Robertson Dec 19 '17 at 09:01
@williamrobertson - yeah, that is another way of doing it. My feeling is, maybe we genuinely have two different queries. There's a difference between rewriting the code to use bind variables, and rewriting the code to execute only one query regardless of what parameters we pass. I find my version easier to understand, although it would get messy if the procedure passes lots of optional parameters. (Although that's an architectural issue as much as anything.) – APC Dec 19 '17 at 09:54

How can I use bind variable in plsql function

1 Answers1