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
2. A public function in a package is invoked from within a SQL statement. The function’s code can include a COMMIT statement. True or False?
♦True
♦False (*)
3. 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.)
♦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); (*)
4. Which of the following are not allowed in a bodiless package? (Choose three)
♦Subprograms (*)
♦Global variables
♦Private variables (*)
♦User-defined exceptions
♦DML statements (*)
5. 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
6. 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 (*)
7. 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
SELECT COUNT(*) FROM ALL_OBJECTS
WHERE object_type LIKE ‘PACK%’
AND owner <> USER;
♦7
♦3
♦6 (*)
♦0
♦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
10. 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.
11. 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);
12. 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.
13. 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;
♦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.
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. 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
♦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. (*)
16. 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 (*)
17. Which of the following exceptions can be raised ONLY when using the UTL_FILE package? (Choose two).
♦INVALID_PATH (*)
♦NO_DATA_FOUND
♦VALUE_ERROR
♦READ_ERROR (*)
♦E_MYEXCEP
18. The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False?
♦True
♦False (*)
19. The DBMS_OUTPUT.PUT procedure places text in a buffer but does not display the contents of the buffer. True or False?
♦True (*)
♦False
20. 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’);
21. Name two reasons for using Dynamic SQL.
♦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. (*)
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?
♦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
CREATE OR REPLACE PROCEDURE addcol
(p_col_name IN VARCHAR2) IS
v_first_string VARCHAR2(100) := ‘ALTER TABLE EMPLOYEES ADD (‘;
v_second_string VARCHAR2(6) := ‘ DATE)’;
BEGIN
… Line A
END;
Which of the following will work correctly when coded at line A? (Choose two.)
♦v_first_string || p_col_name || v_second_string;
♦EXECUTE IMMEDIATE v_first_string || p_col_name || v_second_string;
(*)
♦EXECUTE IMMEDIATE ‘v_first_string’ || p_col_name || ‘v_second_string’;
♦v_first_string := v_first_string || p_col_name;
EXECUTE IMMEDIATE v_first_string || v_second_string;
(*)
♦EXECUTE v_first_string || p_col_name || v_second_string;
24. 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 (*)
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;
♦last_name, salary RETURNING INTO emp_info;
TYPE t_dept IS TABLE OF departments%ROWTYPE
INDEX BY BINARY_INTEGER;
BEGIN
(p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept);
— remaining code
END dept_proc;
♦False (*)
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;
♦False
♦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.
29. 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 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
♦False
31. 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;
♦True
♦False (*)
32. 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
33. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
♦True
♦False (*)
34. A trigger can be created in the database or within an application. True or False?
♦True (*)
♦False
35. 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
36. A trigger can be a public subprogram within a PL/SQL package. True or False?
♦True
♦False (*)
37. 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. (*)
38. You can use a trigger to prevent rows from being deleted from the EMPLOYEES table on Mondays. True or False?
♦True (*)
♦False
39. 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
40. 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.
41. 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
42. 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 (*)
43. 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;
44. Which dictionary view shows the detailed code of a trigger body?
♦USER_SOURCE
♦USER_TRIGGERS (*)
♦USER_OBJECTS
♦USER_DML_TRIGGERS
♦USER_SUBPROGRAMS
45. 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?
♦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
46. With which kind of trigger can the :OLD and :NEW qualifiers be used?
♦DDL triggers
♦Database Event triggers
♦Statement triggers
♦Row triggers (*)
♦AFTER triggers
47. 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 (*)
48. 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
49. 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;
♦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.
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;
BEGIN
— some action
END AFTER EACH ROW;
BEGIN
— some action
END AFTER STATEMENT;
END log_emps;
♦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.
InfoCad Blog 