Gain insight into the structure and functionality of PL/SQL packages, focusing on package specification and body declarations. This quiz assesses understanding of key definitions, syntax, visibility rules, and best practices in working with PL/SQL packages.
Which part of a PL/SQL package contains the public declarations of procedures, functions, variables, and types that can be accessed by other programs?
Explanation: The package specification houses all public declarations accessible from outside the package. The package body implements the routine logic, not public declarations. A trigger is unrelated to package declarations, and an anonymous block does not provide reusable, named programmatic structures like a package specification.
Where should the full implementation (code) of a procedure defined in the package specification be placed?
Explanation: The package body contains the full definitions and executable code for procedures and functions listed in the specification. Only their headers appear in the specification. A database view and sequence are unrelated and cannot contain PL/SQL code. The specification contains declarations, not implementations.
Which of the following can be declared inside a package specification?
Explanation: Public constants can be declared in a package specification, making them accessible outside the package. Private procedures are only declared in the body, not the specification. Table storage is defined in SQL DDL, not within a package, and SQL*Plus commands are not part of valid PL/SQL code.
Which element can be declared only in the package body and never in the package specification?
Explanation: Private helper functions are declared exclusively in the package body and are not visible outside the package. Global variables and cursor specifications, if needed externally, go in the specification. While exceptions can be public or private, public ones must be declared in the specification.
If a PL/SQL package contains only global constants and no procedure or function implementations, what can be omitted?
Explanation: If the package only holds global constants or declarations without executable code, the package body can be omitted. A specification is required for any package; exception handling cannot be omitted from procedures where needed, and public variables refer to specification, not the body.
When you modify the package specification, what must be done to keep the package valid?
Explanation: After modifying a package specification, the body must be recompiled to reflect changes and avoid invalid objects. Altering the body alone won't resolve dependency issues. Disconnecting or restarting the database has no effect on package validity. Only recompiling ensures consistency.
How are public procedures declared in a package specification accessed from outside the package?
Explanation: Public procedures are accessed with the qualified name: package_name.procedure_name. Using only the procedure name or adding parentheses is insufficient for package-scoped procedures. 'Spec.body.procedure_name' is not valid syntax in PL/SQL.
Why is it considered good practice to declare only headers in the package specification and place the code in the package body?
Explanation: Encapsulation ensures only the interface is exposed, while implementation is hidden in the body. Increasing package size is not a goal. Triggers cannot access private objects by this design. SQL*Loader commands are unrelated to package structure.
Where can an initialization section that executes once when a package is first referenced be placed?
Explanation: The initialization section appears last in the package body and runs once per session when the package is first invoked. The specification cannot include executable statements. Standalone procedures and database views do not serve this purpose.
If you change only the body of a PL/SQL package but keep the specification unchanged, what is the effect on dependent objects?
Explanation: Changing only the body does not affect dependent objects because their interface remains unchanged. Dropping or recompiling specifications or dependents is unnecessary. Dependencies are tied to the specification, not the body.