-- Copyright (c) 1994 by Oracle Corporation 
-- SIMPLE :
--      1)      Logon to the ORACLE database
--      2)      Prompt user for a name that exists
--                in the EMP table
--      3)      Do a simple SQL join that finds the
--                employee's department and location


with text_io, integer_text_io, sql_standard; 

-- Note:  the precompiler "with's" the other required ORACLE 
--        packages. 

procedure SIMPLE is 

use text_io, integer_text_io; 

-- declare host and program variables 

ORACLE_ID   :   constant string := "SCOTT/TIGER"; 
ENAME       :   string(1..20); 
ENAME_LEN   :   integer; 
DEPT_NAME   :   string(1..14); 
LOCATION    :   string(1..13); 
EXEC SQL BEGIN DECLARE SECTION;
SQLCODE     :   sql_standard.sqlcode_type; -- for ANSI mode 
SQLSTATE    :   sql_standard.sqlstate_type; -- ANSI mode 
EXEC SQL END DECLARE SECTION;
SQL_ERROR   :   exception; 
SQL_WARNING :   exception; 


--  Check to see if the last database 
--  operation returned any rows. 
--  (See Chapter 5 on error handling.) 
 

function EMPLOYEE_EXISTS return boolean is 
begin 
  return (not(ORACLE.ERROR.IF_NOT_FOUND)); 
end EMPLOYEE_EXISTS; 

begin                           
-- Direct the precompiler to insert "if" logic that 
-- checks the ORACLE return code and raises an exception 
-- if needed. 

   EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 

-- Check for warnings, such as data truncation, also. 

   EXEC SQL WHENEVER SQLWARNING raise SQL_WARNING; 

-- Connect to ORACLE 
   EXEC SQL CONNECT :ORACLE_ID;
   NEW_LINE; 
   PUT_LINE("Connected to ORACLE as " & ORACLE_ID); 
   NEW_LINE; 
   PUT_LINE("*** ORACLE DEMO #1 ***"); 
   NEW_LINE; 

   loop 
       PUT("Enter employee last name (CR to exit): "); 
       GET_LINE(ENAME, ENAME_LEN); 
       exit when ENAME_LEN = 0; 

-- SELECT statements that return one row can use a 
-- simple SELECT statement.  Otherwise, a cursor must be 
-- declared for the SELECT, and a FETCH statement is used. 

       EXEC SQL SELECT INITCAP(loc), INITCAP(dname) 
         INTO :LOCATION, :DEPT_NAME 
         FROM emp, dept 
         WHERE dept.deptno = emp.deptno 
         AND EMP.ENAME = 
         upper(:ENAME(1..ENAME_LEN)); 

       if EMPLOYEE_EXISTS then 
         NEW_LINE; 
         PUT("Employee "); 
         PUT(ENAME(1..ENAME_LEN)); 
         PUT(" works for department " & DEPT_NAME); 
         PUT(" in " & LOCATION); 
       else 
         PUT_LINE("Sorry, no such employee (try ALLEN or JONES)"); 
         NEW_LINE; 

-- The following lines illustrate the use of SQLSTATE and SQLCODE
         PUT("The SQLSTATE error code is: ");
         PUT_LINE(string(sqlstate));
         PUT("The SQLCODE error code is: ");
         PUT(integer(sqlcode),1); 
       end if; 

       NEW_LINE; NEW_LINE; 
   end loop; 

   NEW_LINE; 
   PUT_LINE("Good bye."); 
 
-- Disconnect from the database. 
   EXEC SQL COMMIT RELEASE; 

exception 
-- Turn off error checking, since we do not want 
-- to raise an exception when logging out under 
-- any circumstance. 
 

   EXEC SQL WHENEVER SQLERROR CONTINUE; 
   EXEC SQL WHENEVER SQLWARNING CONTINUE; 

   when SQL_ERROR => 
    PUT_LINE(" ** ORACLE ERROR OCCURRED **"); 
    NEW_LINE; 
    PUT_LINE(ORACLE.ERROR.MESSAGE); 
    EXEC SQL ROLLBACK RELEASE; 

   when SQL_WARNING => 
    PUT_LINE(" ** ORACLE WARNING OCCURRED **"); 
    NEW_LINE; 
    EXEC SQL ROLLBACK RELEASE; 

end SIMPLE; 
