Example 1: Declaring and using a declared local temporary table
    in interactive SQL

    SQL> DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_INT
    cont>        (EMPLOYEE_ID ID_DOM,
    cont>         LAST_NAME CHAR(14),
    cont>         HOURS_WORKED INTEGER,
    cont>         HOURLY_SAL   INTEGER(2),
    cont>         WEEKLY_PAY   INTEGER(2))
    cont>        ON COMMIT PRESERVE ROWS;
    SQL> --
    SQL> INSERT INTO MODULE.PAYCHECK_DECL_INT
    cont>          (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY)
    cont>           SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED,
    cont>                  P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL
    cont>              FROM EMPLOYEES E, PAYROLL P
    cont>                  WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
    cont>                    AND P.WEEK_DATE = DATE '1995-08-01';
    100 rows inserted

    SQL> SELECT * FROM MODULE.PAYCHECK_DECL_INT LIMIT TO 2 ROWS;
     EMPLOYEE_ID   LAST_NAME        HOURS_WORKED       HOURLY_SAL       WEEKLY_PAY
     00165         Smith                      40            30.50          1220.00
     00166         Dietrich                   40            36.00          1440.00
    2 rows selected

    Example 2: Creating a stored module that contains the following:

    o  A declared local temporary table, MODULE.PAYCHECK_DECL_TAB

    o  A procedure, PAYCHECK_INS_DECL, that inserts weekly
       salary records into the declared local temporary table,
       MODULE.PAYCHECK_DECL_TAB

    o  A procedure, LOW_HOURS_DECL, that counts the number of
       employees with less than 40 hours worked

    The following example also demonstrates that you can access the
    declared local temporary table only from within the module.

    SQL> -- Create the module containing a declared temporary table.
    SQL> --
    SQL> CREATE MODULE PAYCHECK_DECL_MOD
    cont>   LANGUAGE SQL
    cont>   DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_TAB
    cont>          (EMPLOYEE_ID ID_DOM,
    cont>           LAST_NAME CHAR(14) ,
    cont>           HOURS_WORKED INTEGER, HOURLY_SAL INTEGER(2),
    cont>           WEEKLY_PAY   INTEGER(2))
    cont>           ON COMMIT PRESERVE ROWS
    cont> --
    cont> -- Create the procedure to insert rows.
    cont> --
    cont>   PROCEDURE PAYCHECK_INS_DECL;
    cont>   BEGIN
    cont>     INSERT INTO MODULE.PAYCHECK_DECL_TAB
    cont>          (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY)
    cont>           SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED,
    cont>                  P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL
    cont>                  FROM EMPLOYEES E, PAYROLL P
    cont>                  WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
    cont>                    AND P.WEEK_DATE = DATE '1995-08-01';
    cont>   END;
    cont> --
    cont> -- Create the procedure to count the low hours.
    cont> --
    cont>   PROCEDURE LOW_HOURS_DECL (:cnt INTEGER);
    cont>   BEGIN
    cont>     SELECT COUNT(*) INTO :cnt FROM MODULE.PAYCHECK_DECL_TAB
    cont>            WHERE HOURS_WORKED < 40;
    cont>   END;
    cont> END MODULE;
    SQL> --
    SQL> -- Call the procedure to insert the rows.
    SQL> --
    SQL> CALL PAYCHECK_INS_DECL();
    SQL> --
    SQL> -- Declare a variable and call the procedure to count records with
    SQL> -- low hours.
    SQL> --
    SQL> DECLARE :low_hr_cnt integer;
    SQL> CALL LOW_HOURS_DECL(:low_hr_cnt);
      LOW_HR_CNT
               2

    SQL> --
    SQL> -- Because the table is a declared local temporary table, you cannot
    SQL> -- access it from outside the stored module that contains it.
    SQL> --
    SQL> SELECT * FROM MODULE.PAYCHECK_DECL_TAB;
    %SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or
    environment

    Example 3: Disabling Compression for a Declard Local Temporary
    Table

    The following example shows a declared local temporary table
    that will not benefit from compression. The clause COMPRESSION IS
    DISABLED is used to reduce the CPU overhead for the table as well
    as preventing a possible row size increase because of compression
    notations.

    SQL> declare local temporary table module.scratch0
    cont>     (averages double precision)
    cont>     compression is DISABLED
    cont>     on commit PRESERVE rows
    cont> ;
    SQL>
    SQL> insert into module.scratch0
    cont>     select avg (char_length (a)) from module.scratch1;
    1 row inserted
    SQL>
    SQL> select * from module.scratch0;
                    AVERAGES
      2.100000000000000E+001