PLSQL Semester 2 Mid Term Exam

Section 10
     
  1.  Your schema contains four packages, each having a specification and a body. You have also been granted privileges to access three packages (and their bodies) in other users' schemas. What will be displayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTS
    WHERE object_type LIKE 'PACK%'
    AND owner <> USER;
(1) Points
     
    14
    7
    3
    6 (*)
    0

2.  We want to remove the specification (but not the body) of package BIGPACK from the database. Which of the following commands will do this?  (1) Points
     
    DROP PACKAGE bigpack;
    DROP PACKAGE SPECIFICATION bigpack;
    DROP PACKAGE bigpack SPECIFICATION;
    DROP PACKAGE HEADER bigpack;
   None of the above (*)

3.  A package contains both public and private subprograms. Which one of the following statements is true?  (1) Points
     
    Each subprogram is loaded into memory when it is first invoked.
    The public subprograms are all loaded into memory at the same time, but the private subprograms are loaded into memory one at a time as they are invoked.
    The whole package is loaded into memory when the first call is made to any subprogram in the package. (*)
    If three users invoke three different subprograms in the package, there will be three copies of the code in memory.

4.  Package OLDPACK is in your schema. What will happen when the following statement is executed?
DROP PACKAGE oldpack; (1) Points
     
    The body will be dropped but the specification will be retained.
    The specification will be dropped but the body will be retained.
    Both the specification and the body will be dropped. (*)
    The statement will fail because you must drop the body before you can drop the specification.
 
 5.  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;

6.  Package MYPACK contains procedure MYPROC. You can see which parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?  (1) Points
     
    True
    False (*)
 
7.  Which of the following are good reasons for creating and using Packages?

Related procedures, functions and variables can be grouped together as a single unit
We can recompile the package body without having to recompile the specification
We can create packages without needing any system privileges
We can declare INDEX BY tables and use them as parameters
(1) Points
     
    A and B
    A, B and C
    A and C
    A, B and D (*)
    A, B, C and D

8.  Which part of a package must be created first, the specification or the body?  (1) Points
     
    The body
    The specification (*)
    The specification and body must be created at the same time.
    It does not matter which is created first.
    The body can be created first, but only if the package has no specification.

9.  Which of the following statements about packages is NOT true ?  (1) Points
     
    All procedures and functions must be declared in the specification. (*)
    Cursors can be declared in the specification.
    The body contains the detailed code of the subprograms.
    Variables can be declared in the body.
    The specification must be created before the body.

10.  Which two of these declarations cannot be in the same package specification?

PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2);
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);
 (1) Points
     
    1 and 2
    1 and 3 (*)
    2 and 3
    3 and 4
    1 and 4
 
11.  Examine the following package code:
CREATE OR REPLACE PACKAGE ol_pack IS
    PROCEDURE subprog (p1 IN VARCHAR2, p2 IN NUMBER);
    PROCEDURE subprog (param1 IN CHAR, param2 IN NUMBER);
    FUNCTION subprog (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE;
END ol_pack;
Which of the following calls will be successful? (Choose two.)
(1) Points (Choose all correct answers)
     
    ol_pack.subprog('Jane',30);
    ol_pack.subprog(param1=>'Jane',param2=>30); (*)
    v_number := ol_pack.subprog(p1=>'Jane');
    v_date := ol_pack.subprog('Jane',30); (*)
 
 12.  The following example shows a valid record data type and variaable. True or False?
TYPE DeptRecTyp
  IS RECORD (deptid NUMBER(4) NOT NULL := 99,
  dname departments.department_name%TYPE,
  loc departments.location_id%TYPE,
  region regions%ROWTYPE );
dept_rec DeptRecTyp;
(1) Points
     
    True (*)
    False

13.  Examine the following code:
CREATE OR REPLACE PACKAGE emppack IS
    PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
END emppack;
CREATE OR REPLACE PACKAGE BODY emppack IS
    -- Line A
    PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER) IS
       BEGIN
          IF NOT sal_ok(p_salary) THEN
             RAISE_APPLICATION_ERROR(-20201,'Invalid salary');
          END IF;
    END upd_emp;
    FUNCTION sal_ok(pf_salary NUMBER) RETURN BOOLEAN IS
       BEGIN
          IF pf_salary > 50000 THEN RETURN FALSE;
          ELSE RETURN TRUE;
          END IF;
    END sal_ok;
END emppack;
What must be coded at Line A for this package to compile successfully?
 (1) Points
     
    FUNCTION sal_ok;
    FUNCTION sal_ok(pf_salary NUMBER);
    FUNCTION sal_ok(pf_salary NUMBER) RETURN BOOLEAN; (*)
    PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
    Nothing is needed at Line A
 
14.  Which of the following are not allowed in a bodiless package? (Choose three)  (1) Points
(Choose all correct answers)
     
    Subprograms (*)
    Global variables
    Private variables (*)
    User-defined exceptions
    DML statements (*)
 
 Section 11
     
 15.  Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?  (1) Points
     
    Because DBMS_OUTPUT cannot be used inside procedures
    Because anonymous blocks display messages while the block is executing, while procedures do not display anything until their execution has finished
    Because DBMS_OUTPUT should be used only for testing and debugging PL/SQL code (*)
    Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

16.  Which of the following exceptions can be raised ONLY when using the UTL_FILE package? (Choose two). (1) Points (Choose all correct answers)
     
    INVALID_PATH (*)
    NO_DATA_FOUND
    VALUE_ERROR
    READ_ERROR (*)
    E_MYEXCEP
 
17.  The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False?  (1) Points
     
    True
    False (*)
 
18.  The DBMS_OUTPUT.PUT procedure places text in a buffer but does not display the contents of the buffer. True or False?  (1) Points
     
    True (*)
    False
 
19.  Package CURSPACK declares a global cursor in the package specification. The package contains three public procedures: OPENPROC opens the cursor; FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor.
What will happen when a user session executes the following commands in the order shown?
    curspack.openproc; -- line 1
    curspack.fetchproc; -- line 2
    curspack.fetchproc; -- line 3
    curspack.openproc; -- line 4
    curspack.fetchproc; -- line 5
    curspack.closeproc; -- line 6
(1) Points
     
    The first 15 rows will be fetched.
    The first 10 rows will be fetched, then the first 5 rows will be fetched again.
    The first 5 rows will be fetched three times.
    An error will occur at line 2.
    An error will occur at line 4. (*)

20.  A cursor is declared in a package specification. User SIOBHAN opens the cursor and fetches the first three rows from the cursor's active set, but does not close the cursor.
User FRED now connects to the database. FRED can immediately fetch the next three rows without opening the cursor. True or False?  (1) Points
     
    True
    False (*)
 
21.  A SQL statement can pass through several stages. Which of the following is NOT one of these stages?  (1) Points
     
    BIND
    FETCH
    PARSE
    RETURN (*)
    EXECUTE
 
22.  You want to create a function which drops a table. You write the following code:
CREATE OR REPLACE FUNCTION droptab
    (p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    DROP TABLE p_tab_name;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;
Why will this procedure not compile successfully?
(1) Points
     
    Because you can never drop a table from inside a function
    Because the PL/SQL compiler cannot check if the argument of p_tab_name is a valid table-name (*)
    Because you do not have the privilege needed to drop a table
    Because you cannot use RETURN in the exception section

23.  Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose two.)  (1) Points (Choose all correct answers)
     
    DELETE
    SAVEPOINT
    ALTER (*)
    SELECT ..... FOR UPDATE NOWAIT
    GRANT (*)
 
24.  Name two reasons for using Dynamic SQL.  (1) Points (Choose all correct answers)
     
    Provide the ability to execute SQL statements whose structure is unknown until execution time. (*)
    Provide the ability to handle mutating rows when executing a statement involving the same table.
    Allow fetch of data for DML statements.
    Enables session-control statements to be written and executed from PL/SQL. (*)
 
25.  What is the correct syntax to use the RETURNING phrase at Position A?
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id -- Position A
  dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary);
END;
(1) Points
     
    RETURNING FROM emp_info;
    last_name, salary RETURNING INTO emp_info;
    RETURNING last_name, salary INTO emp_info; (*)
    RETURNING last_name, salary TO emp_info;
     
26.  Where would you place the BULK COLLECT statement in the following example?
DECLARE
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
-- Position A
FROM departments
WHERE department_id > 70;
BEGIN
  OPEN c1
-- Position B;
  FETCH c1
-- Position C
  INTO dept_recs;
END;
(1) Points
     
    Position A
    Position B
    Position C (*)
     
 27.  What does the RETURNING clause do in the example below?
CREATE OR REPLACE PROCEDURE new_dept
  (p_dept_name IN departments.name%TYPE) IS
  v_new_dept_id departments.dept_id%TYPE;
BEGIN
  INSERT INTO departments (dept_id, name)
    VALUES dept_seq.NEXTVAL, p_dept_name
    RETURNING dept_seq.CURRVAL INTO v_new_dept_id;
  DBMS_OUTPUT.PUT_LINE(p_dept_name ||' is department number ' || v_new_dept_id);
END new_dept;
(1) Points
     
    Inserts the new department id in the department table.
    Performs the SELECT statement to determine the department id of the new department. (*)
    Uses the new department number in a cursor.
     
28.  FORALL can be used with any DML statement. True or False?  (1) Points
     
    True (*)
    False
 
Section 13
     
  29.  Which kinds of trigger can cause a mutating table problem? (Choose two.)  (1) Points
(Choose all correct answers)
     
    BEFORE UPDATE row triggers (*)
    DDL triggers
    AFTER DELETE row triggers (*)
    Database Event triggers
    INSTEAD OF triggers
 
 30.  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 (*)
 
31.  You want to prevent any objects in your schema from being altered or dropped. You decide to create the following trigger:
CREATE TRIGGER stop_ad_trigg
    -- Line A
BEGIN
    RAISE_APPLICATION_ERROR(-20203,'Invalid Operation');
END;
What should you code at Line A ?
(1) Points
     
    AFTER ALTER OR DROP ON SCHEMA
    INSTEAD OF ALTER OR DROP ON SCHEMA
    BEFORE ALTER OR DROP ON SCHEMA (*)
    BEFORE ALTER, DROP ON SCHEMA
    AFTER ALTER, DROP ON SCHEMA

32.  Examine this code:
CREATE TRIGGER de_trigg
    -- Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.)
(1) Points (Choose all correct answers)
     
    AFTER LOGOFF ON SCHEMA (*)
    AFTER LOGON ON SCHEMA
    BEFORE LOGOFF ON SCHEMA
    BEFORE DISCONNECT ON SCHEMA (*)
    AFTER SERVERERROR ON SCHEMA

33.  You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays. True or False?  (1) Points
     
    True (*)
    False

34.  Which of the following are NOT stored inside the database? (Choose two.)  (1) Points
(Choose all correct answers)
     
    A PL/SQL package specification
    A database trigger
    An anonymous block (*)
    An application trigger (*)
    A sequence
 
35.  A trigger can be created in the database or within an application. True or False?  (1) Points
     
    True (*)
    False
 
36.  Which of the following are good guidelines to follow when creating a database trigger? (Choose two.)  (1) Points (Choose all correct answers)
     
    Where possible, use a trigger to enforce a foreign key constraint.
     Use triggers to override privilege checking and view other users' private tables.
     Do not use a trigger to replace or duplicate something which the Oracle Server does automatically. (*)
     Use triggers to prevent unauthorized users from SELECTing confidential data.
     Do not create a trigger that automatically fires another trigger. (*)
 
37.  The following objects have been created in a user's schema:
- a function FUNC1
- A package PACK1 which contains a public procedure PACKPROC and a private function PACKFUNC
- a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of type NUMBER, and the functions return BOOLEANs. Which of the following calls to these objects (from an anonymous block) are correct? (Choose two.)  (1) Points (Choose all correct answers)
     
    pack1.packproc(25); (*)
    SELECT func1(100) FROM dual;
    trigg1;
    IF pack1.packfunc(40) THEN ...
    IF func1(75) THEN ... (*)
 
38.  A business rule states that an employee's salary cannot be greater than 99,999.99 or less than 0. The best way to enforce this rule is by using:  (1) Points
     
    A datatype of NUMBER(7,2) for the SALARY column
    A database trigger
    A check constraint (*)
    An application trigger
    A view

39.  With which kind of trigger can the :OLD and :NEW qualifiers be used?  (1) Points
     
    DDL triggers
    Database Event triggers
    Statement triggers
    Row triggers (*)
    AFTER triggers
 
40.  What are the components of a compound trigger?  (1) Points
     
    Declaration section, timing sections, and exception section.
    Declaration section, referencing section, and timing sections.
    Declaration section and at least one timing section. (*)
    Declaration section and at least two timing sections.
    Declaration section and all four timing sections.
 
41.  Which of the following can NOT be coded in the body of a DML trigger? (Choose two.)  (1) Points
(Choose all correct answers)
     
    IF DELETING THEN
    IF SELECTING THEN (*)
    IF INSERTING THEN
    IF UPDATING ('JOB_ID') THEN
    IF OTHERS THEN (*)
 
42.  In the following code:
CREATE TRIGGER mytrigg
INSTEAD OF INSERT OR UPDATE ON my_object_name
FOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?
(1) Points
     
    True
    False (*)
     
43.  What is wrong with the following code example for a compound trigger?
CREATE OR REPLACE TRIGGER log_emps
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
 TYPE t_log_emp IS TABLE OF log_table%ROWTYPE
 INDEX BY BINARY_INTEGER;
 log_emp_tab t_log_emp;

AFTER EACH ROW IS
BEGIN
-- some action
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
-- some action
END AFTER STATEMENT;
END log_emps;
(1) Points
     
    The order of the timing statements is reversed. (*)
    The declaration section is missing the DECLARE keyword.
    The triggering event FOR UPDATE is not allowed.
    The COMPOUND TRIGGER statement is missing IS.
    There is nothing wrong with this example.
 
44.  Which dictionary view shows the detailed code of a trigger body?  (1) Points
     
    USER_SOURCE
    USER_TRIGGERS (*)
    USER_OBJECTS
    USER_DML_TRIGGERS
    USER_SUBPROGRAMS

45.  After the following SQL statement is executed, all the triggers on the DEPARTMENTS table will no longer fire, but will remain in the database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;
 (1) Points
     
    True (*)
    False
 
46.  Which of the following will remove a trigger in your schema named EMP_TRIGG from the database?  (1) Points
     
    DROP emp_trigg TRIGGER;
    ALTER TRIGGER emp_trigg DISABLE;
    DROP TRIGGER emp_trigg; (*)
    REMOVE TRIGGER emp_trigg;
    None of the above
 
47.  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.

48.  There are five employees in department 50. The following trigger is created:
CREATE TRIGGER upd_emp
AFTER UPDATE ON employees
BEGIN
    INSERT INTO audit_table VALUES (USER, SYSDATE);
END;

A user now executes:

UPDATE employees SET salary = salary * 1.1
    WHERE department_id = 50;

How many rows will be inserted into audit_table?
 (1) Points
     
    One (*)
    Two
    Five
    Six
    None of the above
 
49.  The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_trigg
BEFORE DELETE OF salary ON employees
BEGIN
    RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;
(1) Points
     
    True
    False (*)
 
50.  A DML statement trigger fires only once for each triggering DML statement, while a row trigger fires once for each row processed by the triggering statement. True or False?  (1) Points
     
    True (*)
    False