-- employee table
--
CREATE TABLE EMP ( 
  EMPNO    NUMBER,
  ENAME    VARCHAR2(100),
  JOB      VARCHAR2(9),
  HIREDATE DATE,
  DEPTNO   NUMBER
);

-- get employee number
--
CREATE OR REPLACE PROCEDURE EMP_QUERY 
IS 
BEGIN 
   htp.htmlOpen; 
   htp.headOpen; 
   htp.title('EMPLOYEE SEARCH UTILITY'); 
   htp.headClose; 
   htp.bodyOpen; 
   htp.line; 
   htp.header(1,'EMPLOYEE SEARCH UTILITY'); 
   htp.paragraph; 
   htp.formopen('EMPSEARCH'); 
   -- EMPSEARCH is the procedure that the employee number will be passed to 
   htp.line; 
   htp.print('This application will search the employee database for a specific record.'); 
   htp.nl; 
   htp.em('Enter the 4 digit employee number that you would like to search for:'); 
   htp.nl; 
   htp.formText('EMPNUMBER',10,30); 
   -- the value EMPNUMBER gets passed to the EMPSEARCH procedure 
   htp.formsubmit(NULL,'SEARCH'); 
   htp.formclose; 
   htp.paragraph; 
   htp.paragraph; 
   htp.line; 
   htp.address('(c)1998 QSI'); 
   htp.bodyClose; 
   htp.htmlClose; 
END; 

-- find employee by number
--
CREATE OR REPLACE PROCEDURE EMPSEARCH (EMPNUMBER IN NUMBER) 
IS 
-- local variables
   EMPLOYEE_NAME      VARCHAR2(100); 
   JOB_DESCRIPTION    VARCHAR2(9); 
   DATE_HIRED         DATE; 
   DEPARTMENT_NO      NUMBER; 
BEGIN 
   SELECT ENAME, JOB, HIREDATE, DEPTNO 
   INTO EMPLOYEE_NAME, JOB_DESCRIPTION, DATE_HIRED, DEPARTMENT_NO 
      FROM EMP 
      WHERE EMPNO = EMPNUMBER; 
   -- send output to HTML page 
   htp.htmlOpen; 
   htp.headOpen; 
   htp.title('EMPLOYEE SEARCH UTILITY'); 
   htp.headClose; 
   htp.bodyOpen; 
   htp.line; 
   htp.header(1,'EMPLOYEE SEARCH UTILITY'); 
   htp.line; 
   -- display the employee information 
   htp.strong('Information found for employee number: ' || EMPNUMBER); 
   htp.nl; 
   htp.print('Employee''s name: ' || employee_name); 
   htp.nl; 
   htp.print('Job Description: ' || job_description); 
   htp.nl; 
   htp.print('Hire Date: '||date_hired); 
   htp.nl; 
   htp.print('Department number: ' || department_no); 
   htp.nl; 
   htp.fontOpen ('blue'); 
   htp.print('Press the ''Back'' button on your browser to check another key'); 
   htp.fontClose; 
   htp.nl; 
   htp.line; 
   htp.address('(c)1998 QSI'); 
   htp.bodyClose; 
   htp.htmlClose; 
-- trap no data found error and display warning page 
   EXCEPTION 
      WHEN NO_DATA_FOUND THEN 
      htp.htmlOpen; 
      htp.headOpen; 
      htp.title('EMPLOYEE SEARCH UTILITY'); 
      htp.headClose; 
      htp.bodyOpen; 
      htp.line; 
      htp.header(1,'EMPLOYEE SEARCH UTILITY'); 
      htp.line; 
      htp.fontOpen('red'); 
      htp.em('WARNING: '); 
      htp.fontClose; 
      htp.nl; 
      htp.print('No information found for employee number: ' || EMPNUMBER); 
      htp.nl; 
      htp.print('Please check the number and try again'); 
      htp.nl; 
      htp.fontOpen ('blue'); 
      htp.print('Press the ''Back'' button on your browser to check another key'); 
      htp.fontClose; 
      htp.nl; 
      htp.line; 
      htp.address(' (c)1998 QSI'); 
      htp.bodyClose; 
      htp.htmlClose; 
END;