# 
# ORACLE Performance Tuning Chapter 16 pp. 394-395
# Combining SELECT Statements
    DECLARE                
        CURSOR  C1 (E_NO  NUMBER)  IS 
        SELECT  EMP_NAME, SALARY, GRADE
            FROM  EMP
            WHERE  EMP_NO = E_NO;
    BEGIN
        OPEN  C1  (342);
        FETCH C1  INTO  ..., ..., ...;

        OPEN   C1  (291);
        FETCH  C1 INTO ..., ..., ...;
        CLOSE  C1;
    END;
# 
# ORACLE Performance Tuning Chapter 16 p. 395
#
SELECT  A.EMP_NAME, A.SALARY, A.GRADE,
        B.EMP_NAME, B.SALARY, B.GRADE
    FROM   EMP A,
           EMP B
    WHERE  A.EMP_NO = 0342
    AND    B.EMP_NO = 0291 ;
#
# ORACLE Performance Tuning Chapter 16 p. 396
# SQL*Forms Base Table Views
    CREATE VIEW  EMP_VIEW AS
    SELECT  E.EMP_NO, E.EMP_NAME, E.EMP_DEPT,
        E.MGR_EMP_NO, E.EMP_CAT, D.DEPT_DESC,
        M.EMP_NAME MGR_NAME,  C.CAT_DESC
        FROM  CATEGORY   C,
              DEPARTMENT  D,
              EMP  M,
              EMP  E
        WHERE E.EMP_NO      >  0    
        AND   E.EMP_DEPT    =  D.DEPT_CODE  (+)
        AND   E.MGR_EMP_NO  =  M.EMP_NO  (+)
        AND   E.EMP_CAT     =  C.CAT_CODE  (+)
                                 
    BASE TABLE  EMP_VIEW            
    DATABASE FIELDS  EMP_NO
                     EMP_NAME
                     EMP_DEPT  
                     DEPT_DESC
                     MGR_EMP_NO
                     MGR_NAME
                     EMP_CAT
                     CAT_DESC
# 
# ORACLE Performance Tuning Chapter 16 pp. 406-407
# Local Cache
   TABLE : CODE_TABLE      CODE_TYPE    CHAR(20)
                           CODE_CODE    CHAR(20)
                           CODE_DESC    CHAR(60)

   GLOBAL.CACHE_ACCESS     'Y' :  ONLY SEARCH LOCAL CACHE 
                           'N' :  ONLY SEARCH DATABASE.
                           'X' :  SEARCH LOCAL CACHE, IF NOT FOUND,
                                      SEARCH DATABASE.

  TRIGGER PRE-FORM :  USER_EXIT ('LCACHE LOAD');

  TRIGGER KEY-F10  :  USER_EXIT ('LCACHE LOAD')

  PROCEDURE LOOKUP_CODE (TYP IN       CHAR,
                       CDE IN       CHAR, 
                       DSC IN OUT   CHAR ) IS
      DECLARE 
      CURSOR CT IS SELECT    CODE_DESC
                       FROM  CODE_TABLE
                       WHERE CODE_TYPE = TYP
                       AND   CODE_CODE = CDE;
      BEGIN
      
          IF  :GLOBAL.CACHE_ACCESS = 'Y'
          OR  :GLOBAL.CACHE_ACCESS = 'X' THEN
        
              USER_EXIT ('LCACHE ' || TYP ||' '|| CDE);
             
              IF  :GLOBAL.LCACHE_DESC IS NULL
              AND :GLOBAL.CACHE_ACCESS = 'Y' THEN
                  MESSAGE ('INVALID CODE ENTERED  . . . ');
                  RAISE FORM_TRIGGER_FAILURE;
              ELSE
              DSC := :GLOBAL.LCACHE_DESC;
              END IF;
              ERASE ('GLOBAL.LCACHE_DESC');
          END IF;
         
          IF :GLOBAL.CACHE_ACCESS = 'N'
          OR ( :GLOBAL.CACHE_ACCESS = 'X' AND DSC IS NULL  ) THEN
            
              OPEN  CT;
              FETCH CT INTO DSC;
            
              IF CT%NOTFOUND THEN
                  CLOSE CT;
                  MESSAGE ('INVALID CODE ENTERED ...');
                  RAISE FORM_TRIGGER_FAILURE;
              END IF;
             
              CLOSE CT;
          END IF;
      END;