Mid Term Exam Sem 2 #1

1.  When a user session changes the value of a package variable, the new value can immediately be seen by other sessions. True or False?

♦True
♦False (*)

---

2.  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?

♦True
♦False (*)

---

3.  The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False?

♦True
♦False (*)

---

4.  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 ?

♦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’);

---

5.  What will be displayed when the following code is executed?

BEGIN
DBMS_OUTPUT.PUT(‘I do like’);
DBMS_OUTPUT.PUT_LINE(‘to be’);
DBMS_OUTPUT.PUT(‘beside the seaside’);
END;

♦I do like to be
beside the seaside

♦I do like
to be
beside the seaside

♦I do like to be

♦I do liketo be (*)

♦I do like to be beside the seaside

---

6.  Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?

♦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

---

7.  The easiest way to include DDL statements in a PL/SQL block is to use the DBMS_SQL package. True or False?

♦True
♦False (*)

---

8.  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?
♦True
♦False (*)

---

9.  Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose two.)

♦DELETE
♦SAVEPOINT
♦ALTER (*)
♦SELECT ….. FOR UPDATE NOWAIT
♦GRANT (*)

---

10.  A SQL statement can pass through several stages. Which of the following is NOT one of these stages?

♦BIND
♦FETCH
♦PARSE
♦RETURN (*)
♦EXECUTE

---

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

♦A and B
♦A, B and C
♦A and C
♦A, B and D (*)
♦A, B, C and D

---

12.  The following package specification has been created:

CREATE OR REPLACE PACKAGE mypack IS
FUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER);
END mypack;

Which of the following will correctly invoke the package subprograms? (Choose two.)

♦mypack.myfunc(’22-JAN-07′);

♦mypack.myproc(35);

(*)

♦IF NOT mypack.myfunc(SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE(‘Message’);
END IF;

(*)

♦myproc(40);

♦v_num := mypack.myproc(22);

---

13.  Which one of the following can NOT be part of a Package ?

♦Procedures
♦Explicit cursors
♦Triggers (*)
♦Functions
♦Global variables

---

14.  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;

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

---

15.  Which of the following statements about a package initialization block is true?

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

---

16.  Package TAXPACK declares a global variable G_TAXRATE NUMBER(2,2). The value of the tax rate is stored in table TAXTAB in the database. You want to read this value automatically into G_TAXRATE each time a user session makes its first call to TAXPACK. How would you do this?

Declare the global variable as:
g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;

♦Create a database trigger that includes the following code:
SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;

♦Add a private function to the package body of TAXPACK, and invoke the function from the user session.

♦Add a package initialization block to the package body of TAXPACK.

(*)

---

17.  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;
♦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. (*)

---

18.  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?

♦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

---

19.  A package contains both public and private subprograms. Which one of the following statements is true?

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

---

20.  In a package, public components are declared in the specification but private components are not. True or False?

♦True (*)
♦False

---

21.  Package OLDPACK is in your schema. What will happen when the following statement is executed?

DROP PACKAGE oldpack;

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

---

22.  When a change is made to the detailed code of a public procedure in a package (but not to the procedure’s name or parameters), both the specification and the body must be recompiled. True or False?

♦True
♦False (*)

---

23.  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;

♦14
♦7
♦3
♦6 (*)
♦0

---

24.  Package NEWPACK contains several procedures and functions, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.)

♦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

---

25.  We want to remove the specification (but not the body) of package BIGPACK from the database. Which of the following commands will do this?

♦DROP PACKAGE bigpack;
♦DROP PACKAGE SPECIFICATION bigpack;
♦DROP PACKAGE bigpack SPECIFICATION;
♦DROP PACKAGE HEADER bigpack;
♦None of the above (*)

---

26.  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;

♦IF UPDATING LAST_NAME THEN
♦IF UPDATING(‘LAST_NAME’) THEN (*)
♦IF UPDATE(‘LAST_NAME’) THEN
♦IF UPDATING THEN

---

27.  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?

♦True
♦False (*)

---

28.  There are 3 employees in department 90 and 5 employees in department 50. The following trigger has been created:

CREATE TRIGGER upd_emp_trigg
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
…

A user now executes:

UPDATE employees SET department_id = 50
WHERE department_id = 90;

How many times will the trigger fire?

♦Once
♦Three times (*)
♦Four times
♦Five times
♦Eight times

---

29.  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 …

♦AFTER EACH ROW
♦FOR EVERY ROW
♦FOR EACH ROW (*)
♦ON EACH ROW
♦ON EVERY ROW

---

30.  A trigger can be created in the database or within an application. True or False?

♦True (*)
♦False

---

31.  A trigger can be a public subprogram within a PL/SQL package. True or False?

♦True
♦False (*)

---

32.  Which of the following are NOT stored inside the database? (Choose two.)

♦A PL/SQL package specification
♦A database trigger
♦An anonymous block (*)
♦An application trigger (*)
♦A sequence

---

33.  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:

♦A datatype of NUMBER(7,2) for the SALARY column
♦A database trigger
♦A check constraint (*)
♦An application trigger
♦A view

---

34.  Which of the following are good guidelines to follow when creating a database trigger? (Choose two.)

♦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. (*)

---

35.  You can code COMMIT and ROLLBACK statements in a trigger body. True or False?

♦True
♦False (*)

---

36.  What type of database object would you create to write an auditing record automatically every time a user connects to the database?

♦A procedure
♦A complex view
♦A trigger (*)
♦A function
♦A package

---

37.  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?

♦True (*)
♦False

---

38.  What is wrong with the following code?

CREATE OR REPLACE TRIGGER emp_dept_trigg
BEFORE UPDATE OR DELETE ON employees, departments
BEGIN
…
♦One trigger can be associated with only one table

(*)

♦The second line should be:
BEFORE (UPDATE,DELETE) ON employees, departments

♦DML triggers must be row triggers, so FOR EACH ROW is missing

♦The second line should be:
BEFORE UPDATE OR DELETE ON employees OR departments

---

39.  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;

♦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

---

40.  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;

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

---

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

♦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;

---

42.  Which dictionary view shows the detailed code of a trigger body?

♦USER_SOURCE
♦USER_TRIGGERS (*)
♦USER_OBJECTS
♦USER_DML_TRIGGERS
♦USER_SUBPROGRAMS

---

43.  User AYSEGUL successfully creates the following trigger:

CREATE TRIGGER loc_trigg
BEFORE UPDATE ON aysegul.locations
BEGIN ….

AYSEGUL now tries to drop the LOCATIONS table. What happens?

♦An error message is displayed because you cannot drop a table that is associated with a trigger.
♦The table is dropped and the trigger is disabled.
♦The trigger is dropped but the table is not dropped.
♦Both the table and the trigger are dropped. (*)
♦None of the above.

---

44.  Examine this code:

CREATE TRIGGER de_trigg
— Line A
BEGIN …

Which of the following are NOT valid at Line A ? (Choose two.)

♦AFTER LOGOFF ON SCHEMA (*)
♦AFTER LOGON ON SCHEMA
♦BEFORE LOGOFF ON SCHEMA
♦BEFORE DISCONNECT ON SCHEMA (*)
♦AFTER SERVERERROR ON SCHEMA

---

45.  Which kinds of trigger can cause a mutating table problem? (Choose two.)

♦BEFORE UPDATE row triggers (*)
♦DDL triggers
♦AFTER DELETE row triggers (*)
♦Database Event triggers
♦INSTEAD OF triggers

---

46.  What is the purpose of using the CALL statement in a trigger?

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

---

47.  Which of the following statements could cause a DDL trigger to fire?

♦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 (*)

---

48.  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?

♦A row trigger
♦A statement trigger
♦A database event trigger (*)
♦A DDL trigger
♦An AFTER trigger

---

49.  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?

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

---

50.  Examine this code:

CREATE TRIGGER new_trigg
AFTER CREATE ON reserved_word
BEGIN …

Which of the following can be used in place of reserved_word? (Choose two.)

♦TABLE
♦SCHEMA (*)
♦USER
♦DATABASE (*)
♦TABLE employees