PL/SQL Packages Tutorial: Interfaces, Bodies and Session State
A PL/SQL package groups related declarations and implementations behind a database interface. Design its specification for callers, place implementation details in the body and consider initialization, dependencies and session state before treating the package as a stateless collection of functions.
Here's a popular PL/SQL Packages video — watch it, then summarize it with one click. Or paste any other YouTube link below.
— or paste your own YouTube link —
Want to keep the key points from a YouTube video? Paste the link below to turn it into structured notes.
Why Use PL/SQL Packages Tutorial: Interfaces, Bodies and Session State
- Define a public package specification
- Implement routines in the package body
- Hide internal helper declarations
- Understand package initialization behavior
- Account for state held by a session
Frequently Asked Questions
What belongs in the package specification?
Declarations intended for callers, such as public routines and supported types, belong in the specification. Exposing a declaration creates an interface dependency that should be considered before changing its name or signature.
Can the package body contain private routines?
The body can declare implementation details not exposed through the specification. Private helpers support organization without requiring callers to depend on every internal step used to implement a public operation.
Why does package state matter with pooled sessions?
State can belong to the database session rather than a single application request. A connection reused by another request can therefore retain relevant state unless the application and database lifecycle manage it appropriately.
When does package initialization run?
Initialization follows Oracle's package instantiation rules within the applicable session context. It should not be assumed to run afresh on every procedure call or every HTTP request using a pooled database connection.
How should package changes consider dependent callers?
Compare the public contract and dependency implications before deployment, and test affected application paths. A successful body compilation does not establish that every caller still receives the expected types, values and side effects.