How To Avoid duplicate SQL (multiple parsing) on Oracle

It is a common mistake to write SQL statements like:

  //Assuming rCURSOR is declared as REF CURSOR
 PROCEDURE Q_GETRESULTS(sPARAM1 VARCHAR2, iPARAM2 NUMBER, dPARAM3 DATE, cCursor OUT rCURSOR) IS
   sSQL VARCHAR2(500);
   BEGIN
   sSQL :=         '  SELECT COL1, COL2, COL3 ';
   sSQL := sSQL || '    FROM TABLE1'
   sSQL := sSQL || '   WHERE COL1 = ''' || sPARAM1 || '''';
   sSQL := sSQL || '     AND COL2 = ' || iPARAM2;
   sSQL := sSQL || '     AND COL3 = ''' || dPARAM2 || '''';
   OPEN cCURSOR FOR sSQL
 END Q_GETRESULTS;

What happens when the procedure runs; is that it parsed for the parameters and then it is executed. When one of the parameters changes, it is a different SQL string, hence hard parsed and executed again. Hard parsing is a very CPU intensive process, which also tries to obtain latches on important shared memory area.
This is a bad habit and should be avoided at all costs. It is also subject to SQL Injection.

So, how to avoid it then? There are a few methods for it.

If all the parameters are required from the client application, then a sweet method is to use;

 sSQL :=         'SELECT COL1, COL2, COL3 ';
 sSQL := sSQL || '  FROM TABLE1'
 sSQL := sSQL || ' WHERE COL1 = :B1';
 sSQL := sSQL || '   AND COL2 = :B2';
 sSQL := sSQL || '   AND COL3 = :B2';

 OPEN cCURSOR FOR sSQL USING sPARAM1, iPARAM2, dPARAM3;

If the one or more of the parameters of the procedure is optional (is NULL) then SYS_CONTEXT is our friend. This is a function that returns a string. So we can use it anywhere.
It requires "CREATE ANY CONTEXT" privilege.

 CREATE OR REPLACE CONTEXT CTX_MYCONTEXT USING Q_GETRESULTS;

 PROCEDURE Q_GETRESULTS(sPARAM1 VARCHAR2, iPARAM2 NUMBER, dPARAM3 DATE, cCursor OUT rCURSOR) IS
 sSQL VARCHAR2(500);
 BEGIN
   sSQL :=         '  SELECT COL1, COL2, COL3 ';
   sSQL := sSQL || '    FROM TABLE1'
   sSQL := sSQL || '   WHERE 1=1';
   IF sPARAM1 IS NOT NULL THEN
     DBMS_SESSION.SET_CONTEXT('CTX_MYCONTEXT', 'sPARAM1_VAL', sPARAM1);
    sSQL := sSQL || '   AND COL1 = SYS_CONTEXT(''CTX_MYCONTEXT'', ''sPARAM1_VAL'')';
   END IF;

   IF iPARAM2 IS NOT NULL THEN
     DBMS_SESSION.SET_CONTEXT('CTX_MYCONTEXT', 'iPARAM2_VAL', iPARAM2);
     sSQL := sSQL || '   AND COL2 = TO_NUMBER(SYS_CONTEXT(''CTX_MYCONTEXT'',''iPARAM2_VAL''))';
   END IF;

   IF dPARAM3 IS NOT NULL THEN
     DBMS_SESSION.SET_CONTEXT('CTX_MYCONTEXT', 'dPARAM3_VAL', TO_CHAR(dPARAM3, 'DDMMYYYHH24MISS');
     sSQL := sSQL || '  AND COL3 = TO_DATE(SYS_CONTEXT(''CTX_MYCONTEXT'',''dPARAM3_VAL''), ''DDMMYYYHH24MISS'')';
   END IF;
   OPEN cCURSOR FOR sSQL;
 END Q_GETRESULTS;

This is an elegant and very easy to implement solution. Existing procedures can be converted to this method very easily.
More info about this can be found HERE!. Tom Kyte explains it briefly.

This ensures that SQL is parsed once and executed multiple times with the same plan. It increases performance more than expected.