SECTION 8 LESSON 1
1. PL/SQL subprograms, unlike anonymous blocks, are compiled each time they are executed. True or False? Mark for Review (1) Points True False (*) Incorrect. Refer to Section 8 Lesson 1. 2. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. Which step is missing? A. B. C. D. E.
Type the the procedure procedure code in in the SQL Commands Commands window window Click on the "Save" button button and save save the the procedure procedure code code Retrieve Retrieve the saved saved code from "Saved "Saved SQL" SQL" in SQL Commands Commands Modify Modify the code code in the SQL SQL Commands Commands window window Execut Executee the code code to re-cre re-create ate the the procedur proceduree
Mark for Review (1) Points Enter parameters and data type Execute the procedure from USER_SOURCE data dictionary view Execute the code to create the procedure (*) Invoke the procedure from an anonymous block Correct 3. Procedures are generally used to perform what? (1) Points A SELECT statement
Mark for Review
An action (*) A return of values All of the above None of the above Correct 4. Which of the following are characteristics of anonymous PL/SQL blocks but not PL/SQL subprograms? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Can take parameters Are stored in the database Can begin with the keyword DECLARE (*) Are unnamed (*) Are compiled every time they are executed (*)
Incorrect. Refer to Section 8 Lesson 1. 5. Subprograms and anonymous blocks can be called by other applications. True or False? for Review (1) Points True
Mark
False (*) Incorrect. Refer to Section 8 Lesson 1. 6. A nested subprogram can be called from the main procedure or from the calling environment. True or False? (1) Points True
Mark for Review
False (*) Correct 7. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose two.) (1) Points BEGIN (*)
Mark for Review (Choose all correct answers)
REPLACE EXCEPTION DECLARE END (*) Correct 8. A programmer wants to create a PL/SQL procedure named MY_PROC. What will happen when the following code is executed? CREATE OR REPLACE PROCEDURE my_proc IS v_empid employees.empid%TYPE; BEGIN SELECT employee_id INTO v_empid FROM employees WHERE region_id = 999; DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary); Mark for Review (1) Points The statement will raise a NO_DATA_FOUND exception because region_id 999 does not exist. The statement will fail because the last line of code should be END emp_proc; (*) The statement will fail because you cannot declare variables such as v_empid inside a procedure. Incorrect. Refer to Section 8 Lesson 1. 9. Which of the following are benefits of using PL/SQL subprograms rather than anonymous blocks? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Better data security (*)
Code reuse (*) Stored externally Easier code maintenance (*) Do not need to define exceptions Incorrect. Refer to Section 8 Lesson 1. 10. A stored PL/SQL procedure can be invoked from which of the following? A. B. C. D.
A PL/SQL anonymous block A calling application A SELECT statement Another PL/SQL procedure
Mark for Review (1) Points A only A and B A and C A, B and D (*) B and C Incorrect. Refer to Section 8 Lesson 1. 11. A stored procedure add_dept may be invoked by the following command in Application Express. True or False? BEGIN add_dept; END; Mark for Review (1) Points True (*) False Incorrect. Refer to Section 8 Lesson 1. 12. When modifying procedure code, the procedure must be re-executed to validate and store it in the database. True or False? (1) Points True (*)
Mark for Review
False Correct 13. Why will the following procedure fail? CREATE OR REPLACE PROCEDURE mainproc ... IS
PROCEDURE subproc (...) IS BEGIN ... BEGIN ... subproc (...); ... END; Mark for Review (1) Points Procedure main proc must use the keyword AS not IS Procedure mainproc does not need the keyword BEGIN Procedure subproc does not need the keyword BEGIN Procedure subproc does not have an END; statement (*) Incorrect. Refer to Section 8 Lesson 1.
LESSON 2
1. You want to create a procedure which accepts a single parameter. The parameter is a number with a maximum value of 9999.99. Which of the following is a valid declaration for this parameter? Review (1) Points (v_num NUMBER(6,2))
Mark for
(v_num NUMBER) (*) (v_num) (v_num NUMBER(4,2)) Correct 2. What is the correct syntax to create procedure MYPROC that accepts two number parameters X and Y? Mark for Review (1) Points CREATE PROCEDURE myproc (x NUMBER, y N UMBER) IS ... (*) CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS ... CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) ... CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) ... Incorrect. Refer to Section 8 Lesson 2. 3. What is the purpose of using parameters with stored procedures? (1) Points They prevent the procedure from modifying data in the database.
Mark for Review
They allow values to be passed between the calling environment and the procedure. (*)
They count the number of exceptions raised by the procedure. They speed up the execution of the procedure. Correct 4. Procedure TESTPROC accepts one parameter P1, whose value is up to 1000 characters in length. Which one of the following declares this parameter correctly? (1) Points CREATE PROCEDURE testproc (p1 VARCHAR2(100) ) IS BEGIN .... CREATE PROCEDURE testproc IS p1 VARCHAR2(100); BEGIN .... CREATE PROCEDURE testproc DECLARE p1 VARCHAR2(100); BEGIN .... CREATE PROCEDURE testproc p1 VARCHAR2 IS BEGIN .... CREATE PROCEDURE testproc (p1 VARCHAR2) IS BEGIN .... (*) Incorrect. Refer to Section 8 Lesson 2. 5. Procedure SUBPROC was created as: CREATE PROCEDURE subproc (p_param VARCHAR2) IS BEGIN ... You invoke the procedure by: DECLARE v_param VARCHAR2(20) := 'Smith'; BEGIN subproc(v_param); END; Which of the following is the actual parameter? Mark for Review (1) Points p_param
Mark for Review
v_param (*) 'Smith' None of the above. Incorrect. Refer to Section 8 Lesson 2. 6. Which of the following best describes the difference between a parameter and an argument? Mark for Review (1) Points They are both names of variables. A parameter is passed into the procedure, while an argument is passed out of the procedure A parameter is the name of a variable, while an argument is the datatype of that variable A parameter is the name of a variable that is passed into or out of a procedure, while an argument is the value of that variable (*) There is no difference, parameters and arguments are the same thing Correct 7. Which one of the following statements about formal and actual parameters is true?
Mark for
Review (1) Points Formal and actual parameters must have the same name. Formal and actual parameters must have different names. A formal parameter is declared within the called procedure, while an actual parameter is declared in the calling environment. (*) An actual parameter is declared within the called procedure. Incorrect. Refer to Section 8 Lesson 2. 8. Which of the following can be used as an argument for a procedure parameter?
Mark for
Review (1) Points The name of a variable. A literal value. An expression. All of the above. (*) None of the above. Correct 9. A procedure has been created as: CREATE PROCEDURE myproc (p_left NUMBER, p_right NUMBER) IS BEGIN .... You want to call the procedure from an anonymous block. Which of the following calls is valid? Mark for Review (1) Points
myproc(p_left, p_right); myproc(v_left, v_right); myproc(v_left, 30); All of the above. (*) Incorrect. Refer to Section 8 Lesson 2.
LESSON 3
1. A procedure is invoked by this command: myproc('Smith',100,5000); What is the method of passing parameters used here? Mark for Review (1) Points Positional (*) Named A combination of positional and named. None of the above Correct 2. If you don't specify a mode for a parameter, what is the default mode? (1) Points OUT
Mark for Review
IN (*) COPY DEFAULT R(ead) Correct 3. Procedure NUMPROC has been created as: CREATE PROCEDURE numproc (x NUMBER, y NUMBER := 100, z NUMBER) IS BEGIN .... You want to call the procedure, passing arguments of 10 for X and 20 for Z. Which one of the following calls is correct? Mark for Review (1) Points numproc(10,,20); numproc(x=10,z=20);
numproc(10,z=>20); (*) numproc(x=>10,20); Correct 4. A procedure is invoked by this command: myproc('Smith',salary=>5000); What is the method of passing parameters used here? Mark for Review (1) Points Positional Named A combination of positional and named (*) None of the above Correct 5. Three IN parameters for procedure ADD_EMPLOYEE are defined as: (p_name VARCHAR2 , p_salary NUMBER := 1000, p_hired DATE DEFAULT SYSDATE) The procedure is invoked by: add_employee('Jones'); What is the value of P_SALARY when the procedure starts to execute? Mark for Review (1) Points NULL 1000 (*) The procedure will not compile because P_SALARY should have been coded as DEFAULT 1000 The call will fail because P_SALARY is a required parameter Correct 6. Which kind of parameters cannot have a DEFAULT value? (1) Points OUT (*) IN CONSTANT R(ead) W(rite) Correct
Mark for Review
7. Which of the following statements about IN OUT parameters is true? (Choose two.)
Mark for
Review (1) Points (Choose all correct answers) The data type for the parameter must be VARCHAR2. The parameter value passed into the subprogram is always returned unchanged to the calling environment. The parameter value can be returned as the original unchanged value. (*) The parameter value can be returned as a new value that is set within the procedure. (*) Correct 8. What are the three parameter modes for procedures? (1) Points IN, OUT, IN OUT (*)
Mark for Review
R(ead), W(rite), A(ppend) CONSTANT, VARIABLE, DEFAULT COPY, NOCOPY, REF Correct 9. When creating a procedure, where in the code must the parameters be listed? (1) Points After the procedure name. (*)
Mark for Review
After the keyword IS or AS. Before the procedure name. After the keyword PROCEDURE. Correct 10. The following procedure has been created: CREATE OR REPLACE PROCEDURE myproc (p_p1 NUMBER, p_p2 VARCHAR2) IS BEGIN ... Which one of the following calls to the procedure will NOT work? Mark for Review (1) Points myproc(80, 'Smith'); myproc(p_p1 => 80, 'Smith'); (*) myproc(80, p_p2 => 'Smith'); myproc(p_p1 => 80, p_p2 => 'Smith'); Correct 11. What will happen when the following procedure is called as format_phone (8005551234)? CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) IS BEGIN
p_phone_no := SUBSTR(p_phone_no,1,3) || '.' || SUBSTR(p_phone_no,4,3) || '.' || SUBSTR(p_phone_no,7); END format_phone; Mark for Review (1) Points The phone number 800.555.1234 is printed to the screen. The phone number (800) 555-1234 is printed to the screen. The phone number 800.555.1234 is placed into the p_phone_no variable. (*) The procedure does not execute because the input variable is not properly declared. Incorrect. Refer to Section 8 Lesson 3.
