PLSQL Institute Exit Exam

Section 1
   
1. Which of the following statements about PL/SQL and SQL is true? (1) Points
   
 PL/SQL and SQL are both ANSI-compliant.
 PL/SQL and SQL can be used with many types of databases, including Oracle.
 PL/SQL and SQL are both Oracle proprietary programming languages.
 PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)
 
2. Errors are handled in the Exception part of the PL/SQL block. True or False? (1) Points
   
 True (*)
 False

Section 2
   
3. Which of the following are valid assignment statements? (Choose two.) (1) Points (Choose all correct answers)
   
 v_string = 'Hello';
 v_string := Hello;
 v_number := 17 + 34; (*)
 v_string := 'Hello'; (*)
 v_date := 28-DEC-06;
 
4. Which of the following are valid identifiers? (Choose two.) (1) Points (Choose all correct answers)

 Full Name
 students_street_address (*)
 v_code (*)
 #hours
 completion_%
 
5. A movie is an example of which category of data type? (1) Points

 Scalar
 Composite
 Reference
 LOB (*)

6. If you are using the %TYPE attribute, you can avoid hard coding the: (1) Points
 
 Data type (*)
 Table name
 Column name
 Constraint

7. When an exception occurs within a PL/SQL block, the remaining statements in the executable section of the

block are skipped. True or False? (1) Points
 
 True (*)
 False
   
 Section 3
   
8. You declare an implicit cursor in the DECLARE section of a PL/SQL block. True or False? (1) Points

 True
 False (*)
 
9. The following code will return the last name of the employee whose employee id is equal to 100: True or

False?
DECLARE
    v_last_name employees.last_name%TYPE;
    employee_id employees.employee_id%TYPE := 100;
BEGIN
    SELECT last_name INTO v_last_name
    FROM employees
    WHERE employee_id = employee_id;
END;
(1) Points

 True
 False (*)

10. What keyword goes at the beginning of the following SQL statement?
_____ INTO test1 a
    USING all_objects b
       ON (a.object_id = b.object_id)
    WHEN MATCHED THEN
       UPDATE SET a.status = b.status
    WHEN NOT MATCHED THEN
       INSERT (object_id, status)
       VALUES (b.object_id, b.status);
(1) Points
   
 INSERT
 UPDATE
 DELETE
 MERGE (*)

 Section 4
   
11. A PL/SQL block contains the following code:
v_counter := 1;
LOOP
EXIT WHEN v_counter = 5;
    v_counter := v_counter + 1;
END LOOP;

What is the value of V_COUNTER after the loop is finished?
(1) Points
 
 5 (*)
 6
 1
 This is an infinite loop; the loop will never finish.
 
12. What will be the value of variable c after the following code is executed?
DECLARE
    a BOOLEAN := TRUE;
    b BOOLEAN := NULL;
    c NUMBER;
BEGIN
    IF a AND b THEN c := 2;
       ELSIF a OR b THEN c := 0;
       ELSE c := 1;
    END IF;
END;
(1) Points
 
 1
 Null
 0 (*)
 2

13. When coding two nested loops, both loops must be of the same type. For example, you cannot code a FOR loop inside a WHILE loop. True or False? (1) Points

 True
 False (*)
   
14. What type of control structures are repetition statements that enable you to execute statements in a PLSQL block repeatedly? (1) Points

 IF statements
 Loops (*)
 CASE expressions
 CASE statements

15. Which statement best describes when a FOR loop should be used? (1) Points
 
 When the number of iterations is known (*)
 When testing the value in a Boolean variable
 When the controlling condition must be evaluated at the start of each iteration
   
 Section 5
   
16. The employees table contains 20 rows. What will happen when the following code is executed?
DECLARE
    CURSOR emp_curs IS
       SELECT job_id FROM employees;
    v_job_id employees.job_id%TYPE;
BEGIN
    OPEN emp_curs;
    LOOP
       FETCH emp_curs INTO v_job_id;
       DBMS_OUTPUT.PUT_LINE(v_job_id);
       EXIT WHEN emp_curs%NOTFOUND;
    END LOOP;
    CLOSE emp_curs;
END;
(1) Points
 
 20 job_ids will be displayed.
 The block will fail and an error message will be displayed.
 21 rows of output will be displayed; the first job_id will be displayed twice.
 21 rows of output will be displayed; the last job_id will be displayed twice. (*)
   
17. A cursor has been declared as:
CURSOR c_curs (p_param VARCHAR2) IS
  SELECT * FROM mytable
  WHERE mycolumn = p_param;

Which of the following will open the cursor successfully?
(1) Points
   
 OPEN c_curs(p_param = ABC);
 OPEN c_curs('ABC'); (*)
 OPEN c_curs USING ("ABC");
 p_param := 'ABC';
 OPEN c_curs(p_param);

18. You want to display each row from the DEPARTMENTS table, and immediately underneath it, a list of all EMPLOYEES in that department. Which of the following is a good way to do this? (1) Points
 
 Use a single cursor, declared as SELECT * FROM employees GROUP BY department_id;
 Use two cursors, one for each of the two tables. Declare the EMPLOYEES cursor with a parameter for the
DEPARTMENT_ID. (*)
 Write a SELECT statement which JOINs the two tables, and use CONNECT BY PRIOR and LEVEL to display the rows in the correct order.
 Use a single cursor with a cursor FOR loop.
 Change the physical model so that all employee and department data is in a single table.
 
19. There are no employees in department_id 75.
Which of the following is NOT a valid cursor FOR loop with a subquery? (1) Points
 
 FOR emp_rec IN
   (SELECT last_name, salary FROM employees) LOOP ...
 
 FOR emp_rec IN
    (SELECT * FROM employees) LOOP ...
 
 FOR emp_rec IN
    (SELECT last_name, salary FROM employees ORDER BY last_name) LOOP ...
 
 FOR emp_rec IN
    (SELECT * FROM employees WHERE department_id = 75) LOOP ...
 
 None of the above. They are all valid. (*)
 
20. The following cursor has been declared:
    CURSOR emp_curs IS
       SELECT first_name, last_name, job_id, salary
       FROM employees;

Which of the following correctly declares a composite record with the same structure as the cursor?
(1) Points
 
 emp_rec emp_rec%ROWTYPE;
 emp_rec emp_curs%TYPE;
 emp_rec emp_curs%ROWTYPE; (*)
 emp_rec cursor%ROWTYPE;

 Section 5

21. User MARY has locked a row of the EMPLOYEES table. Now, user SAEED tries to open the following cursor:
CURSOR c IS
  SELECT * FROM employees
  FOR UPDATE WAIT 5;

What will happen when SAEED's session tries to fetch the row that MARY has locked?
(1) Points
 
 SAEED's session successfully fetches the first 5 rows and then waits indefinitely to fetch the 6th row.
 SAEED's session waits for 5 seconds, and then raises an exception if MARY has not unlocked the row. (*)
 SAEED's session waits for 5 seconds, then SAEED is disconnected from the database.
 SAEED's session waits for 5 seconds, then MARY's session is rolled back.
 SAEED's session waits for 5 minutes, and then raises an exception if MARY has not unlocked the row.

 Section 6
   
22. Which of the following statements could cause a DDL trigger to fire? (1) Points

 DROP TABLE employees;
 ALTER TABLE departments ADD (budget NUMBER(8,2));
 CREATE TABLE newemp AS SELECT * FROM employees;
 TRUNCATE TABLE locations;
 All of the above (*)
   
23. Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE TRIGGER dept_trigg
AFTER UPDATE OR DELETE ON departments
    -- Line A
BEGIN ...
(1) Points
 
 AFTER EACH ROW
 FOR EVERY ROW
 FOR EACH ROW (*)
 ON EACH ROW
 ON EVERY ROW
   
 Section 7
   
24. There are no employees in department 75. What will be displayed when this code is executed?
DECLARE
    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;
(1) Points

A
C
D

 
A
D(*)

A

A
B
D

None of the above

25. 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;
(1) Points

 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.
   
26. Which of the following will successfully return a user-defined error message? (1) Points

 RAISE_APPLICATION_ERROR('Error Raised',-22001);
 RAISE_APPLICATION_ERROR(-20257,'Error raised'); (*)
 RAISE_APPLICATION_ERROR(-22001,'Error Raised');
 RAISE_APPLICATION_ERROR('Error Raised',-20257);

27. Examine the following code. What message or messages will be displayed when this code is executed?
DECLARE
    v_last_name employees.last_name%TYPE;
    v_number NUMBER := 27;
BEGIN
    v_number := v_number / 0;
    SELECT last_name INTO v_last_name FROM employees
       WHERE employee_id = 999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('No rows were found');
    WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE('Attempt to divide by zero');
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
(1) Points
 
 No rows were found
 Attempt to divide by zero (*)
 Attempt to divide by zero No rows were found
 An error occurred
 No message will be displayed
   
 Section 8
   
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? (1) Points
 
 True (*)
 False

29. You want to create a procedure named SOMEPROC which accepts a single parameter named SOMEPARM. The parameter can be up to 100 characters long. Which of the following is correct syntax to do this? (1) Points
 
 CREATE PROCEDURE someproc
    (someparm varchar2)
 IS
 BEGIN ...(*)

 CREATE PROCEDURE someproc
    (someparm varchar2(100) )
 IS
 BEGIN...

 CREATE PROCEDURE someproc
 IS
    (someparm VARCHAR2;)
 BEGIN...
 
 CREATE PROCEDURE someproc
    someparm varchar2(100);
 IS
 BEGIN...
 
 CREATE PROCEDURE someproc
    (someparm 100)
 IS
 BEGIN ...

30. Suppose you set up a parameter with an explicit OUT mode. What is true about that parameter? (1) Points
 
 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.

 Section 9
   
31. Which Data Dictionary view can be used to display the detailed code of a procedure in your schema? (1) Points

 USER_PROCEDURES
 USER_OBJECTS
 USER_SOURCE (*)
 USER_SUBPROGRAMS
 None of the above.

32. A function must have at least one IN parameter, and must return exactly one value. (1) Points
 
 True
 False (*)

33. How do you specify that you want a procedure MYPROCA to use "Definer's Rights"? (1) Points
 
 CREATE OR REPLACE PROCEDURE myproca AUTHID CURRENT_USER IS...
 CREATE OR REPLACE PROCEDURE myproca AUTHID OWNER IS...
 GRANT DEFINER TO myprocA;
 ALTER PROCEDURE myproca TO DEFINER;
 Definer's Rights are the default, therefore no extra code or commands are needed. (*)
   
34. Which of the following is a legal location for a function call in a SQL statement? (Choose 3) (1) Points  
(Choose all correct answers)

 CREATE TABLE statement
 WHERE clause in a DELETE statement (*)
 The ORDER BY and GROUP BY clauses of a query (*)
 VALUES clause of an INSERT statement (*)

 Section 10
   
35. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ? (1) Points  
 
 SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'
ORDER BY line;
 
 SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'
ORDER BY line;(*)

 SELECT text FROM USER_SOURCE
WHERE object_name = 'DEPTPACK'
AND object_type = 'PACKAGE BODY'
ORDER BY line;
 
 SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'
ORDER BY line;

36. A public function in a package is invoked from within a SQL statement. The function's code can include a COMMIT statement. True or False? (1) Points

 True
 False (*)

37. What is wrong with the following syntax for creating a package specification?
CREATE OR REPLACE PACKAGE mypack IS
    g_constant1 NUMBER(6) := 100;
    FUNCTION func1 (p_param1 IN VARCHAR2);
    FUNCTION func2;
END mypack;
(1) Points
 
 You cannot declare constants in the specification.
 A package must contain at least one procedure.
 The RETURN datatype of the functions must be specified.(*)
 The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS
 Nothing is wrong, this code contains no errors.

 Section 11
   
38. DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False? (1) Points
 
 True (*)
 False

39. Package MULTIPACK declares the following global variable:
    g_myvar NUMBER;
User DICK executes the following:
    multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:

BEGIN
    DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;

What values will Dick and Hazel see? (1) Points
 
 Dick: 45, Hazel: 45
 Dick: 45, Hazel: 0
 Dick: 45, Hazel: null (*)
 Dick: 0, Hazel: 0
 Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is incorrect

 Section 13
   
40. What is wrong with the following code?
CREATE TRIGGER call_trigg
AFTER LOGOFF ON SCHEMA
BEGIN
    CALL drop_proc;
END;
(1) Points
 
 You cannot code an AFTER LOGOFF trigger
 When using CALL, you must not code BEGIN
 When using CALL, you must not code END;
 The CALL statement must not end with a semicolon (;)
 All of the above (*)

 Section 13
   
41. Examine the following trigger. It should raise an application error if a user tries to update an employee's
last name. It should allow updates to all other columns of the EMPLOYEES table. What should be coded at line A?
CREATE TRIGGER stop_ln_trigg
BEFORE UPDATE ON employees
BEGIN
       -- Line A
    RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
    END IF;
END;
(1) Points

 IF UPDATING LAST_NAME THEN
 IF UPDATING('LAST_NAME') THEN (*)
 IF UPDATE('LAST_NAME') THEN
 IF UPDATING THEN

42. You need to disable all triggers that are associated with DML statements on the DEPARTMENTS table. Which of the following commands should you use? (1) Points

 ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
 ALTER TRIGGER DISABLE ALL ON departments;
 ALTER TABLE departments DISABLE TRIGGERS;
 DISABLE ALL TRIGGERS ON departments;
 ALTER TABLE departments DROP ALL TRIGGERS;

43. You can code COMMIT and ROLLBACK statements in a trigger body. True or False? (1) Points

 True
 False (*)

44. What is wrong with the following code?
CREATE OR REPLACE TRIGGER loc_trigg
BEFORE DELETE ON locations
BEGIN
    RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
    ROLLBACK;
END;
(1) Points
 
 The last line should be: END loc_trigg;
 You cannot use RAISE_APPLICATION_ERROR inside a trigger.
 The second line should be: BEFORE DELETE OF locations
 You cannot use ROLLBACK inside a trigger.(*)
 Nothing is wrong, this trigger will compile and execute successfully.
   
 Section 14
   
45. Procedure B has its local variable emp_number changed to emp_name. The data type of emp_id is changed from number to integer. It is compiled successfully. In Signature Mode, Procedure A, which is dependent on remote Procedure B, will compile and execute successfully. True or False? (1) Points

 True (*)
 False

46. When a table is dropped, all PL/SQL subprograms that reference the table are automatically dropped. True or False? (1) Points

 True
 False (*)

 Section 15
   
47.  Identify some benefits of using conditional compilation. (Choose two) (1) Points (Choose all correct
answers)

 Use new features with the latest database release and disable them with older database versions (*)
 Speed up the compilation time of a lengthy PL/SQL subprogram.
 Determine initialization values during start up of a database session.
 Activate debugging or tracing statements in the development environment (*)

48. Native compilation always runs faster; therefore SQL statements in PL/SQL will always run faster, also. True or False? (1) Points
 
 True
 False (*)
   
49. What does the following statement do?
DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','ENABLE','SESSION');
(1) Points
 
 Enables the PERFORMANCE warning category, setting other category settings to disabled.
 Enables the PERFORMANCE warning category, leaving other category settings unchanged.
 Add the PERFORMANCE warning category into a PL/SQL variable.
 Disables all warning categories, then enables the PERFORMANCE category.
 Enables the PERFORMANCE warning category, leaving other category settings unchanged, for the current session.(*)
   
50. Obfuscation does not allow anyone to see the source code, including the owner. True or False? (1) Points

 True (*)
 False