1. Procedure get_depts has been marked invalid because one of the objects it references has been altered. Which of the following statements are true? (Cho ose two.) Mark for Review (1) Points (Choose all correct answers) The procedure will be recompiled automatically the next time it is invok ed. The recompilation will always be successful. The procedure will be recompiled automatically the next time it is invok ed. The recompilation may or may not be successful. (*)
The procedure can be recompiled manually by: ALTER PROCEDURE get_depts COMPILE; (*)
The procedure can be recompiled manually by: ALTER PROCEDURE get_depts RECOMPILE; The procedure does not need to be recompiled.
Incorrect. Refer to Section 14 Lesson 1. 2. PL/SQL procedure A invokes procedure B, which in turn i nvokes procedure C, which references table T. If table T is dropped, which of th e following following statements statements is is true? true? Mark for for Review Review (1) Points C is invalid but A and B are still valid A, B and C are all invalid (*) B and C are invalid but A is still valid A, B and C are all still valid None of the above
Correct
3. Which data dictionary view shows information about refe rences and dependencies? Mark for Review (1) Points DEPTREE USER_DEPENDENCIES (*) USER_REFERENCES USER_LOCAL_DEPENDENCIES
Incorrect. Refer to Section 14 Lesson 1. 4. User ALICE owns a procedure show_emps which references table employees. Which of the following will generate information that shows thi s dependency? Mark for Review (1) Points BEGIN deptree_fill('TABLE','EMPLOYEES'); END; BEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS'); END; BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES'); END; BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES'); END; (*)
BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS'); END;
Incorrect. Refer to Section 14 Lesson 1. 5. A SELECT from DEPTREE produced the following output. >NESTED_LEVEL >TYPE >NAME >0 >TABLE >EMPLOYEES >1 >VIEW >EMP_VW >2 >PROCEDURE >ADD_EMP >1 >PROCEDURE >QUERY_EMP What dependencies does this show? (Choose three.)
Mark for Review (1) Points (Choose all correct answers) QUERY_EMP is directly dependent on EMPLOYEES (*) ADD_EMP is directly dependent on EMPLOYEES ADD_EMP is directly depedent on EMP_VW (*) QUERY_EMP is directly dependent on ADD_EMP EMP_VW is directly dependent on EMPLOYEES (*)
Incorrect. Refer to Section 14 Lesson 1. 6. Which of the following database objects are created whe n the utldtree.sql script is run? (Choose three.) Mark for Review (1) Points (Choose all correct answers) The utldtree table The deptree_temptab table (*) The deptree and ideptree views (*) The deptree table The deptree_fill procedure (*)
Incorrect. Refer to Section 14 Lesson 1. 7. The IDEPTREE view shows dependencies by indenting the l ines of output instead of by using a NESTED_LEVEL column. True or False? Mark for Review (1) Points True (*)
False
Incorrect. Refer to Section 14 Lesson 1. 8. A procedure includes the following code: SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE employee_id = 100; Which of the following changes to the employees table will allow the procedure t o be recompiled successfully ? (Choose two.) Mark for Review (1) Points (Choose all correct answers) The table is dropped but a public table exists with the same name and st ructure. (*) The table is dropped. A new column is added to the table. (*) The table name is changed to newemps. The first_name column is dropped from the table.
Correct 9. Which of the following will NOT help to minimize depend ency failures? (Choose two.) Mark for Review (1) Points (Choose all correct answers) SELECTing a list of column names instead of using SELECT * (*) Declaring records using the %ROWTYPE attribute Including a column list with INSERT statements Declaring scalar variables with NOT NULL if the corresponding table colu mn has a NOT NULL constraint (*)
Declaring scalar variables using the %TYPE attribute
Incorrect. Refer to Section 14 Lesson 1. 10. Package emp_pack contains two public procedures: get_em ps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The up d_emps package body code is now altered, and the package body (but not the packa ge specification) is recreated. emp_proc will be marked invalid and needs to be recompiled. True or False? Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 14 Lesson 1. 11. A procedure show_emps contains the following declaratio n: CURSOR emp_curs IS SELECT last_name, salary FROM employees; What will happen to the procedure if a new column is added to the employees tabl e? Mark for Review (1) Points The procedure will still be valid and execute correctly because it does not reference the added column. The procedure will automatically be dropped and must be recreated. The procedure will be marked invalid and must be recompiled before it ca n be reexecuted. (*) Users' privileges to execute the procedure will automatically be revoked .
Incorrect. Refer to Section 14 Lesson 1. 12. View dept_view is based on a select from table departme nts. Procedure show_dept contains code which selects from dept_view. Which of th e following statements are true? (Choose three.) Mark for Review
(1) Points (Choose all correct answers) departments is indirectly dependent on show_dept show_dept is directly dependent on dept_view (*) dept_view is directly dependent on departments (*) show_dept is indirectly dependent on departments (*) emp_view is directly dependent on show_dept
Incorrect. Refer to Section 14 Lesson 1. 13. Which of the following statements will show whether pro cedure myproc is valid or invalid? Mark for Review (1) Points SELECT status FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC'; (*)
SELECT status FROM USER_PROCEDURES WHERE procedure_name = 'MYPROC'; SELECT valid FROM USER_OBJECTS WHERE object_type = 'PROCEDURE' AND object_name = 'MYPROC'; SELECT * FROM deptree;
Incorrect. Refer to Section 14 Lesson 1. 14. A single PL/SQL subprogram such as a procedure can be b oth a referenced object and a dependent object. True or False? Mark for Review (1) Points True (*)
False
Correct 1. dependency mode. True or False? (1) Points
The Data Dictionary controls the remote Mark for Review
True False (*)
Correct 2.
In this scenario, the following status is given for each
procedure: - Procedure A is local and has a time stamp of 10 AM - Procedure B is remote and has a local time stamp of 5 AM and has a remote time stamp of 4 AM In Timestamp Mode, Procedure A will execute successfully at 11 AM. True or False ? Mark for Review (1) Points True False (*)
Correct 3. In this scenario, the following status is given for eac h procedure: - Procedure A is local, executed, and invalidated because the remot e Procedure B time stamp does not match the local time stamp for Procedure B - P rocedure A is recompiled. In Timestamp Mode, now Procedure A will execute succes sfully. True or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 14 Lesson 2.
4. With remote dependencies, one master data dictionary th at resides on one server identifies the status of all schema objects. True or Fa lse? Mark for Review (1) Points True False (*)
Correct 5. In Signature Mode, a procedure will not compile if the signatures of the remote dependencies do not match. True or False? Mark for Review (1) Points True (*) False
Correct 6. In Signature Mode, a compiled procedure is still valid if its dependent procedure has a parameter data type change from NUMBER to INTEG ER. Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 14 Lesson 2. 7. A remote dependency is when a dependent object resides on a database on a separate node. True or False? Mark for Review (1) Points True (*) False
Correct 8. Which statement for setting a database parameter is the d efault for remote dependency checking? Mark for Review (1) Points ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP (*) ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE ALTER SESSION REMOTE_DEPENDENCIES_MODE = TIMESTAMP ALTER SESSION REMOTE_DEPENDENCIES_MODE = SIGNATURE
Correct 1. When setting PLSQL_OPTIMIZE_LEVEL = 2, the compiled cod e will remove code and exceptions that can never be executed. True or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 15 Lesson 1. 2. he data dictionary? (1) Points 0,1,2,3 (*) 0,1,2,3,4 1,2,3 1,2,3,4
Correct
What are the valid values for PLSQL_OPTIMIZE_LEVEL in t Mark for Review
3. PLSQL_CODE_TYPE determines the type of code for PL/SQL code and for SQL statements, which is what speeds up the execution speed. True o r False? Mark for Review (1) Points True False (*)
Correct 4. To set the PLSQL_CODE_TYPE to its fastest execution spe ed, which command do you use? Mark for Review (1) Points ALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE; ALTER SYSTEM SET PLSQL_CODE_TYPE=2; ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; (*) ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED; ALTER SESSION SET PLSQL_CODE_TYPE = 2;
Incorrect. Refer to Section 15 Lesson 1. 5. Which are NOT examples of benefits of using PLSQL_OPTIM IZE_LEVEL. (Choose two) Mark for Review (1) Points (Choose all correct answers) Control what PL/SQL does with useless code. Combining compiled code from one subprogram into another subprogram. Separating compiled code so that separate units may be repeated as neede d. (*) Backward compatible with previous versions of the Oracle database.
Modify source code to optimize frequently-used elements at the top. (*)
Incorrect. Refer to Section 15 Lesson 1. 6. Which data dictionary view allows you to see the settin g for PLSQL_OPTIMIZE_LEVEL? Mark for Review (1) Points USER_PLSQL_OBJECTS USER_PLSQL_OPTIMIZE USER_PLSQL_OBJECT_SETTINGS (*) USER_OBJECT_SETTINGS USER_PLSQL_CODE_TYPE
Incorrect. Refer to Section 15 Lesson 1. 1. A warning in PL/SQL is the same as an error in PL/SQL, but can only be viewed through the USER_ERRORS data dictionary view. True or Fal se? Mark for Review (1) Points True False (*)
Correct 2. Which pair of DBMS_WARNING commands would allow you to obtain the current settings and change and restore those settings in a PL/SQL su bprogram? (Choose two) Mark for Review (1) Points (Choose all correct answers) DBMS_WARNING.SET_WARNING_SETTING_STRING (*) DBMS_WARNING.ADD_WARNING_SETTING_CAT
DBMS_WARNING.GET_WARNING_SETTING_STRING (*) DBMS_WARNING.GET_WARNING_STRING
Incorrect. Refer to Section 15 Lesson 2. 3. The informational warning level for PL/SQL compiled cod e identifies the code that may cause execution speed to be slow. True or False? Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 15 Lesson 2. 4. The two statements below are equivalent. True or False? DBMS_WARNING.ADD_WARNING_SETTING_CAT ('INFORMATIONAL','ENABLE','SESSION'); and ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:INFORMATIONAL'; Mark for Review (1) Points True (*) False
Correct 5. Which PL/SQL warning message identifies code that can ca use unexpected behavior or wrong results when executed? Mark for Review (1) Points INFORMATIONAL PERFORMANCE ALL
SEVERE (*) ERROR
Incorrect. Refer to Section 15 Lesson 2. 6. An error in PL/SQL is when the compiler does not procee d successfully and an error message is displayed. True or False? Mark for Review (1) Points True (*) False
Correct 1. Inquiry directives are used to selectiv ely include or exclude PL/SQL code based on values of pre-defined variables that are set using the PLSQL_CCFLAGS parameter. True or False? Mark for Review (1) Points True False (*)
Correct
ompilation. (1) Points
2. Identify the selection directives used in conditional c Mark for Review
$IF $THEN $ELSE $END $CCFLAG $$IF $$THEN $$ELSE $$ELSIF $$END $IF $THEN $ELSE $ELSIF $ENDIF $IF $THEN $ELSE $ELSIF $END (*) $$IF $$THEN $$ELSE $$END $$DEBUG
Correct 3. The value of DBMS_DB_VERSION.VER_LE_11 is TRUE when the version of the Oracle database is version 11 or greater. True or False? Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 15 Lesson 3. 4. You can choose which code to include in a PL/SQL progra m based on conditional compilation directives. True or False? Mark for Review (1) Points True (*) False
Correct 5. Conditional compilation allows you to include extra cod e to help with debugging, which can be removed once errors are resolved. True or False? Mark for Review (1) Points True (*) False
Correct 6. If the version and release of the Oracle database in us e is 10.2, what statement will allow syntax available in version 10.2 or later? Mark for Review (1) Points $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some act
ion $END $IF DBMS_DB_VERSION.VER_LE_10_2 $THEN -- some messaage $ELSE -- some act ion $END; $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some act ion $END (*) $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN -- some messaage $ELSE -- some act ion $END;
Correct 1. For PL/SQL code larger than 32,767 characters, you must use the wrap utility. True or False? Mark for Review (1) Points True (*) False
Correct 2. To create obfuscated code using the wrapper utility, det ermine the order in which to execute the following steps. A Connect to the database and execute the wrapped text file as a script to comp ile the wrapped code into the Data Dictionary. B Log into the database server computer. C Create a text file containing your complete unwrapped source code. D Execute WRAP to create a second text file containing the wrapped code. Mark for Review (1) Points A,B,C,D B,C,D,A (*) C,D,A,B C,A,B,D B,D,C,A
Incorrect. Refer to Section 15 Lesson 4. 3. To obfuscate the procedure my_proc, what statement shou ld be at Line A? BEGIN -- Line A ('CREATE OR REPLACE PROCEDURE mycleverproc (p_p aram1 IN NUMBER, p_param2 OUT NUMBER) IS BEGIN ... /* some clever but private co de here */ END mycleverproc;'); END; Mark for Review (1) Points DBMS_DML.CREATE_WRAP DBMS_DDL.CREATE_WRAP DBMS_DDL.CREATE_WRAPPED (*) DBMS_DDL.WRAPPED DBMS_DDL.WRAP_CODE
Incorrect. Refer to Section 15 Lesson 4. 4. Obfuscation allows the owner to see the source code, bu t not the users to whom EXECUTE privileges have been granted. True or False? Mark for Review (1) Points True False (*)
Incorrect. Refer to Section 15 Lesson 4. 5. When wrapping subprograms, the entire PL/SQL code must be included as an IN argument with data type VARCHAR2 up to 32,767 characters. T rue or False? Mark for Review (1) Points True (*) False
Incorrect. Refer to Section 15 Lesson 4.
6. One benefit of obfuscation is to protect intellectual p roperty written in PL/SQL. True or False? Mark for Review (1) Points True (*) False
Correct
