Hello Experts,
We are having an application for which business logic is mainly written in the PL SQL packages/procedures.
As per my understanding, queries directly written in the PL SQL procedure/function automatically assigns bind variable.
And it will be stored in the shared pool.
However in the AWR report, we are getting same query reported for parse call for each execution.
I would like to understand, how exactly Oracle assign bind variable automatically to queries (not using EXECUTE IMMEDIATE) used inside PL SQL block.
The query written in the below function is getting reported in the AWR report.
Code:
FUNCTION get_dep(txntyp IN VARCHAR2, txnsid IN VARCHAR2) RETURN VARCHAR2
IS
v_depmat VARCHAR2(512);
BEGIN
SELECT deptno INTO v_depmat FROM header A, detail B WHERE CATXNTYP = txntyp AND B.CATXNHDR = txnsid AND A.CATXNSID = B.CATXNHDR;
RETURN v_depmat;
EXCEPTION
WHEN OTHERS THEN
RETURN ' ';
END GETMATINT;
The above function is called in the select query like:
SELECT get_dep('10001', '30001') FROM DUAL;
Please suggest if I am missing anything in the function or I need to explicitly use BIND variable by converting above query into dynamic SQL.
Thanks,