Database Administration and PL/SQL Concepts

A transaction ends either when it is committed (changes saved) or rolled back (changes discarded). These are the only two ways a transaction completes. It does not end by only one of these actions.

$$dbtime$$ is the system variable used to reference database time in some database systems. $$time$$ and $$datetime$$ are not standard system variables for database time.

A view is a virtual table that does not occupy physical storage space - it stores only the query definition. Options A, C, and D describe legitimate uses of views: column security, simplifying complex queries, and hiding computational logic. Therefore, the statement that views occupy data storage space is the INCORRECT statement, making B the right choice.

Functions in Oracle/PLSQL must be invoked within a SQL statement like SELECT. Option A correctly embeds the function call in a SELECT statement, passing a column from the table. EXECUTE (B) is for procedures, not functions. Option C uses wrong function name. Option D lacks required SELECT wrapper - plain function calls aren't valid SQL.

Oracle automatically tracks dependency relationships between schema objects (e.g., a view depends on its base table). When a referenced object changes, dependent objects are marked invalid. Option A is false - invalid objects CAN be referenced (they're just marked invalid). Option C is false - not all objects have status. Option D is false - the view is USER_OBJECTS, not USER_STATUS.

By default, stored procedures in Oracle execute with the privileges of the owner (definer's rights), not the invoker (user A). Therefore, the dynamic SQL operations run under the creator's/owner's privileges.

PL/SQL does not support positional parameters with missing arguments like add_dept(, 'New York'). The stored answer correctly identifies this invalid call, but the question contains a typo ('Choose three' when only one option is correct or three are valid) and misspells the procedure name. However, since the option marked correct is indeed the only invalid call among the choices, the answer is valid.

PL/SQL packages can group related program units including procedures, functions, cursors, and variables. Option A (Cursor) is correct - cursors CAN be declared in a package specification. Triggers (B), Sequences (C), and Views (D) are standalone schema objects that CANNOT be part of packages.

To answer this question, the user needs to understand the concept of package variables in PL/SQL.

Package variables are variables that are declared in the package specification and can be accessed by any subprogram or procedure defined in the package. The state of a package variable after executing the package depends on its type.

Option A is correct. Package variables persist across transactions within a session. This means that the value of a package variable remains the same during the lifetime of the session, even if multiple transactions are executed. The value of the variable can be changed by any subprogram or procedure defined in the package.

Option B is incorrect. Package variables do not persist from session to session for the same user. When a user logs out of the session and logs back in, the package variable is reinitialized with its default value.

Option C is incorrect. Package variables persist across transactions within a session. The value of a package variable remains the same during the lifetime of the session, even if multiple transactions are executed.

Option D is incorrect. Package variables do not persist from user to user when the package is invoked. When a different user invokes the package, the package variable is reinitialized with its default value.

Therefore, the correct answer is:

The Answer is: A

DROP PROCEDURE removes standalone procedures. Packaged procedures cannot be dropped individually - you must drop or recreate the entire package body. Option A is correct. Options B and C are wrong because DROP PROCEDURE doesn't work on packaged procedures. Option D is nonsense - REUSE SETTINGS doesn't exist and ALTER PROCEDURE doesn't remove procedures.

The question asks which statement is FALSE. Options A, C, and D are TRUE - triggers with compilation errors are still created (as INVALID), and you CAN view errors via SHOW ERRORS or USER_ERRORS. Option B claims you can use USER_TRIGGERS to see compilation errors, which is FALSE - compilation errors are in USER_ERRORS, not USER_TRIGGERS.

DEPTREE_TEMPTAB is a temporary table used by Oracle's dependency tracking utility to store dependency relationships. USER_SOURCE contains PL/SQL source code, not dependencies. UTL_DEPTREE is a package for building dependency trees. USER_OBJECTS shows object status but not direct dependency mapping. DEPTREE_TEMPTAB (along with related views like DEPTREE) specifically tracks dependencies.

In dynamic SQL processing, the execute phase runs the actual SQL statement and returns the count of affected rows. For INSERT/UPDATE/DELETE, this is when data modification happens and row counts are returned. Selecting/ordering rows (A) happens for queries. Validity checking (B) is parse phase. Memory allocation (C) is define/prepare phase.

Trigger TYPE determines execution frequency. Row-level triggers fire once per ROW affected. Statement-level triggers fire once per STATEMENT regardless of rows. Trigger body (B) is the code executed. Trigger event (C) is what fires it (INSERT/UPDATE/DELETE). Trigger timing (D) is before/after/in stead-of.

USER_OBJECTS is the standard dictionary view containing LAST_DDL_TIME, which shows when any schema object (including procedures) was last compiled or modified. USER_PROCEDURES and USER_PROCS are not standard Oracle views. USER_PLSQL_UNITS may exist in some versions but USER_OBJECTS is the canonical source for compilation timestamps.

In strict relational database terminology, an attribute corresponds to a field - a single property or characteristic of an entity (like 'Name' or 'Age' in a Customer table). A record (incorrect Option A) is a row containing all attribute values for one entity. A table (incorrect Option C) is the entire collection of records. An entity (incorrect Option D) is the object being modeled (like Customer), not its individual properties.

A junction table resolves a many-to-many relationship (Students to Classes) by combining their primary identifiers. Using 'name' (for student) and 'class name' (for class) creates a composite key. The other choices contain irrelevant attributes like age, or single fields that cannot form a composite key.

Data Builder (such as SAP Datasphere Data Builder or generic database builders) is a data warehouse integration or modeling tool, but in classic data modeling lists, tools like ERwin, ER/Studio, and Visio are standard. empowER is a legacy data modeling tool. However, Data Builder is not a standard standalone entity-relationship modeling tool in the same sense as ERwin or ER/Studio.

Data Architecture's PRIMARY goal is to define data entities relevant to the enterprise - identifying what data exists, its structure, relationships, and meaning. While it may lead to designing logical data systems and physical storage, these are MEANS to an end, not the goal itself. Option D (All of the above) is incorrect because the question asks for the GOAL, not subsequent activities. The goal is semantic - defining data entities - not technical implementation.

A data architecture document focuses on high-level data structure, organization, and governance components like business data models, logical data models, data management processes, and entity-function matrices. Data interface requirements are more detailed technical specifications typically addressed during system or physical design phases, making them the correct exclusion from high-level architecture documents.