PLSQL Semester 2 Mid Term Exam

Section 10
     
  1.  Package MYPACK contains procedure MYPROC. You can see which parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?  (1) Points
     
    True
    False (*)
 
 2.  Which one of the following can NOT be part of a Package ?  (1) Points
     
    Procedures
    Explicit cursors
    Triggers (*)
    Functions
    Global variables

3.  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.

 4.  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.
 
 5.  Package HRPACK contains the following public function:
FUNCTION empfunc (p_deptno NUMBER) RETURN NUMBER IS
BEGIN
    UPDATE employees SET salary = salary * 1.1
       WHERE department_id = p_deptno;
    RETURN SQL%ROWCOUNT;
END empfunc;
What will happen when the following SQL statement is executed?
SELECT department_name, hrpack.empfunc(department_id)
FROM departments;
(1) Points
     
    The SELECT will fail because you cannot return SQL%ROWCOUNT from a packaged function.
    The SELECT will fail because you cannot call packaged functions from within a SQL statement.
    The SELECT will fail because you cannot execute a DML statement from within a query.
    The SELECT will succeed because it is referencing a different table from the function. (*)
     
 6.  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); (*)

7.  We never need to use a forward declaration when invoking a public subprogram. True or False?  (1) Points
     
    True (*)
    False
 
8.  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 (*)
 
9.  Which of the following statements about a package initialization block is true?  (1) Points
     
    It cannot contain any SQL statements.
    It is an anonymous block at the end of a package body. (*)
    It is a procedure in a package that must be invoked before the rest of the package can be used.
    It is an anonymous block in the package specification.
    It is executed automatically every time any global variable in the package is referenced.
 
10.  Package NEWPACK contains several procedures and functions, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.)  (1) Points (Choose all correct answers)
     
    From an anonymous block
    From any procedure in NEWPACK (*)
    From any private function in another package
    From any function in NEWPACK (*)
    From any public procedure in another package
 
 Section 10
     
11.  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 (*)
 
12.  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
 
13.  Examine the following package specification:
CREATE OR REPLACE PACKAGE taxpack IS
    CURSOR empcurs IS SELECT * FROM employees;
    PROCEDURE taxproc;
END mypack;
The package body of TAXPACK also includes a function called TAXFUNC. Which one of the following statements is NOT true?
(1) Points
     
    The procedure can be invoked by:
BEGIN
    taxpack.taxproc;
END;

   The packaage will not compile because you cannot declare a cursor in the specification. (*)

   TAXPROC is a public procedure and TAXFUNC is a private function
 
   TAXPROC can invoke TAXFUNC if TAXPROC is coded before TAXFUNC
 
   TAXPROC can open the cursor
 
14.  We need to declare a package variable named MYVAR, which can be referenced by any subprogram in the package but can NOT be referenced from outside the package. In the following code, where should MYVAR be declared?
CREATE OR REPLACE PACKAGE varpack IS
    -- Point A
...
END varpack;
CREATE OR REPLACE PACKAGE BODY varpack IS
    -- Point B
PROCEDURE varproc IS
    -- Point C
    BEGIN
       ...
    END varproc;
PROCEDURE ...
...
    -- Point D
END varpack;
(1) Points
     
    Point A
    Point B (*)
    Point C
    Point D
    Point B or Point C, they will both work

Section 11
     
15.  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 (*)
 
16.  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
 
17.  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
 
18.  An Oracle directory called FILESDIR has been created by executing:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file called C:\NEWFILES\EMP_REPORT.TXT ?  (1) Points
     
    UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT');
    UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w');
    UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w'); (*)
    UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');
 
19.  DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False?  (1) Points
     
    True (*)
    False

20.  Which of the following best describes the purpose of the UTL_FILE package?  (1) Points
     
    It is used to load binary files such as employees' photos into the database.
    It is used to read and write text files stored outside the database. (*)
    It is used to find out how much free space is left on an operating system disk.
    It is used to query CHAR and VARCHAR2 columns in tables.
 
 Section 12
     
21.  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 (*)
 
22.  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
 
23.  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. (*)
     
24.  MARY wants HENRY to be able to query her EMPLOYEES table. Mary executes the following code:
DECLARE
    v_grant_stmt VARCHAR2(50);
BEGIN
    v_grant_stmt := 'GRANT SELECT ON employees TO henry';
    DBMS_SQL.EXECUTE(v_grant_stmt);
END;
Mary has successfully granted the privilege to Henry. True or False?
 (1) Points
     
    True
     False (*)
 
25.  You want to take make a copy of all the cities in the world listed in the cities table, which contains millions of rows. The following procedure accomplish this efficiently. True or False?
CREATE OR REPLACE PROCEDURE copy_cities IS
  TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
  v_citiestab t_emp;
BEGIN
  SELECT * BULK COLLECT INTO v_citiestab FROM cities;
  FORALL i IN v_citiestab.FIRST..v_citiestab.LAST
  INSERT INTO new_cities VALUES v_citiestab(i);
END copy_cities;
(1) Points
     
    True (*)
    False

26.  All but which of the following are benefits of using the NOCOPY hint? (Choose two)  (1) Points
(Choose all correct answers)
     
    Safer because it uses passing by value. (*)
    Efficient since it uses less memory.
    Uses a larger block of server memory for faster access. (*)
    Faster because a single copy of the data is used.
    Eliminates extra processing.
 
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.
    Incorrect. Refer to Section 12 Lesson 2.
 
28.  To create a list of the top 20 movies from a catalog of millions of titles, the following statement grabs those rows using a collection. True or False?
...
  TYPE nametab IS TABLE OF movies.title%TYPE;
  Title_tab nametab;
...
SELECT title BULK COLLECT INTO title_tab FROM movies
ORDER BY rental_count DESC;
...
(1) Points
     
    True (*)
    False
 
Section 13
     
29.  What is the purpose of using the CALL statement in a trigger?  (1) Points
     
    It allows an INSTEAD OF trigger to be a statement trigger.
    It allows the trigger body code to be placed in a separate procedure. (*)
    It prevents cascading triggers.
    It allows the trigger body code to be placed in a separate procedure or function.
    It allows both DML events and DDL events to be handled using a single trigger.
 
30.  A trigger automatically inserts a row into a logging table every time a user's session receives this error message:
ORA-00942: table or view does not exist
What kind of trigger is this?  (1) Points
     
    A row trigger
    A statement trigger
    A database event trigger (*)
    A DDL trigger
    An AFTER trigger
 
31.  Examine the following code:
CREATE TRIGGER emp_trigg
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    -- Line A
END;

Which of the following statements is NOT allowed at Line A?
 (1) Points
     
    SELECT count(*) INTO v_count FROM departments;
    UPDATE employees SET job_id = 'IT_PROG' WHERE employee_id = :OLD.employee_id;
    SELECT count(*) INTO v_count FROM employees; (*)
    DBMS_OUTPUT.PUT_LINE('A salary was updated');
    None. All of the above are allowed.
 
32.  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 (*)
 
33.  You can code COMMIT and ROLLBACK statements in a trigger body. True or False?  (1) Points
     
    True
    False (*)
 
34.  A trigger can be created in the database or within an application. True or False?  (1) Points
     
    True (*)
    False
 
35.  You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays. 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.  A trigger can be a public subprogram within a PL/SQL package. True or False?  (1) Points
     
    True
    False (*)
 
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.  INSTEAD OF triggers are always row triggers, even if FOR EACH ROW is omitted. True or False?  (1) Points
     
    True (*)
    False
 
40.  What is the event that will cause the trigger on the emp_details view below to fire?
CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT ON emp_details
BEGIN
  INSERT INTO new_emps
    VALUES (:NEW.employee_id, :NEW.last_name,
        :NEW.salary, :NEW.department_id);
      new_depts
      SET dept_sal = dept_sal + :NEW.salary
      WHERE department_id = :NEW.department_id;
END;
(1) Points
     
    An attempt to update salary column on the new_depts table
    A new employee is added to the emp_details table
    A procedure calls the new_emp_dept trigger.
    An attempt to add a row in the emp_details view (*)
    An attempt to add a row in the new_depts table.
 
 Section 13
     
 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.  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
 
43.  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.
 
44.  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
 
45.  What is wrong with the following code?
CREATE TRIGGER dept_trigg
BEFORE UPDATE OF department_name ON departments
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.department_name);
END;
(1) Points
     
    You cannot use :NEW in a BEFORE trigger, only in an AFTER trigger.
    You cannot use :NEW or :OLD in a statement trigger. (*)
    You cannot use DBMS_OUTPUT.PUT_LINE inside a trigger.
    The second line should be: BEFORE UPDATE ON departments.department_name

46.  You need to create a trigger that will fire whenever an employee's salary or job_id is updated, but not when any other column of the EMPLOYEES table is updated. Which of the following is the correct syntax to do this?  (1) Points
     
    CREATE TRIGGER emp_upd_trigg
AFTER UPDATE ON employees (salary, job_id)
BEGIN ...
 
    CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary, job_id ON employees
BEGIN ... (*)

    CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF (salary, job_id) ON employees
BEGIN ...
 
    CREATE TRIGGER emp_upd_trigg
AFTER UPDATE OF salary OR job_id ON employees
BEGIN ...

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.  Which dictionary view shows the detailed code of a trigger body?  (1) Points
     
    USER_SOURCE
    USER_TRIGGERS (*)
    USER_OBJECTS
    USER_DML_TRIGGERS
    USER_SUBPROGRAMS
 
49.  MARY and JOE's schemas each contain an EMPLOYEES table. JOE creates the following trigger:
CREATE TRIGGER upd_trigg
AFTER DELETE ON joe.employees
FOR EACH ROW
BEGIN
    DELETE FROM mary.employees
       WHERE employee_id = :OLD.employee_id;
END;

A third user TOM needs to delete rows from JOE's EMPLOYEES table. What object privileges will TOM and JOE need?
(1) Points
     
    TOM does not need any object privileges, but JOE needs DELETE on both TOM.EMPLOYEES and MARY.EMPLOYEES
 
    TOM needs DELETE on JOE.EMPLOYEES and JOE needs DELETE on MARY.EMPLOYEES (*)
 
    JOE does not need any object privileges, but TOM needs DELETE on MARY.EMPLOYEES
 
    TOM needs DELETE on MARY.EMPLOYEES and JOE needs EXECUTE on TOM.UPD_TRIGG
 
50.  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