Lab 12.1 Exercise AnswersA1: | Answer: Procedure created . The procedure named Discount was compiled into p-code and stored in the database for later execution. Note if you saw an errorthis is due to a typing mistake. Recheck the code against the example in the book and recompile. |
| A1: | Answer: SQL> EXECUTE Discount 5% discount has been given to 25 Adv. Word Perfect .... (through each course with an enrollment over 8) PL/SQL procedure successfully completed. | ||
| A2: | Answer: There is no COMMIT in this procedure, which means the procedure will not update the database. A COMMIT needs to be issued after the procedure is run, if you want the changes to be made. Alternatively, you can enter a COMMIT either before or after the END LOOP. If you put the COMMIT before the END LOOP, then you are committing changes after every loop. If you put the COMMIT after the END LOOP, then the changes will not be committed until after the procedure is near completion. It is wiser to take the second option. This way you are better prepared for handling errors.
|
Answer:
SELECT object_name, object_type, status FROM user_objects WHERE object_name = 'DISCOUNT';
The result is:
OBJECT_NAME OBJECT_TYPE STATUS -------------------- --------------- ------ DISCOUNT PROCEDURE VALID
The status indicates where the procedure was complied successfully. An invalid procedure cannot be executed.
Answer:
SQL> column text format a70 SELECT TO_CHAR(line, 99)||'>', text FROM user_source WHERE name = 'DISCOUNT'
A procedure can become invalid if the table it is based on is deleted or changed. You can recompile an invalid procedure with the command alter procedure procedure_name compile |