PL/SQL ( Procedural Language/Structural Query Language ) is the extension of SQL ( Structured Query Language ) which is allowed to the developers for creating the procedural logic along with the Oracle database. The package is one of the main key componentss of PL/SQL development.
The PL/SQL package is the group of the related procedures, functions, variables, and other bundled constructs for providing the modularity and organized approach to application development. In PL/SQL, the package specification gives the blueprint of the functionalities that are provided by the PL/SQL package.
It is defined by the interface along with the PL/SQL code which interacts with the package. It works like an API ( Application Program Interface ) that defines how the external programs can interact with the software library.
Creating a package specification
Creating a package specification in PL/SQL defines the interface to the package and which functionalities are provided by the packages. The specification of the package works as the connection between the package and its users and defines the procedure, function, types, variables, and exceptions that are accessible in public.
Step-by-step process to creating a specification of the package
Step 1: Create Package specification
First, you can start the ‘CREATE PACKAGE‘ statement. The statement initiates the creation of the package specification and is followed by the package name.
The syntax for creating the package specification:
CREATE PACKAGE package_name ASStep 2: Declare global variables and constants
This step is optional, if the package requires any global constants or global variables, you can declare the constants and variables in the package specification. If you declare the variables and constants in the package specification, you can access these variables and constants throughout the package.
The syntax for declaring constants and variables:
g_variable NUMBER := 0;
constants CONSTANT VARCHAR2(20) := 'CONSTANT_VALUE';
Step 3: Declare public procedures and functions
In this step, you can define the procedures and functions that you will want to expose other PL/SQL code outside of the package. You can declare every procedure and every function and also with the return type and its parameter.
The syntax for declaring the procedures and functions:
PROCEDURE public_procedure(param1 IN NUMBER, param2 OUT VARCHAR2);
FUNCTION public_function(param IN VARCHAR2) RETURN NUMBER;
Step 4: End the package specification
At the end of the package specification, you need to close the package specification with the help of “END” keyword.
The syntax for closing the specification of the package:
END package_name;Compilation of a package
Compilation of the package in PL/SQL is the process of transforming the specification of the package and body of the package into the format which is Oracle database can be efficiently executed. This process verifies the syntax and the semantics of the PL/SQL code and resolves any references to the objects of other databases and it will generate the necessary executable code. Now we can discuss the steps which are involved in the package compilation.
Steps for compilation
- Parsing: The first step of compiling a package in PL/SQL is parsing. Parsing is nothing but, it is dividing the code into individual components like identifiers, keywords, and operators. It will check the syntax and the rules of the PL/SQL. In this step, you can know the syntax errors in the code like incorrect keywords and missing semicolons etc.
- Semantic Analysis: After completion of the parsing, the compiler will perform the semantic analysis on the code. Semantic analysis is nothing but, it checks the code for semantic errors like referencing the undefined variables or using the incorrect data types. It also checks the code that follows the rules and regulations of the PL/SQL language. If any semantic errors are found in the code, the compilation will fail and it will generate the error message.
- Dependency Resolution: The compiler will check the dependencies between the package which is ready to be compiled and objects of the other database. It involves checking the references to the tables, views, functions, procedures, types, or other packages. If any one of the referenced objects does not exist or is invalid in the code, the compilation will fail.
- Compilation of Package Specification: After the completion of the above processes such as parsing, semantic analysis, and dependency resolution, the package specification is successfully compiled. The compiler will verify the declarations of the public procedures, functions, types, variables, and exceptions which are specified in the package specification. It will generate an execution plan for every public function and procedure. After these steps, the database will execute the code efficiently.
- Compilation of Package Body: After the completion of the package specification compilation, the compiler will be ready to compile the body of the package. The compiler will generate the executable code for every procedure and function in the body of the package, and make sure that the implementation of the package body matches the declarations in the specification of the package.
- Error Handling: While compilation, if any errors occur like compilation errors, semantic errors, syntax errors, or unresolved references, the compilation will fail and display the error message on the screen. The error message contains the nature of the error and placed in the code, it will help the developers for identifying the issues and resolve them issues.
- Code Generation: After the completion of compilation, the compiler will generate the compiler code for the package. This code will be optimized for the execution by the database of the Oracle and make the package ready to use.
- Status Update: Finally, after completion of all the above steps, the status of the package is updated in the dictionary of the database. It indicates that the code has successfully compiled and is available for usage by other PL/SQL code.
Package State
The package state refers to the present status or a condition of the PL/SQL package within the session of the database. Understanding the package state plays a crucial role in managing the behavior and package lifecycle.
States of Package State
- Invalid State: If the definition contains syntax errors, semantic errors, or references to nonexistent objects, the package will be considered invalid. When the package is invalid state, it cannot be executed or used by the other PL/SQL code. Compilation errors or changes to the referenced objects can be caused by the invalid package.
- Valid State: When the package definition is syntactically and semantically correct, the package will considered to be valid. In this state, the package will be executed and used by the other PL/SQL code in the same database session. Whenever any referenced objects change or are invalid, the package must be recompiled to remain valid.
- Loaded State: During the execution of a database session, the package is loaded into a memory called a loaded state. This occurs when the PL/SQL code references the package for the first time in the session. The package remains loaded in the memory for the duration of the session, improving the performance by reducing the usage of repeated compilation and parsing.
- Unload State: If the package is not loaded into the memory, it is called an unloaded state. When the packages are referenced by the PL/SQL code, they are typically loaded into the memory. All loaded packages are systematically unloaded from the memory when a session ends.
Conclusion
In summary, the PL/SQL package specification worked as a vital component in the development of the database, providing the structured and modular approach to organizing and encapsulating code in the Oracle databases. The package specification allowed the developers to set related procedures, functions, types, variables, and exceptions together in a single logic unit. It is defined as the interface with other PL/SQL code that can interact with the package.