1. When a table is dropped, all PL/SQL subprograms that reference the table are automatically dropped. True or False?
♦True
♦False (*)
2. Package EMPPACK contains a public procedure GET_EMP, which contains a reference to the EMPLOYEES table. Procedure CALL_EMP invokes EMPPACK.GET_EMP. The following SQL statement is executed:
ALTER TABLE employees ADD (gender CHAR(1));
Which one of the following statements is true?
♦The specification and body of EMPPACK are invalidated, but CALL_EMP remains valid.
♦The body of EMPPACK is invalidated, but the specification remains valid. (*)
♦EMPPACK.GET_EMP is invalidated, but other procedures in EMPPACK remain valid.
♦Nothing is invalidated because the PL/SQL code does not reference the GENDER column.
3. Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram will recompile successfully? (Choose two.)
♦Declaring record structures using %ROWTYPE (*)
♦Using a cursor FOR loop instead of opening and closing the cursor explicitly
♦SELECTing a list of column-names instead of using SELECT *
♦Including a column list with INSERT statements (*)
4. Examine the following code:
CREATE VIEW ed_view AS
SELECT * FROM employees NATURAL JOIN departments;
CREATE PROCEDURE ed_proc IS
CURSOR ed_curs IS SELECT * FROM ed_view;
Which of the following statements about dependencies are true? (Choose two.)
♦ED_PROC is indirectly dependent on DEPARTMENTS (*)
♦EMPLOYEES is referenced by ED_VIEW (*)
♦ED_CURS is directly dependent on ED_VIEW
♦ED_PROC is referenced by ED_VIEW
♦ED_PROC is directly dependent on EMPLOYEES
5. A single procedure can be both a referenced object and a dependent object. True or False?
♦True (*)
♦False
6. Which of the following will display the number of invalid package bodies in your schema?
♦SELECT COUNT(*) FROM user_objects
WHERE object_type = ‘PACKAGE BODY’
AND status = ‘INVALID’;
(*)
♦SELECT COUNT(*) FROM user_dependencies
WHERE type = ‘PACKAGE BODY’
AND status = ‘INVALID’;
♦SELECT COUNT(*) FROM user_packages
WHERE status = ‘INVALID’;
♦SELECT COUNT(*) FROM user_objects
WHERE object_type LIKE ‘PACKAGE%’
AND status = ‘INVALID’;
7. A procedure includes the following code:
CURSOR loc_curs IS SELECT location_id, city, country_id FROM locations;
Which of the following changes to the LOCATIONS table will allow the procedure to be recompiled successfully without editing its code? (Choose two.)
♦RENAME locations TO new_locations;
♦ALTER TABLE locations ADD (climate VARCHAR2(30)); (*)
♦ALTER TABLE locations DROP COLUMN city;
♦ALTER TABLE locations DROP COLUMN postal_code; (*)
8. Examine the following code:
CREATE FUNCTION deptfunc
RETURN NUMBER IS
v_count NUMBER(6);
BEGIN
SELECT COUNT(*) INTO v_count FROM departments;
RETURN v_count;
END;
Which of the following will display the dependency between DEPTFUNC and DEPARTMENTS?
♦SELECT name, type
FROM user_dependencies
WHERE name IN (‘DEPTFUNC’,’DEPARTMENTS’);
♦SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = ‘DEPARTMENTS’
AND referenced_type = ‘TABLE’;
(*)
♦SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = ‘DEPARTMENTS’
AND type = ‘TABLE’;
♦SELECT object_name, object_type
FROM user_objects
WHERE object_name IN (‘DEPARTMENTS’,’DEPTFUNC’)
AND referenced = ‘YES’;
9. Procedure B has a new IN OUT parameter added to it. It compiles successfully. In Signature mode, Procedure A, which is dependent on remote Procedure B, will fail. True or False?
♦True (*)
♦False
10. Which command changes the dependency mode to SIGNATURE in the current session?
♦ALTER SESSION MAKE REMOTE_DEPENDENCIES_MODE = SIGNATURE
♦ALTER SYSTEM MAKE REMOTE_DEPENDENCIES_MODE = SIGNATURE
♦ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE (*)
♦ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE
11. A change in a remote referenced subprogram is automatically recorded as invalid if its base object changes and that new status is relayed to the dependent object’s status and automatically marked as invalid. True or False?
♦True
♦False (*)
12. Procedure B has the ZERO_DIVIDE pre-defined exception added to its EXCEPTION section. It is compiled successfully. In Timestamp Mode, Procedure A, which is dependent on remote Procedure B, will compile and execute successfully. True or False?
♦True
♦False (*)
13. When a subprogram is compiled/recompiled, a time stamp is automatically recorded in the local data dictionary. Switching the dependency mode to SIGNATURE means that only the signature value is recorded. True or False?
♦True
♦False (*)
14. You created a package named pkg1. The code is approximately 90,000 characters. What is the statement that you use to obfuscate this package in the database?
♦DBMS_DML.CREATE_WRAPPED (pkg1);
♦WRAP pkg1.sql
♦DBMS_DML.CREATE_WRAPPED (‘CREATE OR REPLACE PACKAGE BODY pkg1…);
♦WRAP INAME=pkg1.sql (*)
♦DBMS_DML.CREATE_WRAP (pkg1);
15. Which is NOT a benefit of obfuscation?
♦Source code is not loaded in the data dictionary.
♦Source code is hidden from all users.
♦Source code is visible to the owner. (*)
♦Protection for intellectual property is provided.
16. What are the two methods for obfuscating PL/SQL subprograms? (Choose two)
♦DBMS_DDL.CREATE_WRAPPED (*)
♦DBMS_DDL.WRAP
♦DBMS_DML.CREATE_WRAPPED
♦PL/SQL wrapper utility program (*)
♦SQL wrapper utility program
17. In the USER_ERRORS data dictionary view, if an error is prefixed with “Warning,” the command completes but has a side effect the user needs to know about. For all other errors, the command terminates abnormally. True or False?
♦True (*)
♦False
18. What does the following statement do?
DBMS_WARNING.ADD_WARNING_SETTING_CAT(‘PERFORMANCE’,’ENABLE’,’SESSION’);
♦Enables the PERFORMANCE warning category, setting other category settings to disabled.
♦Enables the PERFORMANCE warning category, leaving other category settings unchanged.
♦Add the PERFORMANCE warning category into a PL/SQL variable.
♦Disables all warning categories, then enables the PERFORMANCE category.
♦Enables the PERFORMANCE warning category, leaving other category settings unchanged, for the current session. (*)
19. Which term best describes the action below:
A PL/SQL program compiles successfully, but contains some code that causes performance to be less than optimal.
♦Error
♦Warning (*)
20. In order to use the deterministic functions in Oracle version 11, you can use the following sample code to test for the Oracle version before compiling that section. True or False?
CREATE OR REPLACE FUNCTION myfunc
RETURN NUMBER
$IF DBMS_DB_VERSION.VERSION >= 11 $THEN
DETERMINISTIC
$END
IS BEGIN
— body of function
END myfunc;
♦True (*)
♦False
21. To include selections of code for compilation based on user-defined values, use the PLSQL_CCFLAGS parameters. True or False?
♦True (*)
♦False
22. Identify some benefits of using conditional compilation. (Choose two)
♦Use new features with the latest database release and disable them with older database versions (*)
♦Speed up the compilation time of a lengthy PL/SQL subprogram.
♦Determine initialization values during start up of a database session.
♦Activate debugging or tracing statements in the development environment (*)
23. What is the name of the column used to identify the PLSQL_OPTIMIZE_LEVEL in the data dictionary?
♦OPTIMIZE_LEVEL
♦PLSQL_OPTIMIZE_LEVEL (*)
♦PLSQL_CODE_TYPE
♦PLSQL_LEVEL
♦USER_PLSQL_OPTIMIZE
24. Native compilation always runs faster; therefore SQL statements in PL/SQL will always run faster, also. True or False?
♦True
♦False (*)
25. To determine the current setting for PLSQL_OPTIMIZE_LEVEL, query the data dictionary view USER_PLSQL_OBJECTS_SETTING. True or False?
♦True
♦False (*)
InfoCad Blog 