♦False
2. 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;
♦SELECT text FROM USER_SOURCE
WHERE name = ‘DEPTPACK’
AND type = ‘BODY’
ORDER BY line;
3. 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.
4. 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 (*)
5. Examine the following package specification:
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:
taxpack.taxproc;
END;
7. 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
8. 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.
9. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
♦True (*)
♦False
10. What is wrong with the following syntax for creating a package specification?
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.
11. Which one of the following can NOT be part of a Package ?
♦Procedures
♦Explicit cursors
♦Triggers (*)
♦Functions
♦Global variables
12. 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
13. 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
14. What will be displayed when the following code is executed?
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
to be
beside the seaside
(*)
♦I do like to be beside the seaside
15. DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged function. True or False?
♦True (*)
♦False
16. 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.
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. (*)
17. 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 (*)
18. 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 (*)
19. MARY wants HENRY to be able to query her EMPLOYEES table. Mary executes the following code:
v_grant_stmt VARCHAR2(50);
BEGIN
v_grant_stmt := ‘GRANT SELECT ON employees TO henry’;
DBMS_SQL.EXECUTE(v_grant_stmt);
END;
♦False (*)
20. 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.
21. The easiest way to include DDL statements in a PL/SQL block is to use the DBMS_SQL package. True or False?
♦True
♦False (*)
22. 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.
(*)
23. We never need to use a forward declaration when invoking a public subprogram. True or False?
♦True (*)
♦False
24. 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 2
♦1 and 3 (*)
♦2 and 3
♦3 and 4
♦1 and 4
25. Package HRPACK contains the following public function:
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. (*)
26. What is wrong with the following code?
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
27. 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
28. Examine the following code:
— Line A
BEGIN
INSERT INTO log_table VALUES (USER, SYSDATE);
END;
Which of the following can NOT be coded at Line A?
♦BEFORE UPDATE ON employees
♦AFTER INSERT OR DELETE ON employees
♦AFTER SELECT ON employees (*)
♦BEFORE DELETE ON employees
♦AFTER UPDATE OF last_name ON employees
29. The following code will successfully create emp_trigg: True or False?
BEFORE DELETE OF salary ON employees
BEGIN
RAISE_APPLICATION_ERROR(-20202,’Deleting salary is not allowed’);
END;
♦False (*)
30. With which kind of trigger can the :OLD and :NEW qualifiers be used?
♦DDL triggers
♦Database Event triggers
♦Statement triggers
♦Row triggers (*)
♦AFTER triggers
31. There are 3 employees in department 90 and 5 employees in department 50. The following trigger has been created:
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
…
WHERE department_id = 90;
How many times will the trigger fire?
♦Once
♦Three times (*)
♦Four times
♦Five times
♦Eight times
32. 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?
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
33. Examine the following code. To create a row trigger, what code should be included at Line A?
AFTER UPDATE OR DELETE ON departments
— Line A
BEGIN …
♦AFTER EACH ROW
♦FOR EVERY ROW
♦FOR EACH ROW (*)
♦ON EACH ROW
♦ON EVERY ROW
34. User AYSEGUL successfully creates the following trigger:
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.
35. 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;
36. 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?
♦False
37. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
♦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:
♦A datatype of NUMBER(7,2) for the SALARY column
♦A database trigger
♦A check constraint (*)
♦An application trigger
♦A view
39. 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
40. 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. (*)
41. 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 … (*)
42. A trigger can be created in the database or within an application. True or False?
♦True (*)
♦False
43. 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
44. Examine this code:
— 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. 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.
46. 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
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. Examine this code:
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
49. You want to prevent any objects in your schema from being altered or dropped. You decide to create the following trigger:
— 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
50. The database administrator wants to write a log record every time an Oracle Server error occurs in any user’s session. The DBA creates the following trigger:
— 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
InfoCad Blog 