-- Copyright © Oracle Corporation 1995.  All Rights Reserved.
-- This SQL module provides the SQL procedures needed by the
-- SQL$REPORT program.
-- The module illustrates how to use SQL module language to process a cursor
-- for preparing a report.  
-- In order to compile the generated Ada package  that lets you use this
-- module with an Ada program, you must supply the /PACKAGE_COMPILATION
-- qualifier on the module language command line.
-------------------------------------------------------------------------------
-- Header Information Section
-------------------------------------------------------------------------------
MODULE		SQL_REPORT            -- Module name
LANGUAGE	ADA		      -- Language of calling program
AUTHORIZATION	SQL_SAMPLE            -- Provides default db handle
PARAMETER COLONS                      -- Parameters are prefixed with colons
-------------------------------------------------------------------------------
-- DECLARE Statements Section
-------------------------------------------------------------------------------

-- Declaration of the database not using the CDD and using a logical name.
DECLARE ALIAS FILENAME 'PERSONNEL'   

-- Declaration of the cursor to be used for creating a record stream for the 
-- report
DECLARE REPORT_CURSOR CURSOR FOR
    SELECT J.EMPLOYEE_ID, J.LAST_NAME, J.FIRST_NAME, J.JOB_CODE,
           J.DEPARTMENT_CODE, S.SALARY_AMOUNT
    FROM CURRENT_JOB J, CURRENT_SALARY S
    WHERE J.EMPLOYEE_ID = S.EMPLOYEE_ID
    ORDER BY J.DEPARTMENT_CODE, J.JOB_CODE

-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------

-- This procedure uses the executable form for starting a transaction.
PROCEDURE SET_TRANSACTION
    SQLCODE;

    SET TRANSACTION READ ONLY;


-- This procedure opens the cursor that has been declared for the EMPLOYEES
-- table.
PROCEDURE OPEN_CURSOR
    SQLCODE;

    OPEN REPORT_CURSOR;


-- This procedure FETCHes the data from the opened cursor
PROCEDURE FETCH_REPORT_RECORD
    SQLCODE
    :P_EMPLOYEE_ID	CHAR(5)
    :P_LAST_NAME        CHAR(14)
    :P_FIRST_NAME	CHAR(10)
    :P_JOB_CODE          CHAR(4)
    :P_DEPARTMENT_CODE	CHAR(4)
    :P_SALARY_AMOUNT	REAL;

    FETCH REPORT_CURSOR INTO :P_EMPLOYEE_ID, :P_LAST_NAME, :P_FIRST_NAME,
                             :P_JOB_CODE, :P_DEPARTMENT_CODE, :P_SALARY_AMOUNT;


-- This procedure rolls back the transaction.

PROCEDURE ROLLBACK_TRANSACTION
    SQLCODE;

    ROLLBACK;