v_last_name employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘A’);
BEGIN
SELECT last_name INTO v_last_name
FROM employees WHERE department_id = 75;
DBMS_OUTPUT.PUT_LINE(‘B’);
END;
DBMS_OUTPUT.PUT_LINE(‘C’);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘D’);
END;
♦A
C
D
♦A
D
(*)
♦A
♦A
B
D
♦None of the above
2. Exceptions declared in a block are considered local to that block, and global to all its sub-blocks. True or False?
♦True (*)
♦False
3. What will happen when the following code is executed?
DECLARE
e_excep1 EXCEPTION;
e_excep2 EXCEPTION;
BEGIN
RAISE e_excep1;
EXCEPTION
WHEN e_excep1 THEN BEGIN
RAISE e_excep2; END;
END;
♦It will fail to compile because you cannot have a subblock inside an exception section.
♦It will fail to compile because e_excep1 is out of scope in the subblock.
♦It will fail to compile because you cannot declare more than one exception in the same block.
♦It will compile successfully and return an unhandled e_excep2 to the calling environment. (*)
4. The following code does not violate any constraints and will not raise an ORA-02292 error. What will happen when the code is executed?
BEGIN
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);
5. Examine the following code. Why does the exception handler not follow good practice guidelines?
DECLARE
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.
6. Which of the following EXCEPTION sections are constructed correctly? (Choose two.)
♦EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN OTHERS THEN statement_2;
END;
(*)
♦EXCEPTION
WHEN OTHERS THEN statement_2;
WHEN NO_DATA_FOUND THEN statement_1;
END;
♦EXCEPTION
WHEN NO_DATA_FOUND THEN statement_1;
WHEN NO_DATA_FOUND THEN statement_2;
WHEN OTHERS THEN statement_3;
END;
♦EXCEPTION
WHEN OTHERS THEN statement_1;
END;
(*)
7. The following EXCEPTION section is constructed correctly. True or False?
EXCEPTION
WHEN NO_DATA_FOUND OR TOO_MANY_ROWS
THEN statement_1;
statement_2;
WHEN OTHERS
THEN statement_3;
END;
♦True (*)
♦False
8. 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
9. 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 (*)
10. 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);
11. Examine the following code fragment. At Line A, you want to raise an exception if the fetched salary value is greater than 30000. How can you do this?
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees
WHERE employee_id = 100;
IF v_salary > 30000 THEN
— Line A
END IF;
…
♦Use RAISE_APPLICATION_ERROR to raise an exception explicitly. (*)
♦Test for WHEN OTHERS in the exception section, because WHEN OTHERS traps all exceptions.
♦Define an EXCEPTION variable and associate it with an Oracle Server error number using PRAGMA EXCEPTION_INIT.
12. Examine the followiing code. Which exception handlers would successfully trap the exception which will be raised when this code is executed? (Choose two.)
DECLARE
CURSOR emp_curs IS SELECT * FROM employees;
v_emp_rec emp_curs%ROWTYPE;
BEGIN
FETCH emp_curs INTO v_emp_rec;
OPEN emp_curs;
CLOSE emp_curs;
EXCEPTION …
END;
♦WHEN CURSOR_NOT_OPEN
♦WHEN INVALID_CURSOR (*)
♦WHEN OTHERS (*)
♦WHEN NO_DATA_FOUND
♦WHEN INVALID_FETCH
13. 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?
EXCEPTION
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 (*)
♦TOO_MANY_ROWS (*)
♦NO_DATA_FOUND (*)
♦OTHERS
♦ZERO_DIVIDE (*)
♦E_INSERT_EXCEP
15. User-defined exceptions must be declared explicitly by the programmer, but then are raised automatically by the Oracle Server. True or False?
♦True
♦False (*)
16. There are no employees in department_id 99. What output will be displayed when the following code is executed?
DECLARE
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
17. A user-defined exception must be declared as a variable of data type EXCEPTION. True or False?
♦True (*)
♦False
18. There are no employees in department 99. What message or messages will be displayed when the following code is executed?
DECLARE
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
♦Message 1
Message 3
Message 4
♦Message 1
Message 4
(*)
19. The following procedure has been created:
CREATE OR REPLACE PROCEDURE myproc
(A IN NUMBER := 20,
B IN NUMBER,
C IN NUMBER DEFAULT 30)
IS …..
Which of the following will invoke the procedure correctly?
♦myproc(40);
♦myproc(10, B => 30, 50);
♦myproc(C => 25);
♦All of the above
♦None of the above (*)
20. What are the type of parameter modes?
♦CHARACTER, NUMBER, DATE, BOOLEAN
♦CONSTANT, VARIABLE, DEFAULT
♦LOCAL, GLOBAL, BOTH
♦IN, OUT, IN OUT (*)
21. Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure was called as follows:
SOMEPROC(10,20,D=>50);
How was parameter B referenced?
♦Positional (*)
♦Named
♦A combination of positionally and named
♦A combination of named and defaulted
♦Defaulted
22. Procedure SOMEPROC has five parameters named A, B, C, D, E in that order. The procedure was called as follows:
SOMEPROC(10,20,D=>50);
How was parameter D referenced?
♦Positionally
♦Named (*)
♦A combination of positionally and named
♦A combination of named and defaulted
♦Defaulted
23. You have created a procedure named MYPROC that accepts three IN parameters A, B, and C (all numbers). Which of the following calls to MYPROC is NOT correct?
♦myproc(5,10,20);
♦myproc(a=>5,b=>10,20) (*)
♦myproc(a=>5,b=>10,c=>20)
♦myproc(5,10,c=>20)
24. A procedure will execute faster if it has at least one parameter.
♦True
♦False (*)
25. Examine the following procedure:
CREATE OR REPLACE PROCEDURE smallproc
(p_param IN NUMBER)
IS
BEGIN ….
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
26. 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
27. Which of the following best describes how an IN parameter affects a procedure?
♦It describes the order in which the procedure’s statements should be executed.
♦It describes which parts of the procedure’s code are optional or conditional.
♦It makes the procedure execute faster.
♦It passes a value into the procedure when the procedure is invoked. (*)
♦It allows complex calculations to be executed inside the procedure.
28. 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.
♦They must return exactly one value to the calling environment.
♦They can have an exception section. (*)
♦They can be invoked from inside a SQL statement.
♦They can accept 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. View and reload your code later by clicking on the History button in the SQL Commands window. True or False?
♦True
♦False (*)
32. A nested subprogram can only be invoked from the main subprogram. True or False?
♦True (*)
♦False
33. 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;
♦BEGIN
my_proc1;
END;
(*)
♦CREATE OR REPLACE PROCEDURE my_proc2 IS
BEGIN
my_proc1;
END my_proc2;
(*)
♦SELECT my_proc1 FROM DUAL;
34. 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;
♦ALTER PROCEDURE myproca TO INVOKER;
♦CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS…
35. What will happen when the following procedure is executed?
PROCEDURE log_usage (p_card_id NUMBER, p_loc NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN
INSERT INTO log_table (card_id, location, tran_date)
VALUES (p_card_id, p_loc, SYSDATE);
COMMIT;
END log_usage;
♦The subprogram will fail because the PRAGMA statement must be before IS.
♦The subprogram will fail because it is missing AUTHID CURRENT_USER before IS.
♦The compilation will fail because a semicolon after AUTONOMOUS_TRANSACTION is required. (*)
♦The program will compile successfully.
36. User BOB creates procedure MYPROC using the default Definer’s Rights. BOB then executes:
GRANT EXECUTE ON bob.myproc TO ted;
When TED invokes BOB.MYPROC, whose privileges are checked?
♦TED’s privileges
♦PUBLIC’s privileges
♦SYSTEM’s privileges
♦BOB’s privileges (*)
♦ORACLE’s privileges
37. 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.
38. Examine the following code:
CREATE OR REPLACE FUNCTION add_func
(p_param1 NUMBER, p_param2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (p_param1 + p_param2);
END;
What will be displayed when the following SQL statement is executed?
SELECT add_func(6, add_func(3,8)) FROM dual;
♦23
♦11
♦66
♦17 (*)
♦An error message will be displayed because you cannot nest user-defined functions.
IS BEGIN
RETURN NULL;
END bad_one;
♦You cannot RETURN a NULL.
♦You must declare the type of the RETURN before the IS. (*)
♦You must have at least one IN parameter.
♦You must code CREATE OR REPLACE, not CREATE.
♦The body of the function must contain at least one executable statement (as well as RETURN).
40. What is wrong with the following code?
CREATE FUNCTION badfunc
(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.
41. You have created a function named NEWFUNC. You now change some of the function code, and try to recreate the function by executing:
CREATE OR REPLACE FUNCTION newfunc …. ;
What happens?
♦The command fails because the function already exists.
♦The function is automatically dropped and then recreated. (*)
♦The command fails because you should execute: CREATE AND REPLACE ….;
♦A second function named NEWFUNC_2 is created.
♦The function is dropped but not recreated.
42. 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.
43. 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.
44. Examine the following code: CREATE PROCEDURE parent
IS BEGIN
child1;
child2;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END parent;
Neither CHILD1 nor CHILD2 has an exception handler.
When PARENT is invoked, CHILD1 raises a NO_DATA_FOUND exception. What happens next?
♦PARENT handles the exception, then CHILD1 continues to execute.
♦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.
45. The following code shows the dependencies between three procedures:
CREATE PROCEDURE parent
IS BEGIN
child1;
child2;
END parent;
You now try to execute:
What happens?
♦CHILD2 is dropped successfully. PARENT and CHILD1 are both marked INVALID.
♦The database automatically drops PARENT as well.
♦CHILD2 is dropped successfully. PARENT is marked INVALID. CHILD1 is still valid. (*)
♦The database automatically drops CHILD1 as well.
46. A benefit of user-defined functions is that the function can accept any SQL or PL/SQL data type. True or False?
♦True
♦False (*)
47. Why will the following statement fail? SELECT employee_id, tax(p_value => salary) FROM employees;
♦User-defined functions are not allowed in the SELECT clause
♦Name notation is not allowed (*)
♦The data type for the tax variable does not match the data type for salary
♦The statement will execute and not fail
48. Which of the following is a benefit of user-defined functions? (Choose 3)
♦They can add business rules to the database and can be reused many times. (*)
♦They can be used in a WHERE clause to filter data and thereby increase efficiency. (*)
♦They can do the same job as built-in system functions such as UPPER and ROUND.
♦They can often be used inside SQL statements. (*)
49. Identify the valid collection types: (Choose all correct answers)
♦INDEX BY TABLE (*)
♦INDEX BY VIEW
♦INDEX BY TABLE OF ROWS
♦INDEX BY TABLE OF RECORDS (*)
50. You an use %ROWTYPE with tables and views.
♦True (*)
♦False
InfoCad Blog 
I am really grateful to the owner of this website who has shared this enormous article at at this place.|
LikeLike