Subprograms (procedures and functions) are the building blocks of modular and reusable PL/SQL code. Adhering to best practices when designing and implementing them is crucial for creating robust and maintainable applications.
Effective subprogram design is about more than just writing code that works. It involves considering factors like readability, maintainability, performance, and error handling. By following these best practices, you can create PL/SQL code that is easier to understand, debug, and extend, ultimately leading to more stable and efficient Oracle database applications. This section will cover key principles for writing high-quality PL/SQL subprograms, including naming conventions, parameter usage, error handling, and code reusability. Mastering these techniques is vital for any PL/SQL developer looking to optimize their Oracle database programming skills.
Example 1: Consistent Naming Conventions for Procedures
-- Procedure to insert a new employee record
-- Follows a clear naming convention: PKG_NAME_PROC_NAME
CREATE OR REPLACE PROCEDURE employees_pkg.insert_employee (
p_employee_id IN NUMBER, -- Input parameter for employee ID
p_first_name IN VARCHAR2, -- Input parameter for first name
p_last_name IN VARCHAR2, -- Input parameter for last name
p_email IN VARCHAR2 -- Input parameter for email address
)
AS
BEGIN
-- Insert statement to add a new employee
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (p_employee_id, p_first_name, p_last_name, p_email);
-- Commit the transaction if successful
COMMIT;
EXCEPTION
-- Exception handling for potential errors during insertion
WHEN OTHERS THEN
-- Log the error for debugging purposes
DBMS_OUTPUT.PUT_LINE('Error inserting employee: ' || SQLERRM);
-- Rollback the transaction in case of an error
ROLLBACK;
-- Re-raise the exception to propagate it to the calling environment
RAISE;
END insert_employee;
/
Explanation
This example demonstrates a consistent naming convention (PKG_NAME_PROC_NAME) for a procedure that inserts employee records. Using a standard prefix like insert_ clearly indicates the procedure's purpose. Parameters also follow a p_ prefix for easy identification. This improves code readability and maintainability, a key aspect of Oracle PL/SQL best practices, making it easier for developers to understand the flow and purpose of your database procedures.
Example 2: Consistent Naming Conventions for Functions
-- Function to calculate the total salary for a department
-- Follows a clear naming convention: PKG_NAME_FUNC_NAME
CREATE OR REPLACE FUNCTION departments_pkg.get_total_department_salary (
p_department_id IN NUMBER -- Input parameter for department ID
)
RETURN NUMBER
AS
v_total_salary NUMBER; -- Local variable to store the calculated total salary
BEGIN
-- Select statement to sum salaries for employees in the specified department
SELECT SUM(salary)
INTO v_total_salary
FROM employees
WHERE department_id = p_department_id;
-- Return the calculated total salary
RETURN v_total_salary;
EXCEPTION
-- Exception handling for potential errors
WHEN NO_DATA_FOUND THEN
-- Handle case where no employees are found in the department
RETURN 0;
WHEN OTHERS THEN
-- Log the error
DBMS_OUTPUT.PUT_LINE('Error calculating total salary: ' || SQLERRM);
-- Re-raise the exception
RAISE;
END get_total_department_salary;
/
Explanation
Similar to procedures, this example showcases a clear naming convention (get_total_department_salary) for a function that calculates a total. The get_ prefix signifies that the function retrieves data. Consistent naming for both functions and procedures greatly enhances code clarity and makes your PL/SQL programs more intuitive to navigate and understand, a core principle in Oracle development.
Example 3: Limiting Subprogram Scope and Single Responsibility Principle
-- Procedure responsible for validating employee data before insertion
-- Adheres to the Single Responsibility Principle
CREATE OR REPLACE PROCEDURE employees_pkg.validate_employee_data (
p_first_name IN VARCHAR2, -- Input parameter for first name
p_last_name IN VARCHAR2, -- Input parameter for last name
p_email IN VARCHAR2, -- Input parameter for email address
p_is_valid OUT BOOLEAN -- Output parameter indicating data validity
)
AS
BEGIN
-- Assume data is valid initially
p_is_valid := TRUE;
-- Check for NULL or empty first name
IF p_first_name IS NULL OR LENGTH(TRIM(p_first_name)) = 0 THEN
DBMS_OUTPUT.PUT_LINE('First name cannot be empty.');
p_is_valid := FALSE;
END IF;
-- Check for NULL or empty last name
IF p_last_name IS NULL OR LENGTH(TRIM(p_last_name)) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Last name cannot be empty.');
p_is_valid := FALSE;
END IF;
-- Basic email format validation (can be more robust)
IF p_email IS NULL OR NOT REGEXP_LIKE(p_email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') THEN
DBMS_OUTPUT.PUT_LINE('Invalid email format.');
p_is_valid := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error during data validation: ' || SQLERRM);
p_is_valid := FALSE;
RAISE;
END validate_employee_data;
/
-- Procedure to actually insert the employee, calling the validation procedure
CREATE OR REPLACE PROCEDURE employees_pkg.add_new_employee (
p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2
)
AS
v_is_valid BOOLEAN;
BEGIN
-- Call the validation procedure
employees_pkg.validate_employee_data(p_first_name, p_last_name, p_email, v_is_valid);
-- Proceed with insertion only if data is valid
IF v_is_valid THEN
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (p_employee_id, p_first_name, p_last_name, p_email);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee ' || p_first_name || ' ' || p_last_name || ' added successfully.');
ELSE
DBMS_OUTPUT.PUT_LINE('Employee data is invalid. Insertion aborted.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error adding new employee: ' || SQLERRM);
ROLLBACK;
RAISE;
END add_new_employee;
/
Explanation
This example demonstrates the Single Responsibility Principle. Instead of one large procedure handling both validation and insertion, we have validate_employee_data solely for validation and add_new_employee for the actual insertion, which calls the validation procedure. This makes each subprogram focused, easier to test, and more manageable, significantly improving the maintainability of your Oracle PL/SQL code.
Example 4: Robust Error Handling with EXCEPTION Block
-- Procedure to update an employee's salary with comprehensive error handling
CREATE OR REPLACE PROCEDURE employees_pkg.update_employee_salary (
p_employee_id IN NUMBER, -- Input parameter for employee ID
p_new_salary IN NUMBER -- Input parameter for new salary
)
AS
e_invalid_salary EXCEPTION; -- Custom exception for invalid salary
PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001); -- Associate custom exception with an error code
BEGIN
-- Check for valid salary range (e.g., salary cannot be negative)
IF p_new_salary < 0 THEN
RAISE e_invalid_salary; -- Raise custom exception if salary is invalid
END IF;
-- Update the employee's salary
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
-- Check if any rows were updated
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID: ' || p_employee_id);
ELSE
DBMS_OUTPUT.PUT_LINE('Salary updated for employee ID: ' || p_employee_id);
COMMIT; -- Commit only if update was successful
END IF;
EXCEPTION
WHEN e_invalid_salary THEN
-- Handle the custom invalid salary exception
DBMS_OUTPUT.PUT_LINE('Error: New salary cannot be negative.');
ROLLBACK;
WHEN NO_DATA_FOUND THEN
-- This block might not be hit if SQL%ROWCOUNT is checked, but good for other SELECTs
DBMS_OUTPUT.PUT_LINE('Employee with ID ' || p_employee_id || ' not found for update.');
ROLLBACK;
WHEN DUP_VAL_ON_INDEX THEN
-- Example for unique constraint violation (though less likely on salary update)
DBMS_OUTPUT.PUT_LINE('Error: Duplicate value encountered (e.g., on unique index).');
ROLLBACK;
WHEN OTHERS THEN
-- Catch-all for any other unhandled exceptions
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLCODE || ' - ' || SQLERRM);
ROLLBACK; -- Always rollback on error
RAISE; -- Re-raise the exception to the calling environment
END update_employee_salary;
/
Explanation
This example showcases robust error handling using the EXCEPTION block. It includes specific handlers for custom exceptions (e_invalid_salary), built-in exceptions (NO_DATA_FOUND), and a generic WHEN OTHERS handler. This structured approach to error management ensures that your PL/SQL subprograms gracefully handle unexpected situations, preventing application crashes and providing informative error messages, crucial for reliable Oracle database development.
Example 5: Using Parameters for Flexibility and Reusability
-- Procedure to log application events to a custom logging table
CREATE OR REPLACE PROCEDURE system_log_pkg.log_event (
p_event_type IN VARCHAR2, -- Type of event (e.g., 'INFO', 'WARNING', 'ERROR')
p_event_message IN VARCHAR2, -- Detailed message of the event
p_user_id IN NUMBER DEFAULT NULL -- Optional: User who triggered the event
)
AS
PRAGMA AUTONOMOUS_TRANSACTION; -- Allows logging to commit independently
BEGIN
-- Insert the event details into the logging table
INSERT INTO application_logs (log_id, event_type, event_message, log_timestamp, user_id)
VALUES (log_sequence.NEXTVAL, p_event_type, p_event_message, SYSTIMESTAMP, p_user_id);
-- Commit the log entry (independent transaction)
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Log the logging error itself (if possible) or just display
DBMS_OUTPUT.PUT_LINE('Error logging event: ' || SQLERRM);
-- Rollback the autonomous transaction in case of error
ROLLBACK;
END log_event;
/
-- How to call the logging procedure
BEGIN
-- Log an informational message
system_log_pkg.log_event('INFO', 'Application started successfully.');
-- Log a warning message with user ID
system_log_pkg.log_event('WARNING', 'Low disk space detected on server.', 101);
-- Log an error message
system_log_pkg.log_event('ERROR', 'Failed to connect to external service.');
-- Simulate an error scenario where logging is used
DECLARE
v_dummy NUMBER;
BEGIN
SELECT 1/0 INTO v_dummy FROM DUAL; -- This will raise a division by zero error
EXCEPTION
WHEN OTHERS THEN
-- Log the error using the reusable procedure
system_log_pkg.log_event('ERROR', 'Division by zero error occurred: ' || SQLERRM, USER_ID); -- Assuming USER_ID is available
RAISE;
END;
END;
/
Explanation
This example demonstrates the power of parameters in creating flexible and reusable subprograms. The log_event procedure accepts parameters for event_type, event_message, and an optional user_id, allowing it to be used for various logging scenarios throughout an application. This reusability is a cornerstone of efficient PL/SQL development, reducing code duplication and making your Oracle applications more maintainable and scalable.
NOCOPY Hint
The NOCOPY hint is a performance optimization used with OUT and IN OUT parameters in PL/SQL subprograms. It tells the PL/SQL engine to pass the parameter by reference rather than by value, potentially improving performance for large data structures.
By default, OUT and IN OUT parameters in PL/SQL are passed by value. This means that when a subprogram is called, a local copy of the parameter is created for IN OUT parameters, and a local copy is also created for OUT parameters to hold the result before copying it back to the calling environment. For large collections, records, or LOBs, this copying process can incur significant overhead, impacting the performance of your PL/SQL code. The NOCOPY hint instructs the PL/SQL engine to avoid this copying whenever possible, allowing the subprogram to directly access the memory location of the actual parameter in the calling environment. This can lead to substantial performance gains, especially in data-intensive Oracle applications. However, it's crucial to understand the implications, as NOCOPY can introduce unexpected behavior in certain error scenarios, which we will explore in the examples.
Example 1: Basic Usage of NOCOPY with a Large Collection
-- Define a large collection type
CREATE OR REPLACE PACKAGE employee_data_types AS
TYPE employee_names_tbl IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
END employee_data_types;
/
-- Procedure demonstrating NOCOPY for an OUT parameter
CREATE OR REPLACE PROCEDURE process_large_data (
p_input_data IN employee_data_types.employee_names_tbl, -- Input collection
p_output_data OUT NOCOPY employee_data_types.employee_names_tbl -- Output collection with NOCOPY
)
AS
BEGIN
-- Simulate processing: copy input to output and add some entries
p_output_data := p_input_data;
p_output_data(p_output_data.COUNT + 1) := 'New Employee 1';
p_output_data(p_output_data.COUNT + 1) := 'New Employee 2';
DBMS_OUTPUT.PUT_LINE('Inside procedure: p_output_data count = ' || p_output_data.COUNT);
END process_large_data;
/
-- Test block to observe NOCOPY behavior
SET SERVEROUTPUT ON;
DECLARE
v_input_list employee_data_types.employee_names_tbl;
v_output_list employee_data_types.employee_names_tbl;
BEGIN
-- Populate input list
FOR i IN 1..10000 LOOP
v_input_list(i) := 'Employee_' || i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Before call: v_input_list count = ' || v_input_list.COUNT);
DBMS_OUTPUT.PUT_LINE('Before call: v_output_list count = ' || v_output_list.COUNT);
-- Call the procedure with NOCOPY
process_large_data(v_input_list, v_output_list);
DBMS_OUTPUT.PUT_LINE('After call: v_input_list count = ' || v_input_list.COUNT);
DBMS_OUTPUT.PUT_LINE('After call: v_output_list count = ' || v_output_list.COUNT);
-- Check if the actual parameter was directly modified (if NOCOPY was effective)
-- In this simple case, p_input_data is IN, so v_input_list remains unchanged.
-- The effect of NOCOPY is on the p_output_data and v_output_list.
-- If an error occurred inside the procedure with NOCOPY, v_output_list might be partially updated.
END;
/
Explanation
This example demonstrates the basic usage of NOCOPY with an OUT parameter that is a large collection. By specifying NOCOPY for p_output_data, the PL/SQL engine attempts to pass the collection by reference, avoiding a full copy when returning the result. This can lead to performance improvements for your Oracle PL/SQL programs, especially when dealing with substantial data structures, making your database operations more efficient.
Example 2: NOCOPY with IN OUT Parameter
-- Procedure demonstrating NOCOPY for an IN OUT parameter
CREATE OR REPLACE PROCEDURE modify_collection_in_place (
p_data_list IN OUT NOCOPY employee_data_types.employee_names_tbl -- IN OUT parameter with NOCOPY
)
AS
BEGIN
-- Modify the collection directly
IF p_data_list.EXISTS(1) THEN
p_data_list(1) := 'Modified First Item';
END IF;
p_data_list(p_data_list.COUNT + 1) := 'Added New Item';
DBMS_OUTPUT.PUT_LINE('Inside procedure: p_data_list count = ' || p_data_list.COUNT);
END modify_collection_in_place;
/
-- Test block
SET SERVEROUTPUT ON;
DECLARE
v_my_list employee_data_types.employee_names_tbl;
BEGIN
-- Populate the list
FOR i IN 1..5 LOOP
v_my_list(i) := 'Original Item ' || i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Before call: v_my_list count = ' || v_my_list.COUNT);
DBMS_OUTPUT.PUT_LINE('Before call: v_my_list(1) = ' || v_my_list(1));
-- Call the procedure
modify_collection_in_place(v_my_list);
DBMS_OUTPUT.PUT_LINE('After call: v_my_list count = ' || v_my_list.COUNT);
DBMS_OUTPUT.PUT_LINE('After call: v_my_list(1) = ' || v_my_list(1));
DBMS_OUTPUT.PUT_LINE('After call: v_my_list(v_my_list.COUNT) = ' || v_my_list(v_my_list.COUNT));
END;
/
Explanation
Here, NOCOPY is used with an IN OUT parameter. When NOCOPY is effective, modifications made to p_data_list inside the procedure directly affect v_my_list in the calling environment, as no copy is made. This can yield significant performance gains when working with large collections, making your Oracle PL/SQL code more efficient.
Example 3: Understanding NOCOPY and Exceptions (Partial Results)
-- Procedure to demonstrate NOCOPY behavior with exceptions
CREATE OR REPLACE PROCEDURE process_and_raise_error (
p_data IN OUT NOCOPY employee_data_types.employee_names_tbl
)
AS
BEGIN
-- Modify some elements
IF p_data.EXISTS(1) THEN
p_data(1) := 'Modified by Error Proc 1';
END IF;
IF p_data.EXISTS(2) THEN
p_data(2) := 'Modified by Error Proc 2';
END IF;
-- Simulate an error after partial modifications
RAISE NO_DATA_FOUND; -- Or any other exception
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error caught inside procedure: ' || SQLERRM);
-- With NOCOPY, partial changes might be visible in the calling environment
RAISE; -- Re-raise the exception
END process_and_raise_error;
/
-- Test block to observe partial changes
SET SERVEROUTPUT ON;
DECLARE
v_original_list employee_data_types.employee_names_tbl;
BEGIN
-- Populate the list
FOR i IN 1..5 LOOP
v_original_list(i) := 'Item ' || i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Before call:');
FOR i IN v_original_list.FIRST .. v_original_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(' ' || v_original_list(i));
END LOOP;
BEGIN
-- Call the procedure with NOCOPY
process_and_raise_error(v_original_list);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error caught in calling block: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('After call (with error):');
-- Observe that partial modifications made before the error are visible
IF v_original_list.COUNT > 0 THEN
FOR i IN v_original_list.FIRST .. v_original_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE(' ' || v_original_list(i));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE(' List is empty or error during initialization.');
END IF;
END;
END;
/
Explanation
This crucial example illustrates a significant implication of NOCOPY: when an exception occurs within a NOCOPY procedure, any modifications made to the parameter before the exception is raised are visible in the calling environment. If NOCOPY were not used, the original value would be preserved. This "partial results" behavior is a key consideration when deciding whether to use NOCOPY for critical Oracle PL/SQL operations where data integrity is paramount.
Example 4: When NOCOPY Cannot Be Applied
-- Example to illustrate cases where NOCOPY is ignored by Oracle
-- (No actual code example to run, as NOCOPY will simply be ignored,
-- but a conceptual explanation is important)
-- NOCOPY is ignored if:
-- 1. The actual and formal parameters are different types (even implicitly convertible).
-- e.g., passing a VARCHAR2 to a NUMBER IN OUT NOCOPY parameter.
-- 2. The actual parameter is a component of a larger structure
-- e.g., passing my_record.my_field to a NOCOPY parameter.
-- 3. The actual parameter is a remote or composite variable
-- e.g., passing a collection from a database link.
-- 4. There's a character set conversion required.
-- 5. The actual parameter needs to be converted for a different client.
-- 6. The subprogram is part of a package specification, and the body is in a separate compilation unit
-- (less common now with single file packages).
-- If NOCOPY is ignored, the parameter is passed by value (default behavior),
-- which means performance may not be optimized, but the safe behavior (no partial updates on error)
-- is maintained. Oracle silently ignores the hint without raising an error.
-- Consider a scenario where NOCOPY might be ignored:
-- Assume a package spec:
-- CREATE OR REPLACE PACKAGE my_types_pkg AS
-- TYPE num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
-- PROCEDURE process_numbers (p_nums IN OUT NOCOPY num_table);
-- END;
-- /
-- And a procedure that tries to pass a different type (implicitly convertible, but still different)
-- DECLARE
-- v_varchar_nums my_varchar_table_type; -- Different type, even if elements are numbers
-- BEGIN
-- -- NOCOPY will likely be ignored here because v_varchar_nums is not my_types_pkg.num_table
-- my_types_pkg.process_numbers(v_varchar_nums);
-- END;
Explanation
This example highlights situations where the NOCOPY hint might be silently ignored by the Oracle PL/SQL engine. It's crucial to understand these limitations, as applying NOCOPY when it cannot be truly used by reference will not raise an error but also won't provide the expected performance benefits. Common scenarios include type mismatches (even if implicitly convertible), passing components of composite variables, or using remote parameters. Being aware of these exceptions is vital for effective Oracle PL/SQL performance tuning and debugging.
Example 5: Performance Impact Measurement (Conceptual)
-- This example provides a conceptual outline of how to measure performance impact.
-- Actual execution requires significant data and careful setup.
-- Define a large collection type for testing
CREATE OR REPLACE PACKAGE performance_test_pkg AS
TYPE large_data_array IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
-- Procedure with NOCOPY
PROCEDURE process_with_nocopy (
p_data IN OUT NOCOPY large_data_array
);
-- Procedure without NOCOPY
PROCEDURE process_without_nocopy (
p_data IN OUT large_data_array
);
END performance_test_pkg;
/
CREATE OR REPLACE PACKAGE BODY performance_test_pkg AS
PROCEDURE process_with_nocopy (
p_data IN OUT NOCOPY large_data_array
)
AS
v_temp VARCHAR2(100);
BEGIN
-- Simulate some work
FOR i IN 1 .. p_data.COUNT LOOP
v_temp := p_data(i); -- Accessing elements
p_data(i) := 'PROCESSED_' || p_data(i); -- Modifying elements
END LOOP;
END;
PROCEDURE process_without_nocopy (
p_data IN OUT large_data_array
)
AS
v_temp VARCHAR2(100);
BEGIN
-- Simulate some work
FOR i IN 1 .. p_data.COUNT LOOP
v_temp := p_data(i); -- Accessing elements
p_data(i) := 'PROCESSED_' || p_data(i); -- Modifying elements
END LOOP;
END;
END performance_test_pkg;
/
-- Test block to measure performance (conceptual)
SET SERVEROUTPUT ON;
DECLARE
v_data_size CONSTANT NUMBER := 100000; -- Number of elements
v_start_time PLS_INTEGER;
v_end_time PLS_INTEGER;
v_test_array performance_test_pkg.large_data_array;
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting performance test...');
-- Initialize test array
FOR i IN 1 .. v_data_size LOOP
v_test_array(i) := RPAD('Some large data string ' || i, 4000, 'X');
END LOOP;
-- Test with NOCOPY
v_start_time := DBMS_UTILITY.GET_TIME;
performance_test_pkg.process_with_nocopy(v_test_array);
v_end_time := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Time with NOCOPY: ' || (v_end_time - v_start_time) || ' hundredths of seconds.');
-- Re-initialize test array (important for fair comparison)
v_test_array.DELETE;
FOR i IN 1 .. v_data_size LOOP
v_test_array(i) := RPAD('Some large data string ' || i, 4000, 'X');
END LOOP;
-- Test without NOCOPY
v_start_time := DBMS_UTILITY.GET_TIME;
performance_test_pkg.process_without_nocopy(v_test_array);
v_end_time := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Time without NOCOPY: ' || (v_end_time - v_start_time) || ' hundredths of seconds.');
DBMS_OUTPUT.PUT_LINE('Performance test completed.');
END;
/
Explanation
This conceptual example outlines how to quantitatively measure the performance benefits of using the NOCOPY hint. By wrapping large data processing in two identical procedures (one with NOCOPY, one without) and using DBMS_UTILITY.GET_TIME to measure execution time, developers can empirically demonstrate the reduction in CPU and memory overhead. This approach is invaluable for validating performance optimizations in your Oracle PL/SQL applications and for identifying where NOCOPY can provide the most significant impact on database performance.
The DEFAULT Clause for Parameters
The DEFAULT clause in PL/SQL allows you to specify a default value for subprogram parameters. If a calling program omits an argument for such a parameter, the default value is automatically used. This enhances flexibility and reduces the need for overloaded subprograms or complex conditional logic.
Providing default values for parameters simplifies subprogram calls and makes your PL/SQL code more robust. It allows developers to call a procedure or function with fewer arguments, relying on sensible defaults for commonly used values. This significantly improves code readability and maintainability by reducing boilerplate code and making subprograms more adaptable to various scenarios without requiring multiple overloaded versions. It's a powerful feature for creating flexible and user-friendly Oracle PL/SQL APIs.
Example 1: Basic DEFAULT Clause for an Optional Parameter
-- Procedure to add an employee with an optional department ID
CREATE OR REPLACE PROCEDURE employees_pkg.add_employee_with_default (
p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_salary IN NUMBER,
p_department_id IN NUMBER DEFAULT 90 -- Default to department 90 (e.g., IT) if not provided
)
AS
BEGIN
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id)
VALUES (p_employee_id, p_first_name, p_last_name, p_email, p_salary, p_department_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Employee ' || p_first_name || ' ' || p_last_name || ' added to department ' || p_department_id || '.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error adding employee: ' || SQLERRM);
ROLLBACK;
RAISE;
END add_employee_with_default;
/
-- Test calls
SET SERVEROUTPUT ON;
BEGIN
-- Call 1: Provide all parameters, overriding the default
employees_pkg.add_employee_with_default(
p_employee_id => 208,
p_first_name => 'John',
p_last_name => 'Doe',
p_email => 'john.doe@example.com',
p_salary => 60000,
p_department_id => 50 -- Explicitly setting department to 50
);
-- Call 2: Omit p_department_id, using the default (90)
employees_pkg.add_employee_with_default(
p_employee_id => 209,
p_first_name => 'Jane',
p_last_name => 'Smith',
p_email => 'jane.smith@example.com',
p_salary => 75000
-- p_department_id is omitted, will default to 90
);
-- Call 3: Using named notation for clarity, still omitting default
employees_pkg.add_employee_with_default(
p_employee_id => 210,
p_first_name => 'Alice',
p_last_name => 'Brown',
p_email => 'alice.brown@example.com',
p_salary => 80000
);
END;
/
Explanation
This example demonstrates the most common use of the DEFAULT clause: providing a default value for an optional parameter (p_department_id). If the calling program omits the p_department_id argument, it automatically defaults to 90. This makes the procedure more flexible and reduces the need for multiple overloaded versions, streamlining your Oracle PL/SQL development and enhancing code readability.
Example 2: Using DEFAULT Clause with Functions
-- Function to calculate net salary, with a default tax rate
CREATE OR REPLACE FUNCTION payroll_pkg.calculate_net_salary (
p_gross_salary IN NUMBER,
p_tax_rate IN NUMBER DEFAULT 0.20 -- Default tax rate of 20%
)
RETURN NUMBER
AS
v_net_salary NUMBER;
BEGIN
v_net_salary := p_gross_salary * (1 - p_tax_rate);
RETURN v_net_salary;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error calculating net salary: ' || SQLERRM);
RETURN NULL;
END calculate_net_salary;
/
-- Test calls
SET SERVEROUTPUT ON;
DECLARE
v_salary1 NUMBER;
v_salary2 NUMBER;
v_salary3 NUMBER;
BEGIN
-- Call 1: Provide both gross salary and custom tax rate
v_salary1 := payroll_pkg.calculate_net_salary(5000, 0.15); -- 15% tax rate
DBMS_OUTPUT.PUT_LINE('Net Salary 1 (custom tax): ' || v_salary1);
-- Call 2: Omit tax rate, use default (20%)
v_salary2 := payroll_pkg.calculate_net_salary(5000);
DBMS_OUTPUT.PUT_LINE('Net Salary 2 (default tax): ' || v_salary2);
-- Call 3: Explicitly pass the default value
v_salary3 := payroll_pkg.calculate_net_salary(5000, 0.20);
DBMS_OUTPUT.PUT_LINE('Net Salary 3 (explicit default tax): ' || v_salary3);
END;
/
Explanation
This example extends the DEFAULT clause concept to a function (calculate_net_salary). The p_tax_rate parameter has a default value of 0.20. This allows callers to either specify a custom tax rate or rely on the standard 20% rate. This flexibility is invaluable for building versatile Oracle PL/SQL functions that can adapt to various business requirements.
Example 3: DEFAULT Clause with Mixed Parameter Types and Order
-- Procedure with mixed parameter types and default values
CREATE OR REPLACE PROCEDURE order_mgmt_pkg.create_order (
p_customer_id IN NUMBER,
p_order_date IN DATE DEFAULT SYSDATE, -- Default to current date
p_status IN VARCHAR2 DEFAULT 'PENDING', -- Default order status
p_shipping_type IN VARCHAR2 DEFAULT 'STANDARD',
p_discount_code IN VARCHAR2 DEFAULT NULL -- Optional, no default value
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Creating order for Customer ID: ' || p_customer_id);
DBMS_OUTPUT.PUT_LINE(' Order Date: ' || TO_CHAR(p_order_date, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE(' Status: ' || p_status);
DBMS_OUTPUT.PUT_LINE(' Shipping Type: ' || p_shipping_type);
IF p_discount_code IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(' Discount Code: ' || p_discount_code);
END IF;
-- Simulate order insertion
-- INSERT INTO orders (...) VALUES (...);
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating order: ' || SQLERRM);
RAISE;
END create_order;
/
-- Test calls using positional and named notation
SET SERVEROUTPUT ON;
BEGIN
-- Call 1: Minimum required parameters (positional)
order_mgmt_pkg.create_order(1001);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 2: Skipping a default parameter using named notation
order_mgmt_pkg.create_order(
p_customer_id => 1002,
p_shipping_type => 'EXPRESS' -- skips p_order_date and p_status, uses defaults
);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 3: Providing all parameters
order_mgmt_pkg.create_order(
p_customer_id => 1003,
p_order_date => TO_DATE('2025-12-25', 'YYYY-MM-DD'),
p_status => 'SHIPPED',
p_shipping_type => 'OVERNIGHT',
p_discount_code => 'SAVE20'
);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 4: Providing first few and then one later parameter using named notation
order_mgmt_pkg.create_order(1004, SYSDATE, p_discount_code => 'FREESHIP');
DBMS_OUTPUT.PUT_LINE('---');
-- Note: Once you use named notation, all subsequent parameters must also use named notation.
-- The following would be invalid:
-- order_mgmt_pkg.create_order(1005, p_status => 'COMPLETED', 'EXPRESS');
END;
/
Explanation
This example illustrates the flexibility of the DEFAULT clause with multiple parameters of different data types. It also highlights the ability to skip parameters with default values when using named notation in your Oracle PL/SQL calls. This allows for highly customizable subprogram interfaces, reducing the complexity of calls while maintaining full functionality. Remember the rule: once you use named notation, all subsequent parameters in the call must also use named notation.
Example 4: Using NULL as a Default Value
-- Procedure to update employee contact information, with optional phone and mobile numbers
CREATE OR REPLACE PROCEDURE employees_pkg.update_contact_info (
p_employee_id IN NUMBER,
p_email IN VARCHAR2,
p_phone_number IN VARCHAR2 DEFAULT NULL, -- Default to NULL if not provided
p_mobile_number IN VARCHAR2 DEFAULT NULL -- Default to NULL if not provided
)
AS
BEGIN
UPDATE employees
SET
email = p_email,
phone_number = NVL(p_phone_number, phone_number), -- Update only if not NULL
mobile_number = NVL(p_mobile_number, mobile_number) -- Update only if not NULL
WHERE employee_id = p_employee_id;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('Employee ID ' || p_employee_id || ' not found.');
ELSE
COMMIT;
DBMS_OUTPUT.PUT_LINE('Contact info updated for Employee ID: ' || p_employee_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error updating contact info: ' || SQLERRM);
ROLLBACK;
RAISE;
END update_contact_info;
/
-- Test calls
SET SERVEROUTPUT ON;
BEGIN
-- Assume employee 208 exists from previous examples
-- Call 1: Update email and set phone number to NULL (explicitly)
employees_pkg.update_contact_info(208, 'new.john.doe@example.com', NULL, NULL);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 2: Update email, keep existing phone and mobile (omit parameters)
employees_pkg.update_contact_info(209, 'new.jane.smith@example.com');
DBMS_OUTPUT.PUT_LINE('---');
-- Call 3: Update email and phone number, keep existing mobile
employees_pkg.update_contact_info(210, 'alice.b@example.com', '555-1234');
DBMS_OUTPUT.PUT_LINE('---');
-- Call 4: Update email and mobile number, keep existing phone
employees_pkg.update_contact_info(
p_employee_id => 208,
p_email => 'john.doe.updated@example.com',
p_mobile_number => '999-8765'
);
END;
/
Explanation
This example demonstrates setting NULL as a default value for parameters. This is particularly useful for optional fields that might not always be provided. The NVL function is used within the UPDATE statement to ensure that if a parameter is passed as NULL (either explicitly or by default), the existing column value is retained rather than being overwritten with NULL. This design pattern for optional updates is a common and efficient practice in Oracle PL/SQL database programming.
Example 5: DEFAULT Clause with Expressions
-- Procedure to create a project with a calculated end date based on duration
CREATE OR REPLACE PROCEDURE project_mgmt_pkg.create_project (
p_project_name IN VARCHAR2,
p_start_date IN DATE DEFAULT TRUNC(SYSDATE), -- Default to today's date
p_duration_days IN NUMBER DEFAULT 30, -- Default to 30 days duration
p_project_manager_id IN NUMBER DEFAULT NULL,
p_end_date IN DATE DEFAULT TRUNC(SYSDATE) + 30 -- Default calculated based on start + duration (initial guess)
)
AS
v_actual_end_date DATE;
BEGIN
-- Re-calculate actual end date based on provided p_start_date and p_duration_days
v_actual_end_date := p_start_date + p_duration_days;
DBMS_OUTPUT.PUT_LINE('Creating Project: ' || p_project_name);
DBMS_OUTPUT.PUT_LINE(' Start Date: ' || TO_CHAR(p_start_date, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE(' Duration (Days): ' || p_duration_days);
DBMS_OUTPUT.PUT_LINE(' Calculated End Date: ' || TO_CHAR(v_actual_end_date, 'YYYY-MM-DD'));
DBMS_OUTPUT.PUT_LINE(' Project Manager ID: ' || NVL(TO_CHAR(p_project_manager_id), 'Not Assigned'));
-- In a real scenario, you would insert into a projects table
-- INSERT INTO projects (project_name, start_date, duration_days, project_manager_id, end_date)
-- VALUES (p_project_name, p_start_date, p_duration_days, p_project_manager_id, v_actual_end_date);
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating project: ' || SQLERRM);
RAISE;
END create_project;
/
-- Test calls
SET SERVEROUTPUT ON;
BEGIN
-- Call 1: Only project name, all others default
project_mgmt_pkg.create_project('Website Redesign');
DBMS_OUTPUT.PUT_LINE('---');
-- Call 2: Custom start date, default duration
project_mgmt_pkg.create_project(
p_project_name => 'Mobile App Development',
p_start_date => TRUNC(SYSDATE) + 7
);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 3: Custom duration
project_mgmt_pkg.create_project(
p_project_name => 'Database Migration',
p_duration_days => 60
);
DBMS_OUTPUT.PUT_LINE('---');
-- Call 4: All custom parameters
project_mgmt_pkg.create_project(
p_project_name => 'CRM Integration',
p_start_date => TO_DATE('2026-01-01', 'YYYY-MM-DD'),
p_duration_days => 90,
p_project_manager_id => 105
);
END;
/
Explanation
This example demonstrates using expressions within the DEFAULT clause. p_start_date defaults to TRUNC(SYSDATE) and p_end_date defaults to TRUNC(SYSDATE) + 30. This allows for dynamic default values based on system functions or calculations, providing even greater flexibility and automation in your PL/SQL subprograms. While p_end_date has a default, the actual v_actual_end_date is always calculated based on the potentially provided p_start_date and p_duration_days, illustrating how default expressions can work in conjunction with subsequent logic.
Overloading Subprograms
Overloading subprograms in PL/SQL means creating multiple procedures or functions with the same name within the same PL/SQL scope (e.g., a package), but with different parameter lists (number, data type, or order of parameters). This allows for more intuitive and flexible APIs, as a single subprogram name can handle various input scenarios.
Subprogram overloading is a powerful feature in Oracle PL/SQL that promotes code clarity and reusability. Instead of inventing unique names for procedures or functions that perform similar logical operations but accept different sets of arguments (e.g., insert_employee_by_id, insert_employee_by_name), you can use a single, descriptive name and let PL/SQL determine which specific implementation to call based on the arguments provided during the call. This significantly improves the maintainability and readability of your Oracle database applications by creating a more natural and intuitive programming interface. It's an essential technique for building robust and user-friendly PL/SQL packages.
Example 1: Overloading a Procedure for Different Input Types
-- Package to demonstrate procedure overloading for adding an item
CREATE OR REPLACE PACKAGE item_management_pkg AS
-- Overload 1: Add item by name and quantity
PROCEDURE add_item (
p_item_name IN VARCHAR2,
p_quantity IN NUMBER
);
-- Overload 2: Add item by item_id (primary key) and quantity
PROCEDURE add_item (
p_item_id IN NUMBER,
p_quantity IN NUMBER
);
END item_management_pkg;
/
CREATE OR REPLACE PACKAGE BODY item_management_pkg AS
-- Implementation of Overload 1
PROCEDURE add_item (
p_item_name IN VARCHAR2,
p_quantity IN NUMBER
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding item by name: ' || p_item_name || ', Quantity: ' || p_quantity);
-- Logic to find item_id from item_name and then add/update inventory
-- INSERT INTO inventory (item_id, quantity) VALUES (get_item_id(p_item_name), p_quantity);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error adding item by name: ' || SQLERRM);
RAISE;
END add_item;
-- Implementation of Overload 2
PROCEDURE add_item (
p_item_id IN NUMBER,
p_quantity IN NUMBER
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding item by ID: ' || p_item_id || ', Quantity: ' || p_quantity);
-- Logic to add/update inventory directly using item_id
-- INSERT INTO inventory (item_id, quantity) VALUES (p_item_id, p_quantity);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error adding item by ID: ' || SQLERRM);
RAISE;
END add_item;
END item_management_pkg;
/
-- Test calls
SET SERVEROUTPUT ON;
BEGIN
-- Call the first overload (by name)
item_management_pkg.add_item('Laptop', 5);
-- Call the second overload (by ID)
item_management_pkg.add_item(101, 10);
END;
/
Explanation
This example demonstrates overloading the add_item procedure within a package. We have two versions: one that accepts an item_name (VARCHAR2) and another that accepts an item_id (NUMBER). Both perform the logical action of "adding an item," but the PL/SQL compiler determines which one to execute based on the data types of the arguments provided. This makes your Oracle PL/SQL API more intuitive and user-friendly, allowing developers to interact with the same logical operation through different input parameters.
Example 2: Overloading a Function for Different Parameter Counts
-- Package to demonstrate function overloading for calculating area
CREATE OR REPLACE PACKAGE geometry_pkg AS
-- Overload 1: Calculate area of a circle
FUNCTION calculate_area (
p_radius IN NUMBER
) RETURN NUMBER;
-- Overload 2: Calculate area of a rectangle
FUNCTION calculate_area (
p_length IN NUMBER,
p_width IN NUMBER
) RETURN NUMBER;
END geometry_pkg;
/
CREATE OR REPLACE PACKAGE BODY geometry_pkg AS
-- Implementation of Overload 1 (circle)
FUNCTION calculate_area (
p_radius IN NUMBER
) RETURN NUMBER
AS
BEGIN
RETURN (3.1415926535 * p_radius * p_radius); -- Pi * r^2
END calculate_area;
-- Implementation of Overload 2 (rectangle)
FUNCTION calculate_area (
p_length IN NUMBER,
p_width IN NUMBER
) RETURN NUMBER
AS
BEGIN
RETURN (p_length * p_width);
END calculate_area;
END geometry_pkg;
/
-- Test calls
SET SERVEROUTPUT ON;
DECLARE
v_circle_area NUMBER;
v_rectangle_area NUMBER;
BEGIN
-- Call the first overload (for circle, 1 parameter)
v_circle_area := geometry_pkg.calculate_area(5);
DBMS_OUTPUT.PUT_LINE('Area of circle with radius 5: ' || v_circle_area);
-- Call the second overload (for rectangle, 2 parameters)
v_rectangle_area := geometry_pkg.calculate_area(10, 7);
DBMS_OUTPUT.PUT_LINE('Area of rectangle with length 10, width 7: ' || v_rectangle_area);
END;
/
Explanation
This example shows function overloading where the number of parameters distinguishes the overloaded versions. The calculate_area function can compute the area of a circle (one parameter for radius) or a rectangle (two parameters for length and width). This allows for a single, consistent function name for a related concept (area calculation) while supporting different geometric shapes, a hallmark of well-designed Oracle PL/SQL APIs.
Example 3: Overloading with Different Parameter Orders
-- Package to demonstrate overloading based on parameter order
CREATE OR REPLACE PACKAGE data_formatter_pkg AS
-- Overload 1: Format date then string
PROCEDURE format_output (
p_date_val IN DATE,
p_string_val IN VARCHAR2
);
-- Overload 2: Format string then date
PROCEDURE format_output (
p_string_val IN VARCHAR2,
p_date_val IN DATE
);
END data_formatter_pkg;
/
CREATE OR REPLACE PACKAGE BODY data_formatter_pkg AS
-- Implementation of Overload 1 (date, string)
PROCEDURE format_output (
p_date_val IN DATE,
p_string_val IN VARCHAR2
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Format (Date, String): ' || TO_CHAR(p_date_val, 'YYYY-MM-DD') || ' - ' || p_string_val);
END format_output;
-- Implementation of Overload 2 (string, date)
PROCEDURE format_output (
p_string_val IN VARCHAR2,
p_date_val IN DATE
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Format (String, Date): ' || p_string_val || ' - ' || TO_CHAR(p_date_val, 'YYYY-MM-DD'));
END format_output;
END data_formatter_pkg;
/
-- Test calls
SET SERVEROUTPUT ON;
BEGIN
-- Call the first overload (date, string)
data_formatter_pkg.format_output(SYSDATE, 'Report Date');
-- Call the second overload (string, date)
data_formatter_pkg.format_output('Login Time', SYSDATE);
END;
/
Explanation
This example demonstrates overloading where the order of parameters (even if types are the same) dictates which overloaded version is called. The format_output procedure has two versions: one expecting a DATE followed by a VARCHAR2, and another expecting a VARCHAR2 followed by a DATE. This level of overloading provides fine-grained control over how your PL/SQL subprograms respond to different argument patterns, enhancing flexibility for your Oracle applications.
Example 4: Overloading with IN/OUT/IN OUT Parameters (Less Common for Distinction)
-- Package to demonstrate overloading where parameter mode is the distinguishing factor.
-- (Note: This is less common as the PL/SQL engine primarily looks at types and count,
-- but distinct modes can sometimes disambiguate if types and count are otherwise identical,
-- which is rare in practice).
CREATE OR REPLACE PACKAGE process_data_pkg AS
-- Overload 1: Process data IN and return a transformed OUT value
PROCEDURE process_data (
p_input IN NUMBER,
p_output OUT NUMBER
);
-- Overload 2: Process data IN OUT (modifies in place)
PROCEDURE process_data (
p_data IN OUT NUMBER
);
END process_data_pkg;
/
CREATE OR REPLACE PACKAGE BODY process_data_pkg AS
-- Implementation of Overload 1 (IN, OUT)
PROCEDURE process_data (
p_input IN NUMBER,
p_output OUT NUMBER
)
AS
BEGIN
p_output := p_input * 2; -- Double the input
DBMS_OUTPUT.PUT_LINE('Process (IN, OUT): Input=' || p_input || ', Output=' || p_output);
END process_data;
-- Implementation of Overload 2 (IN OUT)
PROCEDURE process_data (
p_data IN OUT NUMBER
)
AS
BEGIN
p_data := p_data + 10; -- Add 10 to the data
DBMS_OUTPUT.PUT_LINE('Process (IN OUT): Data modified to ' || p_data);
END process_data;
END process_data_pkg;
/
-- Test calls
SET SERVEROUTPUT ON;
DECLARE
v_val1 NUMBER := 5;
v_result NUMBER;
v_val2 NUMBER := 15;
BEGIN
-- Call the first overload (IN, OUT)
process_data_pkg.process_data(v_val1, v_result);
DBMS_OUTPUT.PUT_LINE('Result from IN, OUT call: ' || v_result);
-- Call the second overload (IN OUT)
process_data_pkg.process_data(v_val2);
DBMS_OUTPUT.PUT_LINE('Result from IN OUT call: ' || v_val2);
END;
/
Explanation
While the primary distinguishing factors for overloading are parameter data types and count, parameter modes (IN, OUT, IN OUT) can sometimes play a role in disambiguation if other factors are identical (which is rare to design for). This example shows two process_data procedures, one with separate IN and OUT parameters and another with a single IN OUT parameter. PL/SQL can distinguish between these based on how the parameters are defined in the call. This illustrates the depth of PL/SQL's overloading capabilities, contributing to more flexible Oracle database programming interfaces.
Example 5: Overloading with Different Record Types
-- Define two distinct record types
CREATE OR REPLACE PACKAGE employee_types_pkg AS
TYPE full_employee_rec IS RECORD (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
salary NUMBER
);
TYPE basic_employee_rec IS RECORD (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
END employee_types_pkg;
/
-- Package to demonstrate overloading with different record types
CREATE OR REPLACE PACKAGE employee_service_pkg AS
-- Overload 1: Display full employee details
PROCEDURE display_employee (
p_employee_rec IN employee_types_pkg.full_employee_rec
);
-- Overload 2: Display basic employee details
PROCEDURE display_employee (
p_employee_rec IN employee_types_pkg.basic_employee_rec
);
END employee_service_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_service_pkg AS
-- Implementation of Overload 1 (full record)
PROCEDURE display_employee (
p_employee_rec IN employee_types_pkg.full_employee_rec
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Full Employee Details ---');
DBMS_OUTPUT.PUT_LINE('ID: ' || p_employee_rec.employee_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || p_employee_rec.first_name || ' ' || p_employee_rec.last_name);
DBMS_OUTPUT.PUT_LINE('Email: ' || p_employee_rec.email);
DBMS_OUTPUT.PUT_LINE('Salary: ' || p_employee_rec.salary);
END display_employee;
-- Implementation of Overload 2 (basic record)
PROCEDURE display_employee (
p_employee_rec IN employee_types_pkg.basic_employee_rec
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Basic Employee Details ---');
DBMS_OUTPUT.PUT_LINE('ID: ' || p_employee_rec.employee_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || p_employee_rec.first_name || ' ' || p_employee_rec.last_name);
END display_employee;
END employee_service_pkg;
/
-- Test calls
SET SERVEROUTPUT ON;
DECLARE
v_full_emp employee_types_pkg.full_employee_rec;
v_basic_emp employee_types_pkg.basic_employee_rec;
BEGIN
-- Populate full employee record
v_full_emp.employee_id := 10;
v_full_emp.first_name := 'Manager';
v_full_emp.last_name := 'Oracle';
v_full_emp.email := 'manager.oracle@example.com';
v_full_emp.salary := 120000;
-- Populate basic employee record
v_basic_emp.employee_id := 20;
v_basic_emp.first_name := 'Junior';
v_basic_emp.last_name := 'Developer';
-- Call the first overload (with full_employee_rec)
employee_service_pkg.display_employee(v_full_emp);
-- Call the second overload (with basic_employee_rec)
employee_service_pkg.display_employee(v_basic_emp);
END;
/
Explanation
This advanced example demonstrates overloading procedures based on different user-defined RECORD types. Even though both overloads of display_employee take a single parameter, the distinct record types (full_employee_rec vs. basic_employee_rec) allow PL/SQL to differentiate them. This capability is extremely useful for building highly specialized and type-safe APIs in your Oracle PL/SQL applications, allowing for cleaner code and better data encapsulation.