Download Semester 2 Final Exam PLSQL Test Bank 2025-2026. Questions & Correct Answers. Grade A and more Exams Advanced Data Analysis in PDF only on Docsity!
Semester 2 Final Exam PLSQL Test Bank
2025-2026. Questions & Correct
Answers. Grade A
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? – ANS True False () 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.) - ANSALTER TABLE locations DROP COLUMN city; ALTER TABLE locations DROP COLUMN postal_code; () RENAME locations TO new_locations; ALTER TABLE locations ADD (climate VARCHAR2(30)); (*)
A remote dependency is when a dependent object resides on a database on a separate node. True or False? - ANSTrue (*) False 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.) - ANSADD_EMP is directly dependent on EMP_VW () QUERY_EMP is directly dependent on ADD_EMP EMP_VW is directly dependent on EMPLOYEES () ADD_EMP is directly dependent on EMPLOYEES QUERY_EMP is directly dependent on EMPLOYEES (*)
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'; For PL/SQL code larger than 32,767 characters, you must use the wrap utility. True or False? - ANSTrue () False How would you determine the current Oracle database version? - ANSDBMS_DB_VERSION.VER_LE_ DBMS_DB_VERSION.VER_LE_ DBMS_DB_VERSION.VERSION (*) DBMS_DB_VERSION.RELEASE
Identify examples of benefits of using PLSQL_OPTIMIZE_LEVEL. (Choose three) - ANSControl what PL/SQL does with useless code () Copy compiled code from one subprogram into another subprogram () Separating compiled code so that separate units may be repeated as needed Modify source code to optimize frequently-used elements at the top Backward compatible with previous versions of the Oracle database () In Signature Mode, a procedure will not compile if the signatures of the remote dependencies do not match. True or False? - ANSTrue () False In the following example, what statement belongs in Line A? ALTER SESSION SET PLSQL_CCFLAGS = 'debug:true'; CREATE OR REPLACE PROCEDURE testproc IS BEGIN
False 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? - ANSTrue False (*) In this scenario, the following status is given for each procedure:
- Procedure A is local, executed, and invalidated because the remote Procedure B time stamp does not match the local time stamp for Procedure B
- Procedure A is recompiled. In Timestamp Mode, now Procedure A will execute successfully. True or False? - ANSTrue (*) False Inquiry directives are used to selectively include or exclude PL/SQL code based on values of pre-defined variables that are set using the PLSQL_CCFLAGS parameter. True or False? - ANSTrue
False () Native machine code PL/SQL will always execute faster than bytecode PL/SQL because it need not be interpreted at run time. True or False? - ANSTrue () False Obfuscation allows the owner to see the source code, but not the users to whom EXECUTE privileges have been granted. True or False? - ANSTrue False () Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) is recreated. emp_proc will be marked invalid and needs to be recompiled. True or False? - ANSTrue False ()
False () 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? - ANSTrue False ( To include selections of code for compilation based on user-defined values, use the PLSQL_CCFLAGS parameters. True or False? - ANSTrue (*) False To set the PLSQL_CODE_TYPE to its fastest execution speed, which command do you use? - ANSALTER SYSTEM SET PLSQL_CODE_TYPE=NATIVE; ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED; ALTER SYSTEM SET PLSQL_CODE_TYPE=2; ALTER SESSION SET PLSQL_CODE_TYPE = 2;
ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE; (*)
User ALICE owns a procedure show_emps which references table employees. Which of the following will generate information that shows this dependency? - ANSBEGIN deptree_fill('PROCEDURE','ALICE','SHOW_EMPS'); END; BEGIN deptree_fill('TABLE','ALICE','EMPLOYEES'); END; (*) BEGIN deptree_fill('ALICE','PROCEDURE','SHOW_EMPS'); END; BEGIN deptree_fill('TABLE','EMPLOYEES'); END; BEGIN deptree_fill('ALICE','TABLE','EMPLOYEES'); END;
DBMS_DML.CREATE_WRAPPED
DBMS_DDL.CREATE_WRAPPED (*)
What does the following statement do? DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','E NABLE','SESSION'); - ANSEnables the PERFORMANCE warning category, leaving other category settings unchanged. Enables the PERFORMANCE warning category, setting other category settings to disabled. 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. (*) When a table is dropped, all PL/SQL subprograms that reference the table are automatically dropped. True or False? - ANSTrue
False () When setting PLSQL_OPTIMIZE_LEVEL = 3, the compiled code will run more slowly, but it will work with older versions of the Oracle software. True or False? - ANSTrue False () When setting PLSQL_OPTIMIZE_LEVEL = 3, the compiled code will run more slowly, but it will work with older versions of the Oracle software. True or False? - ANSTrue False () When wrapping subprograms, the entire PL/SQL code must be included as an IN argument with data type CLOB to allow for any size program. True or False? - ANSTrue False () When wrapping subprograms, the entire PL/SQL code must be included as an IN argument with data type VARCHAR2 up to 32,767 characters. True or False? - ANSTrue (*) False
ALTER SESSION SET REMOTE_DEPENDENCIES_MODE =
SIGNATURE (*)
Which of the following database objects are created when the utldtree.sql script is run? (Choose three.) - ANSThe deptree table The utldtree table The deptree_temptab table () The deptree_fill procedure () The deptree and ideptree views (*) Which of the following is NOT created when the utldtree.sql script is run? - ANSThe DEPTREE view The DEPTREE_FILL procedure The DEPTREE_TEMPTAB table
The USER_DEPENDENCIES view (*) Which of the following techniques will make it more likely that an invalidated PL/SQL subprogram will recompile successfully? (Choose two.)
- ANSDeclaring record structures using %ROWTYPE () Including a column list with INSERT statements () SELECTing a list of column-names instead of using SELECT * Using a cursor FOR loop instead of opening and closing the cursor explicitly Which of the following will display the number of invalid package bodies in your schema? - ANSSELECT COUNT() FROM user_dependencies WHERE type = 'PACKAGE BODY' AND status = 'INVALID'; SELECT COUNT() FROM user_objects WHERE object_type LIKE 'PACKAGE%' AND status = 'INVALID';
Which pair of DBMS_WARNING commands would allow you to obtain the current settings and change and restore those settings in a PL/SQL subprogram? (Choose two) - ANSDBMS_WARNING.GET_WARNING_SETTING_STRING () DBMS_WARNING.GET_WARNING_STRING DBMS_WARNING.SET_WARNING_SETTING_STRING () DBMS_WARNING.ADD_WARNING_SETTING_CAT With remote dependencies, one master data dictionary that resides on one server identifies the status of all schema objects. True or False? - ANSTrue False (*) You created a package named pkg1. The code is approximately 90, characters. What is the statement that you use to obfuscate this package in the database? - ANSDBMS_DML.CREATE_WRAPPED ('CREATE OR REPLACE PACKAGE BODY pkg1...); WRAP pkg1.sql
DBMS_DML.CREATE_WRAPPED (pkg1); WRAP INAME=pkg1.sql (*) DBMS_DML.CREATE_WRAP (pkg1);
