0 Replies Latest reply on Jun 6, 2014 11:14 AM by 791937

    AWR report - SQL ordered by Parse Calls

    791937

      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,