Oracle PL/SQL 1Z0-144 Certified practice tests

1. What is the primary use of PL/SQL in Oracle databases? A) To create and manage database schema objects B) As a scripting language for database administration tasks C) For performing complex calculations D) To write applications within the database that run at the client level

Correct Answer: A) To create and manage database schema objects

2. Which of the following is NOT a data type in PL/SQL? A) VARCHAR2 B) NUMBER C) RECORD D) BOOLEAN

Correct Answer: C) RECORD (Actually, RECORD is a composite data type in PL/SQL)

3. In PL/SQL, what is the purpose of the %ROWTYPE attribute? A) To define a custom record type based on a table B) To convert a collection to a variabl C) To create a cursor to fetch rows from a table D) To specify the precision and scale of a NUMBER variable

Correct Answer: A) To define a custom record type based on a table

4. What is the difference between a PL/SQL procedure and a function? A) A procedure can modify database data, while a function cannot B) A procedure returns data to the calling environment, while a function cannot C) A function performs an operation and returns a value, whereas a procedure does not return any value D) Procedures and functions are interchangeable; there is no difference

Correct Answer: C) A function performs an operation and returns a value, whereas a procedure does not return any value

5. Which of the following PL/SQL constructs allows you to execute dynamic SQL? A) DBMS_SQL package B) EXECUTE IMMEDIATE statement C) FOR loop D) WHILE loop

Correct Answer: B) EXECUTE IMMEDIATE statement

6. What is the purpose of the ACCESSIBLE BY clause in a PL/SQL package? A) To specify the privileges required to execute a package B) To restrict access to certain subprograms within the package C) To define constants and exceptions used by the package D) To declare package-level variables

Correct Answer: B) To restrict access to certain subprograms within the package

7. Which of the following is NOT a benefit of using packages in PL/SQL? A) Packages allow you to group related subprograms and data types B) Packages provide encapsulation and modularity of program code C) Packages can be used to perform DML operations directly on table columns D) Packages make your code easier for others to understand and maintain

Correct Answer: C) Packages can be used to perform DML operations directly on table columns (Actually, packages can include subprograms that perform DML operations on tables, but not directly on columns.)

8. What is the difference between a trigger and an autonomous transaction? A) A trigger always rolls back upon encountering an error, whereas an autonomous transaction does not B) An autonomous transaction is executed in response to certain database events, whereas a trigger performs actions autonomously C) A trigger performs operations in response to specific DDL or DML events, whereas an autonomous transaction does so without regard to the event that invoked it D) There is no difference; they are used for the same purposes

Correct Answer: C) A trigger performs operations in response to specific DDL or DML events, whereas an autonomous transaction does so without regard to the event that invoked it

9. Which of the following is a valid PL/SQL hint that can be specified at compile time? A) NOCOPY B) FAST_FOREIGN_KEYS C) PARALLEL_ENABLE D) ALL_ROWID

Correct Answer: A) NOCOopy (Note: The correct spelling is NOCOPY, not NOCOPY)

10. What must you do before executing a PL/SQL block that contains declarations and executable statements from multiple source code files (i.e., server-side program units compiled separately)? A) Use the CONCATENATE_SOURCE_FILES pragma in each source file B) Merge all source code files into a single unit before compilation C) Specify the WHITELIST_GROUP attribute for the program units D) Use the ACCESSIBLE BY clause to make the declarations visible to the executable statements

Correct Answer: B) Merge all source code files into a single unit before compilation (Note: In practice, this is typically handled by the database, but conceptually, you must ensure that all components are accessible to each other.)