Welcome to Oracle Communities
Register and Participate in Oracle's online communities. Learn from thousands of experts, get answers to your questions and share knowledge with peers.
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.
FUNCTION get_dep(txntyp IN VARCHAR2, txnsid IN VARCHAR2) RETURN VARCHAR2
SELECT deptno INTO v_depmat FROM header A, detail B WHERE CATXNTYP = txntyp AND B.CATXNHDR = txnsid AND A.CATXNSID = B.CATXNHDR;
WHEN OTHERS THEN
RETURN ' ';
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.