♦A SELECT statement returns more than one row
♦Any other kind of exception that can occur within the block
♦All of the above (*)
♦None of the above
2. Examine the following code. Why does the exception handler not follow good practice guidelines?
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 999;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An error occurred’);
END;
♦You should not use DBMS_OUTPUT.PUT_LINE in an exception handler.
♦employee_id 999 does not exist in the employees table.
♦The exception handler should test for the named exception NO_DATA_FOUND. (*)
♦The exception handler should COMMIT the transaction.
3. Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?
♦Protects the database from errors
♦Code is more readable because error-handling routines can be written in the same block in which the error occurred
♦Prevents errors from occurring (*)
♦Avoids costly and time-consuming correction of mistakes
4. Which of the following are good practice guidelines for exception handling? (Choose three.)
♦Test your code with different combinations of data to see what potential errors can happen. (*)
♦Use an exception handler whenever there is any possibility of an error occurring. (*)
♦Include a WHEN OTHERS handler as the first handler in the exception section.
♦Allow exceptions to propagate back to the calling environment.
♦Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. (*)
5. Which of the following best describes a predefined Oracle Server error?
♦Has a standard Oracle error number but must be named by the PL/SQL programmer
♦Is not raised automatically but must be declared and raised explicitly by the PL/SQL programmer
♦Has a standard Oracle error number and a standard name which can be referenced in the EXCEPTION section (*)
♦Is associated with an Oracle error number using PRAGMA EXCEPTION_INIT
6. Which of the following are examples of predefined Oracle Server errors? (Choose three.)
♦TOO_MANY_ROWS (*)
♦NO_DATA_FOUND (*)
♦OTHERS
♦ZERO_DIVIDE (*)
♦E_INSERT_EXCEP
7. The following exception handler will successfully insert the Oracle error number and error message into a log table whenever an Oracle Server error occurs. True or False?
WHEN OTHERS THEN
INSERT INTO err_log_table (num_col, char_col)
VALUES (SQLCODE, SQLERRM);
END;
(Assume that err_log_table has been created with suitable columns and datatypes.)
♦True
♦False (*)
8. Which kinds of exceptions are raised implicitly (i.e., automatically)? (Choose two.)
♦Predefined Oracle Server errors such as NO_DATA_FOUND (*)
♦User-defined errors
♦All errors
♦Non-predefined Oracle Server errors such as ORA-01400 (*)
9. An attempt to update an employee’s salary to a negative value will violate a check constraint and raise an ORA-02290 exception. Which of the following is a correct definition of a handler for this exception?
♦DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(-02290,e_sal_excep);
♦DECLARE
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
e_sal_excep EXCEPTION;
♦DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,-02290);
(*)
♦DECLARE
e_sal_excep EXCEPTION;
PRAGMA_EXCEPTION_INIT(e_sal_exception,-02290);
♦DECLARE
e_sal_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_sal_excep,02290);
10. An attempt to insert a null value into a NOT NULL table column raises an ORA-01400 exception. How can you code an exception handler to trap this exception?
♦Test for WHEN ORA-1400 in the exception section.
♦Declare a variable e_null_excep of type EXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section. (*)
♦Declare a variable e_null_excep of type VARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section.
♦Declare a variable as follows: e_null_excep EXCEPTION := -01400; Then test for WHEN e_null_excep in the exception section.
DECLARE
e_constraint_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_constraint_violation, -2292);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Inner block message’);
END;
EXCEPTION
WHEN e_constraint_violation THEN
DBMS_OUTPUT.PUT_LINE(‘Outer block message’);
END;
♦Inner block message’ will be displayed.
♦The code will fail because the exception is declared in the inner block but is referenced in the outer block. (*)
♦Outer block message’ will be displayed.
♦The code will fail because line 4 should read: PRAGMA EXCEPTION_INIT(-2292, e_constraint_violation);
12. Using nested blocks, when is it necessary to label the outer block?.
♦You must always label the outer block.
♦You must always label both blocks.
♦You must label the outer block when two variables with the same name are declared, one in each block.
♦You must label the outer block when two variables with the same name are declared and you need to reference the outer block’s variable within the inner block. (*)
♦Block labels are just comments and are therefore recommended but never needed.
13. What will be displayed when the following code is executed?
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 10;
DECLARE
v_myvar NUMBER := 200;
BEGIN
outer.v_myvar := 20;
v_myvar := v_myvar / 0; — this raises a ZERO_DIVIDE error
outer.v_myvar := 30;
END;
v_myvar := 40;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE(v_myvar);
END;
♦10
♦20 (*)
♦30
♦40
♦200
14. Using two nested blocks, a TOO_MANY_ROWS exception is raised within the inner block. Which of the following exception handlers will successfully handle the exception?
♦WHEN TOO_MANY_ROWS in the inner block
♦WHEN TOO_MANY_ROWS in either block
♦WHEN OTHERS in either block
♦WHEN OTHERS in the inner block
♦All of the above (*)
15. Which of the following will display the value ‘Smith’?
♦<<outer>>
DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
♦<<outer>>
DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(<<outer>>.v_name);
END;
END;
♦<<outer>>
DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(outer.v_name);
END;
END;
(*)
♦<<outer>>
DECLARE
v_name VARCHAR2(10) := ‘Smith’;
BEGIN
<<inner>>
DECLARE
v_name VARCHAR2(10) := ‘Jones’;
BEGIN
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
16. The following code will execute correctly. True or False?
v_myvar1 NUMBER;
BEGIN
DECLARE
v_myvar2 NUMBER;
BEGIN
v_myvar1 := 100;
END;
v_myvar2 := 100; v END;
♦True
♦False (*)
17. There are no employees in department_id 99. What output will be displayed when the following code is executed?
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM employees WHERE department_id = 99;
IF v_count = 0 THEN
RAISE NO_DATA_FOUND;
DBMS_OUTPUT.PUT_LINE(‘No employees found’);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Department 99 is empty’);
END;
♦No employees found
♦No employees found Department 99 is empty
♦Department 99 is empty (*)
♦The block will fail because you cannot explicitly RAISE a predefined Oracle Server error such as NO_DATA_FOUND
18. A user-defined exception can be raised:
B. In the executable section
C. In the exception section
♦B
♦C
♦A and B
♦B and C (*)
♦A and C
19. A user-defined exception is raised by using:
♦FLAG exception_name;
♦RAISE exception-name; (*)
♦PRAGMA EXCEPTION_INIT
♦RAISE(error_number, exception_name);
20. There are no employees in department 99. What message or messages will be displayed when the following code is executed?
e_my_excep EXCEPTION;
BEGIN
BEGIN
UPDATE employees SET salary = 10000
WHERE department_id = 99;
IF SQL%ROWCOUNT = 0 THEN
RAISE e_my_excep;
END IF;
EXCEPTION
WHEN e_my_excep THEN
DBMS_OUTPUT.PUT_LINE(‘Message 1’);
RAISE e_my_excep;
DBMS_OUTPUT.PUT_LINE(‘Message 2’);
END;
DBMS_OUTPUT.PUT_LINE(‘Message 3’);
EXCEPTION
WHEN e_my_excep THEN
DBMS_OUTPUT.PUT_LINE(‘Message 4’);
END;
♦Message 1
Message 3
♦Message 1
Message 2
♦Message 1
Message 3
Message 4
♦Message 1
Message 4
(*)
21. Which of the following is NOT correct coding for a procedure parameter?
♦(p_param IN VARCHAR2)
♦(p_param VARCHAR2)
♦(p_param VARCHAR2(50)) (*)
♦(p_param employees.last_name%TYPE)
♦(p_param IN OUT VARCHAR2)
22. Examine the following procedure:
CREATE OR REPLACE PROCEDURE smallproc
(p_param IN NUMBER)
IS
BEGIN ….
The procedure is invoked by:
DECLARE
v_param NUMBER := 20;
BEGIN
smallproc(v_param);
END;
Which of the following statements is true?
♦p_param is a parameter and v_param is an argument
♦p_param is a formal parameter and 20 is an actual parameter
♦p_param is a formal parameter and v_param is an actual parameter (*)
♦p_param and v_param are both formal parameters, while 20 is an actual parameter
♦p_param is an actual parameter and v_param is a formal parameter
23. A procedure will execute faster if it has at least one parameter.
♦True
♦False (*)
24. Which of the following statements about actual parameters is NOT true?
♦An actual parameter is declared in the calling environment, not in the called procedure
♦An actual parameter must be the name of a variable (*)
♦An actual parameter can have a Boolean datatype
♦The datatypes of an actual parameter and its formal parameter must be compatible
♦An actual parameter can have a TIMESTAMP datatype
25. Which of the following can NOT be used as the datatype of a procedure parameter?
♦A non-SQL datatype such as BOOLEAN
♦The name of another procedure (*)
♦A large object datatype such as CLOB
♦A PLSQL record defined using %ROWTYPE
26. You have created procedure MYPROC with a single parameter PARM1 NUMBER. Now you want to add a second parameter to the procedure. Which of the following will change the procedure successfully?
♦ALTER PROCEDURE myproc ADD (parm2 NUMBER);
♦The procedure cannot be modified. Once a procedure has been created, the number of parameters cannot be changed.
♦CREATE OR REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER);
(You do not need to repeat the detailed code of the procedure, only the header)
♦REPLACE PROCEDURE someproc
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN …
♦CREATE OR REPLACE PROCEDURE MYPROC
(parm1 NUMBER, parm2 NUMBER)
IS
BEGIN … (*)
27. Which of the following are characteristics of PL/SQL subprograms but not of anonymous PL/SQL blocks? (Choose three.)
♦Can take parameters (*)
♦Are stored in the database (*)
♦Can begin with the keyword DECLARE
♦Are named (*)
♦Are compiled every time they are executed
28. A PL/SQL stored procedure can accept one or more input parameters and can return one or more output values to the calling environment. True or False?
♦True (*)
♦False
29. A programmer wants to create a PL/SQL procedure named EMP_PROC. What will happen when the following code is executed?
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 999;
DBMS_OUTPUT.PUT_LINE(‘The salary is: ‘ || v_salary);
END;
♦The statement will raise a NO_DATA_FOUND exception because employee_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_salary inside a procedure.
♦The procedure will be created successfully. (*)
♦The statement will fail because the procedure does not have any parameters.
30. A stored PL/SQL procedure can be invoked from which of the following?
Another PL/SQL procedure
A calling application
♦A only
♦A and B
♦A and C
♦A, B and C (*)
♦B and C
31. A PL/SQL procedure named MYPROC has already been created and stored in the database. Which of the following will successfully re-create the procedure after some changes have been made to the code?
♦CREATE PROCEDURE myproc IS …
♦CREATE OR REPLACE PROCEDURE myproc IS …. (*)
♦UPDATE PROCEDURE myproc IS …
♦ALTER PROCEDURE myproc IS …
♦None of the above, because the procedure must be dropped before it can be re-created.
32. A PL/SQL procedure named MY_PROC1 has been successfully created in the database. The procedure has no parameters. Which of the following will successfully invoke the procedure in Application Express? (Choose two.)
♦DECLARE
v_var1 NUMBER := 20;
BEGIN
my_proc1(v_var1);
END;
♦EXECUTE my_proc1;
my_proc1;
END;
(*)
♦CREATE OR REPLACE PROCEDURE my_proc2 IS
BEGIN
my_proc1;
END my_proc2;
(*)
♦SELECT my_proc1 FROM DUAL;
33. What are the type of parameter modes?
♦CHARACTER, NUMBER, DATE, BOOLEAN
♦CONSTANT, VARIABLE, DEFAULT
♦LOCAL, GLOBAL, BOTH
♦IN, OUT, IN OUT (*)
34. Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure was called as follows:
How was parameter B referenced?
♦Positional (*)
♦Named
♦A combination of positionally and named
♦A combination of named and defaulted
♦Defaulted
35. Which parameter mode is the default?
♦IN (*)
♦OUT
♦NUMBER
♦VARIABLE
♦CONSTANT
36. Suppose you set up a parameter with an explicit OUT mode. What is true about that parameter?
♦It must have a DEFAULT value.
♦It cannot have a DEFAULT value. (*)
♦It acts like a constant (its value cannot be changed inside the subprogram).
♦It must be the same type as the matching IN parameter.
♦It inherits its type from the matching IN parameter.
FROM employees
WHERE is_leapyear(hire_date)=TRUE;
♦The IS_LEAPYEAR function must be in the SELECT clause, not the WHERE clause.
♦You cannot use DATE and BOOLEAN datatypes in the same function.
♦The SELECT statement returns more than one row.
♦IS_LEAPYEAR is a reserved word in the SQL language.
♦The function returns a Boolean, and therefore cannot be used within a SELECT statement. (*)
38. Which one of the following statements about user-defined functions is NOT true?
♦They can execute spell-checking routines.
♦They can be used inside SQL statements.
♦They can be combined (nested) together, similar to nesting system functions, for example INITCAP(SUBSTR( …..)).
♦They can return a TIMESTAMP datatype.
♦They can allow you to COMMIT from inside a SELECT statement. (*)
39. In which DML statements can user-defined functions be used?
♦INSERT and UPDATE, but not DELETE.
♦INSERT only.
♦All DML statements. (*)
♦UPDATE only
♦DELETE only
40. Which of the following is a difference between a procedure and a function?
♦A procedure can include DML statements, but a function cannot.
♦A function must have at least one IN parameter, while parameters are optional for a procedure.
♦A procedure can return a BOOLEAN datatype, while a function cannot.
♦A function can be used inside a SQL statement, while a procedure cannot. (*)
♦A procedure can include an EXCEPTION section, while a function cannot.
41. You try to create a function named MYFUNC. The function does not compile correctly because there are errors in your code. Which Dictionary view can you query to see the errors?
♦USER_SOURCE
♦USER_ERRORS (*)
♦USER_OBJECTS
♦USER_DEPENDENCIES
♦USER_COMPILES
42. Consider the following function:
(a NUMBER := 1,
b NUMBER := 2 )
RETURN NUMBER
IS BEGIN
RETURN (a+b);
END ADD_EM;
Which one of the following blocks will NOT work correctly?
♦DECLARE
x NUMBER;
BEGIN
x:= add_em(b=4);
END;
(*)
♦DECLARE
x NUMBER;
BEGIN
x:= add_em(4);
END;
♦DECLARE
x NUMBER;
BEGIN
x:= add_em(4,5);
END;
♦DECLARE
x NUMBER;
BEGIN
x:= add_em;
END;
♦None of them will work.
43. What is wrong with the following code?
(p_param NUMBER(4))
RETURN BOOLEAN
IS BEGIN
RETURN (p_param > 10);
END badfunc;
♦P_PARAM must be declared AFTER the RETURN clause.
♦P_PARAM must have a default value.
♦The datatype of the IN parameter cannot have a precision or scale. It must be NUMBER, not NUMBER(4). (*)
♦RETURN (p_param > 10); is wrong because you cannot return an expression.
♦The NUMBER datatype must have a scale as well as a precision.
44. A function must have at least one IN parameter, and must return exactly one value.
♦True
♦False (*)
45. In a SELECT statement, where can a function NOT be used?
♦In a GROUP BY or HAVING clause.
♦A function can be used anywhere in a SELECT statement. (*)
♦In a WHERE clause.
♦In the column list (SELECT) clause.
♦In an ORDER BY clause.
46. Which Data Dictionary view can be used to display the detailed code of a procedure in your schema?
♦USER_PROCEDURES
♦USER_OBJECTS
♦USER_SOURCE (*)
♦USER_SUBPROGRAMS
♦None of the above.
47. You want to see the names, modes and data types of the formal parameters of function MY_FUNC in your schema. How can you do this? (Choose two)
♦Query USER_PARAMETERS
♦Query USER_SOURCE (*)
♦Query USER_FUNCTIONS
♦SHOW PARAMETER my_funct;
♦DESCRIBE my_funct; (*)
48. Examine the following code:
CREATE PROCEDURE parent
IS BEGIN
child1;
child2;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END parent;
When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. What happens next?
♦CHILD1 ends abruptly. PARENT handles the exception and then ends. CHILD2 does not execute. (*)
♦CHILD1 ends abruptly, PARENT handles the exception, then CHILD2 executes.
♦CHILD1 ends abruptly, PARENT also ends abruptly and returns an unhandled exception.
♦PARENT does not compile because you cannot use NULL; in an exception handler.
49. User REYHAN creates the following procedure: CREATE PROCEDURE proc1 AUTHID CURRENT_USER IS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM tom.employees; END; User BILL wants to execute this procedure. What privileges will BILL need?
♦EXECUTE on REYHAN.PROC1 and SELECT on TOM.EMPLOYEES (*)
♦EXECUTE on REYHAN.PROC1
♦SELECT on TOM.EMPLOYEES
♦BILL needs no privileges
♦None of the above. The procedure will fail to compile because REYHAN does not have SELECT privilege on TOM.EMPLOYEES.
50. How do you specify that you want a procedure MYPROCA to use Invoker’s Rights?
♦CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS…
(*)
♦Invoker’s Rights are the default, therefore no extra code is needed.
♦GRANT INVOKER TO myprocA;
AUTHID OWNER IS…
InfoCad Blog 
Great Blogpost thank you for sharing.
LikeLike