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.

Answer 1

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.