Basic Package Structure
1. Simple Package with a Procedure
CREATE OR REPLACE PACKAGE simple_package AS
PROCEDURE greet_user(p_name IN VARCHAR2);
END simple_package;
CREATE OR REPLACE PACKAGE BODY simple_package AS
PROCEDURE greet_user(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name);
END greet_user;
END simple_package;
2. Package with a Function
CREATE OR REPLACE PACKAGE math_package AS
FUNCTION add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER) RETURN NUMBER;
END math_package;
CREATE OR REPLACE PACKAGE BODY math_package AS
FUNCTION add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_num1 + p_num2;
END add_numbers;
END math_package;
3. Package with Multiple Procedures
CREATE OR REPLACE PACKAGE multi_procedure_package AS
PROCEDURE proc1(p_value IN NUMBER);
PROCEDURE proc2(p_name IN VARCHAR2);
END multi_procedure_package;
CREATE OR REPLACE PACKAGE BODY multi_procedure_package AS
PROCEDURE proc1(p_value IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Value: ' || p_value);
END proc1;
PROCEDURE proc2(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Name: ' || p_name);
END proc2;
END multi_procedure_package;
Advanced Package Features
4. Package with a Cursor
CREATE OR REPLACE PACKAGE cursor_package AS
CURSOR employee_cursor IS
SELECT employee_id, employee_name FROM employees;
PROCEDURE print_employees;
END cursor_package;
CREATE OR REPLACE PACKAGE BODY cursor_package AS
PROCEDURE print_employees IS
v_employee_record employee_cursor%ROWTYPE;
BEGIN
OPEN employee_cursor;
LOOP
FETCH employee_cursor INTO v_employee_record;
EXIT WHEN employee_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_record.employee_id || ', Name: ' || v_employee_record.employee_name);
END LOOP;
CLOSE employee_cursor;
END print_employees;
END cursor_package;
5. Package with Exception Handling
CREATE OR REPLACE PACKAGE error_handling_package AS
PROCEDURE risky_operation;
END error_handling_package;
CREATE OR REPLACE PACKAGE BODY error_handling_package AS
PROCEDURE risky_operation IS
BEGIN
-- Simulate an error
RAISE_APPLICATION_ERROR(-20001, 'Something went wrong!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END risky_operation;
END error_handling_package;
6. Package with Overloaded Procedures
CREATE OR REPLACE PACKAGE overload_package AS
PROCEDURE print_value(p_value IN NUMBER);
PROCEDURE print_value(p_value IN VARCHAR2);
END overload_package;
CREATE OR REPLACE PACKAGE BODY overload_package AS
PROCEDURE print_value(p_value IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Number: ' || p_value);
END print_value;
PROCEDURE print_value(p_value IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('String: ' || p_value);
END print_value;
END overload_package;
Packages with Collections
7. Package with PL/SQL Table Collection
CREATE OR REPLACE PACKAGE table_collection_package AS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
PROCEDURE process_employees(p_employees IN emp_table);
END table_collection_package;
CREATE OR REPLACE PACKAGE BODY table_collection_package AS
PROCEDURE process_employees(p_employees IN emp_table) IS
BEGIN
FOR i IN 1..p_employees.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || p_employees(i).employee_id || ', Name: ' || p_employees(i).employee_name);
END LOOP;
END process_employees;
END table_collection_package;
8. Package with Nested Tables
CREATE OR REPLACE PACKAGE nested_table_package AS
TYPE dept_table IS TABLE OF departments%ROWTYPE;
PROCEDURE process_departments(p_depts IN dept_table);
END nested_table_package;
CREATE OR REPLACE PACKAGE BODY nested_table_package AS
PROCEDURE process_departments(p_depts IN dept_table) IS
BEGIN
FOR i IN 1..p_depts.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Dept ID: ' || p_depts(i).department_id || ', Dept Name: ' || p_depts(i).department_name);
END LOOP;
END process_departments;
END nested_table_package;
Packages with Dynamic SQL
9. Package with Dynamic SQL for Queries
CREATE OR REPLACE PACKAGE dynamic_sql_package AS
PROCEDURE run_dynamic_query(p_sql IN VARCHAR2);
END dynamic_sql_package;
CREATE OR REPLACE PACKAGE BODY dynamic_sql_package AS
PROCEDURE run_dynamic_query(p_sql IN VARCHAR2) IS
v_cursor SYS_REFCURSOR;
v_record employees%ROWTYPE;
BEGIN
OPEN v_cursor FOR p_sql;
LOOP
FETCH v_cursor INTO v_record;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_record.employee_id || ', Name: ' || v_record.employee_name);
END LOOP;
CLOSE v_cursor;
END run_dynamic_query;
END dynamic_sql_package;
10. Package with Dynamic SQL for DML Operations
CREATE OR REPLACE PACKAGE dml_dynamic_sql_package AS
PROCEDURE execute_dml(p_sql IN VARCHAR2);
END dml_dynamic_sql_package;
CREATE OR REPLACE PACKAGE BODY dml_dynamic_sql_package AS
PROCEDURE execute_dml(p_sql IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
END execute_dml;
END dml_dynamic_sql_package;
Packages with System Calls
11. Package with DBMS_SCHEDULER Integration
CREATE OR REPLACE PACKAGE scheduler_package AS
PROCEDURE create_job(p_job_name IN VARCHAR2, p_interval IN VARCHAR2);
END scheduler_package;
CREATE OR REPLACE PACKAGE BODY scheduler_package AS
PROCEDURE create_job(p_job_name IN VARCHAR2, p_interval IN VARCHAR2) IS
BEGIN
DBMS_SCHEDULER.create_job(
job_name => p_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN null; END;',
start_date => SYSTIMESTAMP,
repeat_interval => p_interval
);
END create_job;
END scheduler_package;
12. Package with File Handling Using UTL_FILE
CREATE OR REPLACE PACKAGE file_package AS
PROCEDURE write_to_file(p_file_name IN VARCHAR2, p_content IN VARCHAR2);
END file_package;
CREATE OR REPLACE PACKAGE BODY file_package AS
PROCEDURE write_to_file(p_file_name IN VARCHAR2, p_content IN VARCHAR2) IS
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('DIRECTORY_ALIAS', p_file_name, 'W');
UTL_FILE.PUT_LINE(v_file, p_content);
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END write_to_file;
END file_package;
Packages with Error Handling
13. Package with Custom Error Messages
CREATE OR REPLACE PACKAGE error_message_package AS
PROCEDURE custom_error_proc;
END error_message_package;
CREATE OR REPLACE PACKAGE BODY error_message_package AS
PROCEDURE custom_error_proc IS
BEGIN
RAISE_APPLICATION_ERROR(-20002, 'Custom error message');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END custom_error_proc;
END error_message_package;
14. Package with Retries on Failure
CREATE OR REPLACE PACKAGE retry_package AS
PROCEDURE retry_operation;
END retry_package;
CREATE OR REPLACE PACKAGE BODY retry_package AS
PROCEDURE retry_operation IS
v_retry_count NUMBER := 3;
v_current_try NUMBER := 0;
BEGIN
WHILE v_current_try < v_retry_count LOOP
BEGIN
-- Simulate operation
RAISE_APPLICATION_ERROR(-20003, 'Operation failed');
EXCEPTION
WHEN OTHERS THEN
v_current_try := v_current_try + 1;
DBMS_OUTPUT.PUT_LINE
('Retrying... Attempt ' || v_current_try);
END;
END LOOP;
END retry_operation;
END retry_package;
Packages for Data Management
15. Package for Batch Insertions
CREATE OR REPLACE PACKAGE batch_insert_package AS
PROCEDURE insert_employees(p_employees IN SYS_REFCURSOR);
END batch_insert_package;
CREATE OR REPLACE PACKAGE BODY batch_insert_package AS
PROCEDURE insert_employees(p_employees IN SYS_REFCURSOR) IS
v_employee_record employees%ROWTYPE;
BEGIN
LOOP
FETCH p_employees INTO v_employee_record;
EXIT WHEN p_employees%NOTFOUND;
INSERT INTO employees (employee_id, employee_name) VALUES (v_employee_record.employee_id, v_employee_record.employee_name);
END LOOP;
END insert_employees;
END batch_insert_package;
16. Package for Data Validation
CREATE OR REPLACE PACKAGE data_validation_package AS
PROCEDURE validate_employee(p_employee_id IN NUMBER);
END data_validation_package;
CREATE OR REPLACE PACKAGE BODY data_validation_package AS
PROCEDURE validate_employee(p_employee_id IN NUMBER) IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees WHERE employee_id = p_employee_id;
IF v_count = 0 THEN
RAISE_APPLICATION_ERROR(-20004, 'Employee ID not found');
END IF;
END validate_employee;
END data_validation_package;
Packages with Advanced Features
17. Package with Object Types
CREATE OR REPLACE PACKAGE object_type_package AS
TYPE employee_obj IS OBJECT (
employee_id NUMBER,
employee_name VARCHAR2
);
PROCEDURE display_employee(p_employee IN employee_obj);
END object_type_package;
CREATE OR REPLACE PACKAGE BODY object_type_package AS
PROCEDURE display_employee(p_employee IN employee_obj) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('ID: ' || p_employee.employee_id || ', Name: ' || p_employee.employee_name);
END display_employee;
END object_type_package;
18. Package with Autonomous Transactions
CREATE OR REPLACE PACKAGE autonomous_transaction_package AS
PROCEDURE log_transaction(p_message IN VARCHAR2);
END autonomous_transaction_package;
CREATE OR REPLACE PACKAGE BODY autonomous_transaction_package AS
PROCEDURE log_transaction(p_message IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO transaction_log (log_message, log_date) VALUES (p_message, SYSDATE);
COMMIT;
END log_transaction;
END autonomous_transaction_package;
Packages for Business Logic
19. Package for Order Processing
CREATE OR REPLACE PACKAGE order_processing_package AS
PROCEDURE process_order(p_order_id IN NUMBER);
END order_processing_package;
CREATE OR REPLACE PACKAGE BODY order_processing_package AS
PROCEDURE process_order(p_order_id IN NUMBER) IS
BEGIN
-- Order processing logic
DBMS_OUTPUT.PUT_LINE('Processing order ID: ' || p_order_id);
END process_order;
END order_processing_package;
20. Package for Customer Management
CREATE OR REPLACE PACKAGE customer_management_package AS
PROCEDURE add_customer(p_customer_id IN NUMBER, p_customer_name IN VARCHAR2);
PROCEDURE update_customer(p_customer_id IN NUMBER, p_customer_name IN VARCHAR2);
END customer_management_package;
CREATE OR REPLACE PACKAGE BODY customer_management_package AS
PROCEDURE add_customer(p_customer_id IN NUMBER, p_customer_name IN VARCHAR2) IS
BEGIN
INSERT INTO customers (customer_id, customer_name) VALUES (p_customer_id, p_customer_name);
END add_customer;
PROCEDURE update_customer(p_customer_id IN NUMBER, p_customer_name IN VARCHAR2) IS
BEGIN
UPDATE customers SET customer_name = p_customer_name WHERE customer_id = p_customer_id;
END update_customer;
END customer_management_package;
Packages with Complex Operations
21. Package with Complex Calculations
CREATE OR REPLACE PACKAGE complex_calculation_package AS
FUNCTION calculate_bonus(p_salary IN NUMBER, p_performance IN NUMBER) RETURN NUMBER;
END complex_calculation_package;
CREATE OR REPLACE PACKAGE BODY complex_calculation_package AS
FUNCTION calculate_bonus(p_salary IN NUMBER, p_performance IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_salary * (p_performance / 100);
END calculate_bonus;
END complex_calculation_package;
22. Package for Date Manipulation
CREATE OR REPLACE PACKAGE date_manipulation_package AS
FUNCTION add_days(p_start_date IN DATE, p_days IN NUMBER) RETURN DATE;
END date_manipulation_package;
CREATE OR REPLACE PACKAGE BODY date_manipulation_package AS
FUNCTION add_days(p_start_date IN DATE, p_days IN NUMBER) RETURN DATE IS
BEGIN
RETURN p_start_date + p_days;
END add_days;
END date_manipulation_package;
Packages for Reports and Notifications
23. Package for Report Generation
CREATE OR REPLACE PACKAGE report_generation_package AS
PROCEDURE generate_employee_report;
END report_generation_package;
CREATE OR REPLACE PACKAGE BODY report_generation_package AS
PROCEDURE generate_employee_report IS
BEGIN
-- Report generation logic
DBMS_OUTPUT.PUT_LINE('Generating employee report...');
END generate_employee_report;
END report_generation_package;
24. Package for Sending Notifications
CREATE OR REPLACE PACKAGE notification_package AS
PROCEDURE send_email(p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2);
END notification_package;
CREATE OR REPLACE PACKAGE BODY notification_package AS
PROCEDURE send_email(p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2) IS
BEGIN
-- Simulate sending email
DBMS_OUTPUT.PUT_LINE('Sending email to ' || p_recipient || ' with subject ' || p_subject);
END send_email;
END notification_package;
Packages for API Integration
25. Package for Web Service Calls
CREATE OR REPLACE PACKAGE web_service_package AS
PROCEDURE call_web_service(p_endpoint IN VARCHAR2, p_request IN VARCHAR2);
END web_service_package;
CREATE OR REPLACE PACKAGE BODY web_service_package AS
PROCEDURE call_web_service(p_endpoint IN VARCHAR2, p_request IN VARCHAR2) IS
BEGIN
-- Simulate web service call
DBMS_OUTPUT.PUT_LINE('Calling web service at ' || p_endpoint);
END call_web_service;
END web_service_package;
26. Package for External System Integration
CREATE OR REPLACE PACKAGE external_system_package AS
PROCEDURE sync_data_with_external_system(p_data IN VARCHAR2);
END external_system_package;
CREATE OR REPLACE PACKAGE BODY external_system_package AS
PROCEDURE sync_data_with_external_system(p_data IN VARCHAR2) IS
BEGIN
-- Simulate data synchronization
DBMS_OUTPUT.PUT_LINE('Synchronizing data: ' || p_data);
END sync_data_with_external_system;
END external_system_package;
Packages with Security and Access Control
27. Package for Access Control
CREATE OR REPLACE PACKAGE access_control_package AS
PROCEDURE check_user_access(p_user_id IN NUMBER);
END access_control_package;
CREATE OR REPLACE PACKAGE BODY access_control_package AS
PROCEDURE check_user_access(p_user_id IN NUMBER) IS
v_user_role VARCHAR2(30);
BEGIN
SELECT role INTO v_user_role FROM users WHERE user_id = p_user_id;
IF v_user_role <> 'admin' THEN
RAISE_APPLICATION_ERROR(-20005, 'Access denied');
END IF;
END check_user_access;
END access_control_package;
28. Package for Data Masking
CREATE OR REPLACE PACKAGE data_masking_package AS
PROCEDURE mask_sensitive_data(p_employee_id IN NUMBER);
END data_masking_package;
CREATE OR REPLACE PACKAGE BODY data_masking_package AS
PROCEDURE mask_sensitive_data(p_employee_id IN NUMBER) IS
BEGIN
UPDATE employees SET social_security_number = 'XXX-XX-XXXX' WHERE employee_id = p_employee_id;
END mask_sensitive_data;
END data_masking_package;
Packages for Transaction Management
29. Package for Handling Transactions
CREATE OR REPLACE PACKAGE transaction_management_package AS
PROCEDURE perform_transaction;
END transaction_management_package;
CREATE OR REPLACE PACKAGE BODY transaction_management_package AS
PROCEDURE perform_transaction IS
BEGIN
SAVEPOINT my_savepoint;
-- Transaction logic
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO my_savepoint;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back');
END perform_transaction;
END transaction_management_package;
30. Package with Rollback and Commit
CREATE OR REPLACE PACKAGE commit_rollback_package AS
PROCEDURE process_with_commit(p_action IN VARCHAR2);
END commit_rollback_package;
CREATE OR REPLACE PACKAGE BODY commit_rollback_package AS
PROCEDURE process_with_commit(p_action IN VARCHAR2) IS
BEGIN
-- Simulate processing
IF p_action = 'commit' THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('Transaction committed');
ELSE
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Transaction rolled back');
END IF;
END process_with_commit;
END commit_rollback_package;
Packages for Utility Functions
31. Package for Utility Functions
CREATE OR REPLACE PACKAGE utility_package AS
FUNCTION get_current_timestamp RETURN TIMESTAMP;
FUNCTION format_currency(p_amount IN NUMBER) RETURN VARCHAR2;
END utility_package;
CREATE OR REPLACE PACKAGE BODY utility_package AS
FUNCTION get_current_timestamp RETURN TIMESTAMP IS
BEGIN
RETURN SYSTIMESTAMP;
END get_current_timestamp;
FUNCTION format_currency(p_amount IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(p_amount, 'FM$999,999,999.00');
END format_currency;
END utility_package;
32. Package for String Manipulations
CREATE OR REPLACE PACKAGE string_manipulation_package AS
FUNCTION reverse_string(p_str IN VARCHAR2) RETURN VARCHAR2;
FUNCTION string_length(p_str IN VARCHAR2) RETURN NUMBER;
END string_manipulation_package;
CREATE OR REPLACE PACKAGE BODY string_manipulation_package AS
FUNCTION reverse_string(p_str IN VARCHAR2) RETURN VARCHAR2 IS
v_reversed VARCHAR2(32767);
BEGIN
FOR i IN REVERSE 1..LENGTH(p_str) LOOP
v_reversed := v_reversed || SUBSTR(p_str, i, 1);
END LOOP;
RETURN v_reversed;
END reverse_string;
FUNCTION string_length(p_str IN VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN LENGTH(p_str);
END string_length;
END string_manipulation_package;
Packages with Data Retrieval and Manipulation
33. Package for Data Aggregation
CREATE OR REPLACE PACKAGE aggregation_package AS
FUNCTION get_total_sales(p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER;
END aggregation_package;
CREATE OR REPLACE PACKAGE BODY aggregation_package AS
FUNCTION get_total_sales(p_start_date IN DATE, p_end_date IN DATE) RETURN NUMBER IS
v_total NUMBER;
BEGIN
SELECT SUM(sales_amount) INTO v_total
FROM sales
WHERE sales_date BETWEEN p_start_date AND p_end_date;
RETURN v_total;
END get_total_sales;
END aggregation_package;
34. Package for Handling Employee Data
CREATE OR REPLACE PACKAGE employee_data_package AS
FUNCTION get_employee_name(p_employee_id IN NUMBER) RETURN VARCHAR2;
END employee_data_package;
CREATE OR REPLACE PACKAGE BODY employee_data_package AS
FUNCTION get_employee_name(p_employee_id IN NUMBER) RETURN VARCHAR2 IS
v_employee_name VARCHAR2(100);
BEGIN
SELECT employee_name INTO v_employee_name
FROM employees
WHERE employee_id = p_employee_id;
RETURN v_employee_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'No employee found';
END get_employee_name;
END employee_data_package;
Packages for Reporting and Analytics
35. Package for Generating Monthly Reports
CREATE OR REPLACE PACKAGE monthly_report_package AS
PROCEDURE generate_report(p_month IN NUMBER, p_year IN NUMBER);
END monthly_report_package;
CREATE OR REPLACE PACKAGE BODY monthly_report_package AS
PROCEDURE generate_report(p_month IN NUMBER, p_year IN NUMBER) IS
BEGIN
-- Simulate report generation
DBMS_OUTPUT.PUT_LINE('Generating report for ' || TO_CHAR(TO_DATE(p_month || '/' || p_year, 'MM/YYYY'), 'Month YYYY'));
END generate_report;
END monthly_report_package;
36. Package for User Activity Logging
CREATE OR REPLACE PACKAGE activity_logging_package AS
PROCEDURE log_user_activity(p_user_id IN NUMBER, p_activity IN VARCHAR2);
END activity_logging_package;
CREATE OR REPLACE PACKAGE BODY activity_logging_package AS
PROCEDURE log_user_activity(p_user_id IN NUMBER, p_activity IN VARCHAR2) IS
BEGIN
INSERT INTO user_activity (user_id, activity, log_date) VALUES (p_user_id, p_activity, SYSDATE);
END log_user_activity;
END activity_logging_package;
Packages for Complex Business Logic
37. Package for Customer Credit Evaluation
CREATE OR REPLACE PACKAGE credit_evaluation_package AS
FUNCTION evaluate_credit(p_customer_id IN NUMBER) RETURN VARCHAR2;
END credit_evaluation_package;
CREATE OR REPLACE PACKAGE BODY credit_evaluation_package AS
FUNCTION evaluate_credit(p_customer_id IN NUMBER) RETURN VARCHAR2 IS
v_credit_score NUMBER;
BEGIN
SELECT credit_score INTO v_credit_score FROM customers WHERE customer_id = p_customer_id;
IF v_credit_score > 700 THEN
RETURN 'Excellent';
ELSIF v_credit_score BETWEEN 600 AND 700 THEN
RETURN 'Good';
ELSE
RETURN 'Poor';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'No credit score found';
END evaluate_credit;
END credit_evaluation_package;
38. Package for Inventory Management
CREATE OR REPLACE PACKAGE inventory_package AS
PROCEDURE update_inventory(p_item_id IN NUMBER, p_quantity IN NUMBER);
END inventory_package;
CREATE OR REPLACE PACKAGE BODY inventory_package AS
PROCEDURE update_inventory(p_item_id IN NUMBER, p_quantity IN NUMBER) IS
BEGIN
UPDATE inventory SET quantity = quantity + p_quantity WHERE item_id = p_item_id;
END update_inventory;
END inventory_package;
Packages for Financial Operations
39. Package for Loan Processing
CREATE OR REPLACE PACKAGE loan_processing_package AS
PROCEDURE process_loan(p_loan_id IN NUMBER);
END loan_processing_package;
CREATE OR REPLACE PACKAGE BODY loan_processing_package AS
PROCEDURE process_loan(p_loan_id IN NUMBER) IS
BEGIN
-- Simulate loan processing
DBMS_OUTPUT.PUT_LINE('Processing loan ID: ' || p_loan_id);
END process_loan;
END loan_processing_package;
40. Package for Payroll Calculation
CREATE OR REPLACE PACKAGE payroll_package AS
FUNCTION calculate_payroll(p_employee_id IN NUMBER) RETURN NUMBER;
END payroll_package;
CREATE OR REPLACE PACKAGE BODY payroll_package AS
FUNCTION calculate_payroll(p_employee_id IN NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id;
RETURN v_salary * 0.9; -- Assuming 10% deduction for taxes
END calculate_payroll;
END payroll_package;
Packages with Web Service Integration
41. Package for REST API Integration
CREATE OR REPLACE PACKAGE rest_api_package AS
PROCEDURE call_rest_api(p_endpoint IN VARCHAR2, p_payload IN CLOB);
END rest_api_package;
CREATE OR REPLACE PACKAGE BODY rest_api_package AS
PROCEDURE call_rest_api(p_endpoint IN VARCHAR2, p_payload IN CLOB) IS
BEGIN
-- Simulate REST API call
DBMS_OUTPUT.PUT_LINE('Calling REST API at ' || p_endpoint);
END call_rest_api;
END rest_api_package;
42. Package for SOAP API Integration
CREATE OR REPLACE PACKAGE soap_api_package AS
PROCEDURE call_soap_api(p_endpoint IN VARCHAR2, p_soap_request IN CLOB);
END soap_api_package;
CREATE OR REPLACE PACKAGE BODY soap_api_package AS
PROCEDURE call_soap_api(p_endpoint IN VARCHAR2, p_soap_request IN CLOB) IS
BEGIN
-- Simulate SOAP API call
DBMS_OUTPUT.PUT_LINE('Calling SOAP API at ' || p_endpoint);
END call_soap_api;
END soap_api_package;
Packages for Data Encryption and Decryption
43. Package for Data Encryption
CREATE OR REPLACE PACKAGE encryption_package AS
FUNCTION encrypt_data(p_data IN VARCHAR2) RETURN VARCHAR2;
END encryption_package;
CREATE OR REPLACE PACKAGE BODY encryption_package AS
FUNCTION encrypt_data(p_data IN VARCHAR2) RETURN VARCHAR2 IS
v_encrypted_data VARCHAR2(4000);
BEGIN
-- Simulate encryption
v_encrypted_data := UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.ENCRYPT(UTL_RAW.CAST_TO_RAW(p_data), DBMS_CRYPTO.DES_CBC, 'key123'));
RETURN v_encrypted_data;
END encrypt_data;
END encryption_package;
44. Package for Data Decryption
CREATE OR REPLACE PACKAGE decryption_package AS
FUNCTION decrypt_data(p_encrypted_data IN VARCHAR2) RETURN VARCHAR2;
END decryption_package;
CREATE OR REPLACE PACKAGE BODY decryption_package AS
FUNCTION decrypt_data(p_encrypted_data IN VARCHAR2) RETURN VARCHAR2 IS
v_decrypted_data VARCHAR2(4000);
BEGIN
-- Simulate decryption
v_decrypted_data := DBMS_CRYPTO.DECRYPT(UTL_RAW.CAST_TO_RAW(p_en
No comments:
Post a Comment