PLSQL Mid Term Exam Semester 2

1. Examine the following code:
CREATE TRIGGER emp_triggAFTER UPDATE OF salary ON employeesFOR 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 FROMdepartments;
UPDATE employees SET job_id = 'IT_PROG' WHEREemployee_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.

2. Which of the following statements could cause a DDLtrigger to fire?
DROP TABLE employees;
ALTER TABLE departments ADD (budgetNUMBER(8,2));
CREATE TABLE newemp AS SELECT * FROMemployees;
TRUNCATE TABLE locations;
All of the above (*)

3. The database administrator wants to write a log recordevery time an Oracle Server error occurs in any user's session. The DBAcreates the following trigger:

CREATE TRIGGER log_errs_trigg--Line A
BEGIN
--Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

AFTER ERROR ON DATABASE
AFTER SERVER ERROR ON DATABASE
AFTER SERVERERROR ON SCHEMA
AFTER SERVERERROR ON DATABASE (*)
AFTER ORACLE ERROR ON SCHEMA

4. You want to prevent any objects in your schema frombeing 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 ?

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

5. Which kinds of trigger can cause a mutating tableproblem? (Choose two.)
(Choose all correct answers)
BEFORE UPDATE row triggers (*)
DDL triggers
AFTER DELETE row triggers (*)
Database Event triggers
INSTEAD OF triggers
Database Event triggers
INSTEAD OF triggers

6. Examine this code:
CREATE TRIGGER new_triggAFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choosetwo.) (Choose all correct answers)
TABLE
SCHEMA (*)
USER
DATABASE (*)
TABLE employees

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


8. You need to disable all triggers that are associatedwith 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;

9. After the following SQL statement is executed, all thetriggers on the DEPARTMENTS table will no longer fire, but will remain inthe database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;

True (*)
False

10. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_triggBEFORE UPDATE ON aysegul.locationsBEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?

An error message is displayed because youcannot drop a table that is associated with a trigger.
The table is dropped and the trigger isdisabled.
The trigger is dropped but the table is notdropped.
Both the table and the trigger are dropped.(*)
None of the above.

11. A SQL statement canpass through several stages. Which of the following is NOT one of thesestages?
BIND
FETCH
PARSE
RETURN (*)
EXECUTE

12. Examine the following code:
CREATE OR REPLACE PROCEDURE myproc ISCURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
END LOOP;
END;
What will happen when this procedure is invoked?

All views in the user's schema will be dropped. (*)
The procedure will not compile successfullybecause the syntax of EXECUTE IMMEDIATE is incorrect.
The procedure will raise an exception becauseDynamic SQL can drop tables but cannot drop views.
The procedure will raise an exception becauseone of the views is a complex view.

13. Which of the following SQL statements can be included ina PL/SQL block only by using Dynamic SQL? (Choose two.) (Choose all correct answers)
DELETE
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)

14. MARY wants HENRY to be able to query her EMPLOYEEStable. 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 (*)

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

Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)

Dick: 0, Hazel: 0
Both queries will fail because the syntax ofDBMS_OUTPUT.PUT_LINE is incorrect

16. Package CURSPACKdeclares a global cursor in the package specification. The packagecontains three public procedures: OPENPROC opens the cursor; FETCHPROCfetches 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

The first 15 rows will be fetched.
The first 10 rows will be fetched, then thefirst 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. (*)

17. Which of the following statements about a packageinitialization block is true?

It cannot contain any SQL statements.
It is an anonymous block at the end of apackage body. (*)
It is a procedure in a package that must beinvoked before the rest of the package can be used.
It is an anonymous block in the package specification.
It is executed automatically every time anyglobal variable in the package is referenced.

18. A public function in a package is invoked from within aSQL statement. The function's code can include a COMMIT statement. True or False?
True
False (*)

19. Package TAXPACK declares a global variable G_TAXRATENUMBER(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 eachtime a user session makes its first call to TAXPACK. How would you dothis?

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

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

Add a private function to the package body ofTAXPACK, and invoke the function from the user session.
Add a package initialization block to thepackage body of TAXPACK.(*)

20. 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 and 2
1 and 3 (*)
2 and 3
3 and 4
1 and 4

21. Which of the followingare NOT stored inside the database? (Choose two.) (Choose all correct answers)
A PL/SQL package specification
A database trigger
An anonymous block (*)
An application trigger (*)
A sequence

22. You can use a trigger to prevent rows from being deletedfrom the EMPLOYEES table on Mondays. True or False?
True (*)
False

23. What type of database object would you create to writean auditing record automatically every time a user connects to the database?
A procedure
A complex view
A trigger (*)
A function
A package

24. A business rule states that an employee's salary cannotbe greater than 99,999.99 or less than 0. The best way to enforce thisrule is by using:
A datatype of NUMBER(7,2) for the SALARYcolumn
A database trigger
A check constraint (*)
An application trigger
A view

25. The following objects have been created in a user's schema:
-a function FUNC1
-A package PACK1 which contains a public procedure PACKPROC and aprivate function PACKFUNC
-a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of typeNUMBER, and the functions return BOOLEANs. Which of the following callsto these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)

pack1.packproc(25); (*)
SELECT func1(100) FROM dual;
trigg1;
IF pack1.packfunc(40) THEN ...
IF func1(75) THEN ... (*)

26. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
True
False (*)

27. A trigger can be created in the database or within anapplication. True or False?

True (*)
False

28. An Oracle directory called FILESDIR has been created byexecuting:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file calledC:\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');

29. Why is it better to use DBMS_OUTPUT only in anonymousblocks, not inside stored subprograms such as procedures?
Because DBMS_OUTPUT cannot be used inside procedures
Because anonymous blocks display messageswhile the block is executing, while procedures do not display anythinguntil their execution has finished
Because DBMS_OUTPUT should be used only fortesting and debugging PL/SQL code (*)
Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

30. Which of the following best describes the purpose of theUTL_FILE package?

It is used to load binary files such asemployees' photos into the database. 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 isleft on an operating system disk.
It is used to query CHAR and VARCHAR2 columnsin tables.

31. What will be displayedwhen 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 like to be beside the seaside

32. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
True (*)
False

33. The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack ISFUNCTION 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.) (Choose all correct answers)
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);

34. Which one of the following can NOT be part of a Package?
Procedures
Explicit cursors
Triggers (*)
Functions
Global variables

35. What is wrong with the following syntax for creating apackage specification?

CREATE OR REPLACE PACKAGE mypack ISg_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
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 oneprocedure.
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 noerrors.

36. What is wrong with the following code?
CREATE OR REPLACE TRIGGER loc_triggBEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;

END loc_trigg;
a trigger.
BEFORE DELETE OF locations (*)
 and execute successfully.
The last line should be: You cannot use RAISE_APPLICATION_ERROR inside
The second line should be: You cannot use ROLLBACK inside a trigger.
Nothing is wrong, this trigger will compile

37. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_triggBEFORE UPDATE OR DELETE ON employees, departmentsBEGIN
...
One trigger can be associated with only onetable(*)
The second line should be: BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOREACH ROW is missing
The second line should be: BEFORE UPDATE OR DELETE ON employees OR departments

38. A DML statement trigger fires only once for eachtriggering DML statement, while a row trigger fires once for each rowprocessed by the triggering statement. True or False?
True (*)
False

39. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_triggBEFORE DELETE OF salary ON employeesBEGIN
RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;


True
False (*)

40. In a package, public components are declared in thespecification but private components are not. True or False?
True (*)
False

41. Package NEWPACKcontains several procedures and functions, including private functionPRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.) (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

42. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?

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;
AND object_type = 'PACKAGE BODY'ORDER BY line;

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

43. Package OLDPACK is in your schema. What will happen whenthe following statement is executed?
DROP PACKAGE oldpack;
The body will be dropped but thespecification will be retained.
The specification will be dropped but thebody will be retained.
Both the specification and the body will bedropped. (*)
The statement will fail because you must dropthe body before you can drop the specification.

44. When a change is made to the detailed code of a publicprocedure 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 (*)

45. Your schema contains four packages, each having aspecification and a body. You have also been granted privileges to accessthree packages (and their bodies) in other users' schemas. What will bedisplayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTSWHERE object_type LIKE 'PACK%'
AND owner <> USER;

14
7
3
6 (*)
0

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

47. Examine the following code. To create a row trigger,
what code should be included at Line A?
CREATE TRIGGER dept_triggAFTER UPDATE OR DELETE ON departments--Line A
BEGIN ...

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

48. With which kind of trigger can the :OLD and :NEWqualifiers be used?
DDL triggers
Database Event triggers
Statement triggers
Row triggers (*)
AFTER triggers

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

50. Examine the following trigger. It should raise anapplication error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. Whats hould be coded at line A?
CREATE TRIGGER stop_ln_triggBEFORE UPDATE ON employeesBEGIN
--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