/*
 * CompileError.sql
 * Chapter 3, Oracle10g PL/SQL Programming
 * by Ron Hardman, Mike McLaughlin, Scott Urman
 *
 * This script demonstrates the compile time warnings
 *  generated by Oracle
 */

exec clean_schema.trigs
exec clean_schema.procs
exec clean_schema.tables

SET SERVEROUTPUT ON PAGES 9999

PROMPT
PROMPT ** Create a procedure that fails to compile **
PROMPT

CREATE OR REPLACE PROCEDURE compile_error
   AS
     v_timestamp timestamp;
BEGIN
   SELECT systimestamp
   INTO v_timestamp
   FROM duall;

   DBMS_OUTPUT.PUT_LINE(v_timestamp);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

PROMPT 
PROMPT ** Show the reason the procedure is failing to compile **
PROMPT

SHOW ERRORS

PROMPT
PROMPT ** Show the code and line number to help troubleshoot **
PROMPT

SELECT line||' '||text PROCEDURE
FROM user_source
WHERE name = 'COMPILEERROR';

PROMPT
PROMPT ** Show the procedure is marked invalid **
PROMPT
COL object_name FORMAT A15
COL status FORMAT A10
SELECT object_name, status
  FROM user_objects
  WHERE object_name = 'COMPILEERROR';

PROMPT
PROMPT ** Recreate the compileerror procedure with the corrected
PROMPT **  table name
PROMPT

CREATE OR REPLACE PROCEDURE CompileError
   AS
     v_timestamp timestamp;
BEGIN
   SELECT systimestamp
   INTO v_timestamp
   FROM dual;

   DBMS_OUTPUT.PUT_LINE(v_timestamp);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

exec compileerror