Forum Stats

  • 3,784,130 Users
  • 2,254,896 Discussions
  • 7,880,702 Comments

Discussions

AWR report - SQL ordered by Parse Calls

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,

This discussion has been closed.