1. What is the correct format to declare a variable using the following emp_pkg package composite data type? TYPE emprec_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
♦emp_pkg.emprec_type;
♦emprec_type.emp_pkg;
♦v_emp_table emprec_type.emp_pkg;
♦v_emp_table emp_pkg.emprec_type; (*)
♦None of the above
2. 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;
FROM departments;
♦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. (*)
3. Which two of these declarations cannot be in the same package specification?
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);
♦1 and 3 (*)
♦2 and 3
♦3 and 4
♦1 and 4
4. Functions called from a SQL query or DML statement must not end the current transaction, or create or roll back to a savepoint. True or False?
♦True (*)
♦False
5. 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. (*)
6. 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
7. 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;
♦Point A
♦Point B (*)
♦Point C
♦Point D
♦Point B or Point C, they will both work
8. 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?
♦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
9. 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.
10. In a package, public components are declared in the specification but private components are not. True or False?
♦True (*)
♦False
11. Which of the following statements about packages is NOT true ?
♦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.
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. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
♦True (*)
♦False
14. Which of the following are good reasons for creating and using Packages?
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, B and C
♦A and C
♦A, B and D (*)
♦A, B, C and D
15. 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’);
16. 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
17. The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False?
♦True
♦False (*)
18. 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
19. 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 (*)
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?
♦True
♦False (*)
21. 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 (*)
22. A SQL statement can pass through several stages. Which of the following is NOT one of these stages?
♦BIND
♦FETCH
♦PARSE
♦RETURN (*)
♦EXECUTE
23. 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?
♦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
24. A public packaged procedure contains the following SQL statement:
UPDATE employees SET salary = salary * 1.1;
When is this SQL statement parsed?
♦When the package specification is created
♦When the package body is created (*)
♦When the package header is loaded into memory.
♦When the package is loaded into memory.
♦Only the first time the procedure is executed.
25. FORALL can be used with any DML statement. True or False?
♦True (*)
♦False
26. Deterministic means the function will always return the same output return value for any given set of input argument values. True or False?
♦True (*)
♦False
27. Where would you place the BULK COLLECT statement in the following example?
DECLARE
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
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;
♦Position B
♦Position C (*)
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;
♦Performs the SELECT statement to determine the department id of the new department. (*)
♦Uses the new department number in a cursor.
29. 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;
♦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.
30. 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
31. INSTEAD OF triggers are always row triggers, even if FOR EACH ROW is omitted. True or False?
♦True (*)
♦False
32. 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 (*)
33. Which of the following can NOT be coded in the body of a DML trigger? (Choose two.)
♦IF DELETING THEN
♦IF SELECTING THEN (*)
♦IF INSERTING THEN
♦IF UPDATING (‘JOB_ID’) THEN
♦IF OTHERS THEN (*)
34. Which dictionary view shows the detailed code of a trigger body?
♦USER_SOURCE
♦USER_TRIGGERS (*)
♦USER_OBJECTS
♦USER_DML_TRIGGERS
♦USER_SUBPROGRAMS
35. 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;
♦True (*)
♦False
36. Which of the following will remove a trigger in your schema named EMP_TRIGG from the database?
♦DROP emp_trigg TRIGGER;
♦ALTER TRIGGER emp_trigg DISABLE;
♦DROP TRIGGER emp_trigg; (*)
♦REMOVE TRIGGER emp_trigg;
♦None of the above
37. 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?
♦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 …
38. 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
39. 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;
WHERE department_id = 50;
How many rows will be inserted into audit_table?
♦One (*)
♦Two
♦Five
♦Six
♦None of the above
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. 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
42. 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
43. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
♦True
♦False (*)
44. 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
45. 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.)
♦pack1.packproc(25); (*)
♦SELECT func1(100) FROM dual;
trigg1;
♦IF pack1.packfunc(40) THEN …
♦IF func1(75) THEN … (*)
46. You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays. True or False?
♦True (*)
♦False
47. 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 ?
♦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
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. 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.
50. 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.
InfoCad Blog 