What did you see on your screen? Explain what happened .
A1:
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 error ”this is due to a typing mistake. Recheck the code against the example in the book and recompile.
b)
Execute the Discount procedure. How did you accomplish this? What are the results that you see in your SQL*Plus screen?
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.
c)
The script did not contain a COMMIT. Discuss the issues involved with placing a COMMIT in the procedure and indicate where the COMMIT could be placed.
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.
If you receive an error, then type the command:
Show error
You can also add to the command:
L start_line_number end_line_number
to see a portion of the code in order to isolate errors.
12.1.2 Answers
a)
Write the select statement to get pertinent information from the USER_OBJECTS view about the Discount procedure you just wrote. Run the query and describe the results.
A1:
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.
b)
Write the SELECT statement to display the source code from the USER_SOURCE view for the Discount procedure.
A2:
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
![Oracle PL[s]SQL by Example](/icons/blank_book.jpg "Oracle PL[s]SQL by Example"){kind=icon}