Plsql

Advanced Oracle PL/SQL Database Tutorial

This tutorial delves into advanced concepts of Oracle PL/SQL, building upon a foundational understanding. It assumes you are already familiar with basic PL/SQL syntax, including variables, data types, control structures (IF-THEN-ELSE, loops), and basic procedure/function creation.

I. Collections (Arrays)

PL/SQL offers powerful collection types to store and manipulate sets of data.

1. Types of Collections:

• Associative Arrays (Index-By Tables): Indexed by a string or a binary integer. Fast lookups. Ideal for sparse data.
• Nested Tables: Indexed by a consecutive sequence of integers starting from 1. Can be stored in a database table.
• VARRAYs (Variable-Size Arrays): Indexed by a consecutive sequence of integers starting from 1. Have a fixed maximum size. Stored in a database table in a single column.

2. Defining Collections:

-- Associative Array (Index-By Table)
TYPE emp_name_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(20);
emp_names emp_name_type;

-- Nested Table
TYPE phone_numbers_type IS TABLE OF VARCHAR2(20);
phone_numbers phone_numbers_type;

-- VARRAY
TYPE top_products_type IS VARRAY(10) OF VARCHAR2(100);
top_products top_products_type;

3. Using Collections:

DECLARE
  TYPE emp_name_type IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(20);
  emp_names emp_name_type;
  emp_id VARCHAR2(20);
BEGIN
  emp_names('JSMITH') := 'John Smith';
  emp_names('ALICE') := 'Alice Brown';

  emp_id := 'ALICE';
  DBMS_OUTPUT.PUT_LINE('Employee Name for ' || emp_id || ': ' || emp_names(emp_id));

  -- Check if an element exists
  IF emp_names.EXISTS('JSMITH') THEN
    DBMS_OUTPUT.PUT_LINE('JSMITH exists in the collection.');
  END IF;

  -- Iterating through an associative array (requires PL/SQL 12c or higher)
  FOR indx IN INDEXES OF emp_names LOOP
    DBMS_OUTPUT.PUT_LINE('Index: ' || indx || ', Value: ' || emp_names(indx));
  END LOOP;

  -- Nested Table Example
  DECLARE
    TYPE phone_numbers_type IS TABLE OF VARCHAR2(20);
    phone_numbers phone_numbers_type := phone_numbers_type(); -- Initialize
  BEGIN
    phone_numbers.EXTEND(2); -- Allocate space for 2 elements
    phone_numbers(1) := '555-123-4567';
    phone_numbers(2) := '555-987-6543';

    FOR i IN 1..phone_numbers.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Phone Number ' || i || ': ' || phone_numbers(i));
    END LOOP;
  END;

  -- VARRAY Example
  DECLARE
    TYPE top_products_type IS VARRAY(3) OF VARCHAR2(100);
    top_products top_products_type := top_products_type('Product A', 'Product B', 'Product C');
  BEGIN
    FOR i IN 1..top_products.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Top Product ' || i || ': ' || top_products(i));
    END LOOP;
  END;
END;
/

4. Collection Methods:

• EXISTS(n): Returns TRUE if the nth element exists.
• COUNT: Returns the number of elements in the collection.
• FIRST: Returns the index of the first element.
• LAST: Returns the index of the last element.
• NEXT(n): Returns the index of the element after the nth element.
• PRIOR(n): Returns the index of the element before the nth element.
• TRIM(n): Removes n elements from the end of a nested table or VARRAY. If n is omitted, removes one element.
• DELETE(n): Deletes the nth element from a nested table or associative array.
• EXTEND(n, i): Adds n elements to the end of a nested table or VARRAY, each initialized with the value of element i. If only n is supplied, adds n null elements (if the collection data type allows nulls).

II. Dynamic SQL

Dynamic SQL allows you to build SQL statements at runtime, providing flexibility in querying and manipulating data.

1. EXECUTE IMMEDIATE Statement:

DECLARE
  sql_stmt VARCHAR2(200);
  emp_count NUMBER;
BEGIN
  sql_stmt := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';

  EXECUTE IMMEDIATE sql_stmt
  INTO emp_count
  USING 10;  -- Pass the department_id value (10)

  DBMS_OUTPUT.PUT_LINE('Number of employees in department 10: ' || emp_count);

  -- Example with DDL (Data Definition Language)
  sql_stmt := 'CREATE TABLE temp_table (id NUMBER, name VARCHAR2(50))';
  EXECUTE IMMEDIATE sql_stmt;

  -- Clean up
  sql_stmt := 'DROP TABLE temp_table';
  EXECUTE IMMEDIATE sql_stmt;
END;
/

2. Using OPEN-FOR, FETCH, and CLOSE with Dynamic Queries:

This approach allows you to process results from dynamic queries row by row.

DECLARE
  sql_stmt VARCHAR2(200);
  emp_record employees%ROWTYPE;
  emp_cursor SYS_REFCURSOR; -- Strongly typed cursor, if possible.
BEGIN
  sql_stmt := 'SELECT * FROM employees WHERE salary > :salary_threshold';

  OPEN emp_cursor FOR sql_stmt USING 50000;

  LOOP
    FETCH emp_cursor INTO emp_record;
    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
  END LOOP;

  CLOSE emp_cursor;
END;
/

3. Advantages of Dynamic SQL:

• Flexibility: Construct SQL statements based on runtime conditions.
• Complex Queries: Build complex queries that might be difficult or impossible to create statically.
• Meta-Data Driven Operations: Generate SQL based on database metadata (e.g., table names, column names).

4. Security Considerations (SQL Injection):

• Always use bind variables (USING clause): This prevents malicious code from being injected into your SQL statements. Never directly concatenate user input into your SQL string.
• Validate Input: Sanitize any user-supplied data before using it in your SQL.

III. Bulk Processing (FORALL and BULK COLLECT)

Bulk processing significantly improves performance when dealing with large datasets.

1. FORALL Statement:

Executes a single DML statement (INSERT, UPDATE, DELETE) for all elements in a collection. It reduces context switching between PL/SQL and the SQL engine.

DECLARE
  TYPE emp_id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE salary_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  emp_ids  emp_id_type;
  salaries salary_type;
BEGIN
  -- Populate collections with employee IDs and salaries
  emp_ids(1) := 100;
  emp_ids(2) := 101;
  emp_ids(3) := 102;
  salaries(1) := 60000;
  salaries(2) := 70000;
  salaries(3) := 80000;

  -- Bulk Update
  FORALL i IN 1..emp_ids.COUNT
    UPDATE employees SET salary = salaries(i) WHERE employee_id = emp_ids(i);

  COMMIT;
END;
/

2. BULK COLLECT Clause:

Retrieves multiple rows from a query into a collection in a single operation.

DECLARE
  TYPE emp_record_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  emp_records emp_record_type;
BEGIN
  SELECT *
  BULK COLLECT INTO emp_records
  FROM employees
  WHERE department_id = 50;

  FOR i IN 1..emp_records.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_records(i).first_name || ' ' || emp_records(i).last_name);
  END LOOP;
END;
/

3. Combining FORALL and BULK COLLECT:

For maximum performance, combine BULK COLLECT to retrieve data into collections and FORALL to perform bulk DML operations.

DECLARE
  TYPE emp_id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  emp_ids emp_id_type;
BEGIN
  SELECT employee_id
  BULK COLLECT INTO emp_ids
  FROM employees
  WHERE department_id = 50;

  FORALL i IN 1..emp_ids.COUNT
    DELETE FROM employees WHERE employee_id = emp_ids(i);

  COMMIT;
END;
/

4. Considerations:

• Memory Usage: BULK COLLECT can consume significant memory if you’re retrieving very large datasets. Consider limiting the number of rows retrieved using LIMIT clause if necessary.
• Error Handling: FORALL with the SAVE EXCEPTIONS clause can capture and process errors that occur during individual DML operations.

IV. Error Handling with SAVE EXCEPTIONS (for FORALL)

When using FORALL, a single error can halt the entire operation. SAVE EXCEPTIONS allows you to continue processing even if errors occur, capturing the errors for later analysis.

DECLARE
  TYPE emp_id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE salary_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  emp_ids  emp_id_type;
  salaries salary_type;

  num_errors NUMBER := 0;
BEGIN
  -- Populate collections with employee IDs and salaries
  emp_ids(1) := 100;
  emp_ids(2) := 101;
  emp_ids(3) := 99999; -- Non-existent employee ID
  salaries(1) := 60000;
  salaries(2) := 70000;
  salaries(3) := 80000;

  BEGIN
    FORALL i IN 1..emp_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees SET salary = salaries(i) WHERE employee_id = emp_ids(i);
  EXCEPTION
    WHEN OTHERS THEN
      num_errors := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.PUT_LINE('Number of errors: ' || num_errors);

      FOR i IN 1..num_errors LOOP
        DBMS_OUTPUT.PUT_LINE('Error index: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
      END LOOP;
  END;

  COMMIT; -- Commit the successful updates
END;
/

V. Autonomous Transactions

An autonomous transaction is an independent transaction that can be started within another transaction. Changes made in an autonomous transaction are committed or rolled back independently of the calling transaction.

1. Creating an Autonomous Procedure:

CREATE OR REPLACE PROCEDURE log_activity (p_message VARCHAR2)
  AUTONOMOUS_TRANSACTION
AS
BEGIN
  INSERT INTO audit_log (log_date, message) VALUES (SYSDATE, p_message);
  COMMIT;  -- This COMMIT only affects the audit_log table
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Rollback only affects the audit_log table
    RAISE;
END;
/

2. Calling an Autonomous Procedure:

DECLARE
  e_invalid_data EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_data, -2291); -- Foreign Key constraint violation.
BEGIN
  -- Try to insert an invalid record
  INSERT INTO departments (department_id, department_name, location_id)
  VALUES (300, 'Invalid Department', 9999);  -- 9999 is not a valid location_id

  COMMIT; -- This COMMIT might fail and roll back

EXCEPTION
  WHEN e_invalid_data THEN
    DBMS_OUTPUT.PUT_LINE('Foreign Key Constraint Violation');
    log_activity('Foreign Key Violation: Inserting invalid department.');
    ROLLBACK;  -- Roll back the main transaction
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Other error occurred.');
    log_activity('Other error occurred: ' || SQLERRM);
    ROLLBACK;
    RAISE;
END;
/

3. Use Cases:

• Auditing: Logging activities even if the main transaction fails.
• Error Logging: Recording errors even when rolling back the main transaction.
• Independent Operations: Performing tasks that should be committed regardless of the main transaction’s outcome.

VI. Invoker Rights vs. Definer Rights (SECURITY Clause)

PL/SQL units (procedures, functions, packages, etc.) can execute with either invoker rights or definer rights.

1. Definer Rights (DEFAULT):

• The unit executes with the privileges of the user who owns (defines) the unit.
• The default behavior if no SECURITY clause is specified.

2. Invoker Rights (AUTHID CURRENT_USER):

• The unit executes with the privileges of the current user (the user executing the unit).
• Useful when you want a procedure to access data based on the current user’s permissions.

3. Syntax:

CREATE OR REPLACE PROCEDURE my_procedure
  AUTHID CURRENT_USER  -- Invoker Rights
AS
BEGIN
  -- Code that accesses data based on the current user's privileges.
END;
/

CREATE OR REPLACE FUNCTION my_function
  SECURITY DEFINER  -- Definer Rights (explicitly specified, although default)
RETURN NUMBER
AS
BEGIN
  -- Code that accesses data based on the definer's privileges.
  RETURN 1;
END;
/

4. Example:

Assume you have a table employee_salaries that only managers should be able to view directly.

-- Table structure:
-- CREATE TABLE employee_salaries (employee_id NUMBER, salary NUMBER, manager_id NUMBER);

-- Definer rights procedure (defined by a user with access to employee_salaries)
CREATE OR REPLACE PROCEDURE get_employee_salary (p_employee_id NUMBER, p_salary OUT NUMBER)
AS
BEGIN
  SELECT salary INTO p_salary
  FROM employee_salaries
  WHERE employee_id = p_employee_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_salary := NULL;
END;
/

-- Invoker rights function (accessing user specific table)
CREATE OR REPLACE FUNCTION get_user_preference(preference_name VARCHAR2)
AUTHID CURRENT_USER
RETURN VARCHAR2
AS
    pref_value VARCHAR2(200);
BEGIN
    SELECT preference_value INTO pref_value
    FROM user_preferences
    WHERE username = USER AND preference_name = get_user_preference.preference_name;
    RETURN pref_value;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- Usage:
-- A user without direct access to employee_salaries can call get_employee_salary
-- and retrieve the salary.  The procedure executes with the definer's privileges.
-- The get_user_preference function will only retrieve preferences for the currently logged in user.

5. Security Considerations:

• Definer Rights: Be cautious when granting EXECUTE privileges on definer rights units, as users will be able to perform actions they might not normally be authorized to do.
• Invoker Rights: Invoker rights units can be more complex to manage, as the behavior depends on the caller’s privileges.

VII. Working with External Tables

External tables allow you to access data stored in operating system files as if they were regular database tables.

1. Creating a Directory Object:

First, create a directory object that points to the location of your external file. This needs to be done by a DBA or a user with the CREATE ANY DIRECTORY privilege.

CREATE OR REPLACE DIRECTORY data_dir AS '/path/to/your/data/files';  -- Replace with the actual path

2. Creating an External Table:

CREATE TABLE external_employees (
  employee_id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  email VARCHAR2(100),
  hire_date DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (employee_id, first_name, last_name, email, hire_date DATE 'YYYY-MM-DD')
  )
  LOCATION ('employees.csv')  -- Replace with your file name
)
REJECT LIMIT UNLIMITED;  -- If any rows fail to load, don't stop

3. Querying an External Table:

You can query an external table just like a regular table:

SELECT * FROM external_employees;

4. Key Parameters:

• TYPE: Specifies the external table access driver (usually ORACLE_LOADER).
• DEFAULT DIRECTORY: The directory object containing the file.
• ACCESS PARAMETERS: Defines the format of the external file (delimiter, field types, date formats, etc.).
• LOCATION: The name of the external file.
• REJECT LIMIT: Controls how many errors are allowed before the load fails. UNLIMITED allows all rows to be processed regardless of errors.

5. Benefits:

• Direct Access: Access data in files without loading it into the database.
• Integration: Integrate data from various sources (flat files, logs, etc.).
• Data Warehousing: Useful for loading data into data warehouses.

VIII. Advanced Queuing (AQ)

Oracle Advanced Queuing (AQ) provides a message queuing system within the database.

1. Creating a Queue Type (Payload Type):

First, create a type that represents the structure of the messages you want to queue.

CREATE OR REPLACE TYPE emp_message_type AS OBJECT (
  employee_id NUMBER,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER
);
/

2. Creating a Queue Table:

Create a queue table to hold the messages.

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE (
    queue_table         => 'emp_queue_table',
    queue_payload_type  => 'emp_message_type'
  );
END;
/

3. Creating a Queue:

Create a queue within the queue table.

BEGIN
  DBMS_AQADM.CREATE_QUEUE (
    queue_name          => 'emp_queue',
    queue_table         => 'emp_queue_table'
  );
END;
/

4. Starting a Queue:

Start the queue to enable message enqueueing and dequeueing.

BEGIN
  DBMS_AQADM.START_QUEUE (
    queue_name => 'emp_queue'
  );
END;
/

5. Enqueueing a Message:

DECLARE
  enqueue_options   DBMS_AQ.ENQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message           emp_message_type;
  message_id      RAW(16);
BEGIN
  message := emp_message_type(100, 'John', 'Doe', 75000);

  DBMS_AQ.ENQUEUE(
    queue_name         => 'emp_queue',
    enqueue_options    => enqueue_options,
    message_properties => message_properties,
    payload            => message,
    msgid              => message_id
  );

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Message enqueued with ID: ' || RAWTOHEX(message_id));
END;
/

6. Dequeueing a Message:

DECLARE
  dequeue_options   DBMS_AQ.DEQUEUE_OPTIONS_T;
  message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
  message           emp_message_type;
  message_id      RAW(16);
BEGIN
  DBMS_AQ.DEQUEUE(
    queue_name         => 'emp_queue',
    dequeue_options    => dequeue_options,
    message_properties => message_properties,
    payload            => message,
    msgid              => message_id
  );

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Message dequeued: Employee ID: ' || message.employee_id || ', Name: ' || message.first_name || ' ' || message.last_name);
END;
/

7. Cleaning Up (Dropping the Queue and Queue Table):

BEGIN
  DBMS_AQADM.STOP_QUEUE(queue_name => 'emp_queue');
  DBMS_AQADM.DROP_QUEUE(queue_name => 'emp_queue');
  DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'emp_queue_table');
END;
/
DROP TYPE emp_message_type;
/

8. Use Cases:

• Asynchronous Processing: Decouple applications by placing tasks in a queue.
• Workflow Management: Orchestrate complex workflows.
• Event Notifications: Publish events to subscribers.

IX. Working with JSON Data (Oracle 12c and later)

Oracle provides built-in support for storing and querying JSON data.

1. Storing JSON Data:

You can store JSON data in a VARCHAR2 column or a JSON column (recommended for performance).

-- Create a table with a JSON column
CREATE TABLE products (
  product_id NUMBER PRIMARY KEY,
  product_data JSON
);

-- Insert JSON data
INSERT INTO products (product_id, product_data) VALUES (
  1,
  '{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}'
);

2. Querying JSON Data:

Use the JSON_VALUE, JSON_OBJECT, JSON_ARRAYAGG, and other JSON functions to extract and manipulate JSON data.

-- Extract the product name
SELECT JSON_VALUE(product_data, '$.name') AS product_name
FROM products;

-- Extract an element from the features array
SELECT JSON_VALUE(product_data, '$.features[0]') AS first_feature
FROM products;

-- Create a JSON object from relational data
SELECT JSON_OBJECT(
  'product_id' VALUE product_id,
  'product_name' VALUE JSON_VALUE(product_data, '$.name'),
  'price' VALUE JSON_VALUE(product_data, '$.price')
) AS product_info
FROM products;

-- Create a JSON array of product names
SELECT JSON_ARRAYAGG(JSON_VALUE(product_data, '$.name')) AS product_names
FROM products;

3. Creating Indexes on JSON Data:

You can create function-based indexes on JSON paths to improve query performance.

CREATE INDEX product_name_idx ON products (JSON_VALUE(product_data, '$.name'));

4. Updating JSON Data:

Use the JSON_MERGEPATCH and JSON_TRANSFORM functions to update JSON documents.

-- Update the price
UPDATE products
SET product_data = JSON_MERGEPATCH(product_data, '{"price": 1300}')
WHERE product_id = 1;

-- Add a new feature
UPDATE products
SET product_data = JSON_MERGEPATCH(product_data, '{"features": ["16GB RAM", "512GB SSD", "Touchscreen"]}')
WHERE product_id = 1;

X. PL/SQL Code Optimization

Optimizing PL/SQL code is crucial for performance. Here are some key techniques:

• Minimize Context Switching: Use FORALL and BULK COLLECT to reduce the number of interactions between PL/SQL and the SQL engine.
• Use Bind Variables: Avoid hard-coding values in SQL statements. Use bind variables to allow the database to reuse execution plans.
• Optimize SQL Queries: Make sure your SQL queries are well-tuned. Use indexes appropriately, analyze table statistics, and avoid full table scans where possible.
• Cache Data: Cache frequently accessed data in PL/SQL collections or global variables to reduce database access. Be mindful of data staleness.
• Use Native Compilation (PLSQL_CODE_TYPE = NATIVE): Compile PL/SQL code to machine code for improved performance. This is especially effective for computationally intensive code.
• Use Profiling Tools: Tools like DBMS_PROFILER and DBMS_HPROF can help you identify performance bottlenecks in your code.
• Minimize Network Round Trips: For distributed databases, reduce the number of network calls.
• Avoid Using Cursors Unless Necessary: When possible, use set-based operations instead of cursors for better performance. If you must use a cursor, use the BULK COLLECT clause to fetch multiple rows at a time.
• Use Proper Data Types: Using correct data types can help performance. Don’t use VARCHAR2 for numbers.
• Avoid Nested Loops: Reduce nested loops if you can use better logic.
• Use PRAGMA INLINE: Inline functions will improve performance, especially for small and simple functions.

Example of Native Compilation:

ALTER PROCEDURE my_procedure COMPILE PLSQL_CODE_TYPE=NATIVE;

XI. Real-World Scenario: Order Processing System

Let’s consider a simplified order processing system to illustrate how these advanced concepts can be applied.

• Tables:
  • orders (order_id, customer_id, order_date, status)
  • order_items (order_id, product_id, quantity, price)
  • products (product_id, product_name, price, inventory_count)
  • audit_log (log_date, message)
  • customer_preferences (customer_id, preference_name, preference_value)

• Requirements:

  1. Order Placement: When a customer places an order, update the products table to reduce inventory, insert records into the orders and order_items tables, and log the order details in the audit_log. Handle potential inventory shortages.
  2. Order Fulfillment: Asynchronously process orders for shipment.
  3. Reporting: Generate reports on order statistics and product sales.
  4. Personalized Recommendations: Provide personalized product recommendations based on customer preferences.

• PL/SQL Implementation:

  ```sql – Package specification CREATE OR REPLACE PACKAGE order_management AS PROCEDURE place_order ( p_customer_id NUMBER, p_order_items TYPE order_items_type – TYPE order_items_type IS TABLE OF order_item_type; ); PROCEDURE fulfill_order (p_order_id NUMBER);

  FUNCTION get_customer_preference (p_customer_id NUMBER, p_preference_name VARCHAR2) RETURN VARCHAR2;

  FUNCTION get_product_recommendations (p_customer_id NUMBER) RETURN SYS.ODCIVarchar2List;

  TYPE order_item_type IS RECORD ( product_id NUMBER, quantity NUMBER ); TYPE order_items_type IS TABLE OF order_item_type;

  END order_management; /

  – Package body CREATE OR REPLACE PACKAGE BODY order_management AS

  – Autonomous procedure for logging activities PROCEDURE log_activity (p_message VARCHAR2) AUTONOMOUS_TRANSACTION AS BEGIN INSERT INTO audit_log (log_date, message) VALUES (SYSDATE, p_message); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;

  – Procedure to place an order PROCEDURE place_order ( p_customer_id NUMBER, p_order_items TYPE order_items_type ) AS v_order_id NUMBER; v_total_price NUMBER := 0; insufficient_inventory EXCEPTION;

  BEGIN – Get the next order ID (using a sequence) SELECT order_sequence.NEXTVAL INTO v_order_id FROM dual;

  -- Check inventory and calculate the total price
  FOR i IN 1 .. p_order_items.COUNT LOOP
    DECLARE
      v_product_price NUMBER;
      v_inventory_count NUMBER;
    BEGIN
      SELECT price, inventory_count INTO v_product_price, v_inventory_count FROM products WHERE product_id = p_order_items(i).product_id;
  
      IF v_inventory_count < p_order_items(i).quantity THEN
        RAISE insufficient_inventory;
      END IF;
  
      v_total_price := v_total_price + (v_product_price * p_order_items(i).quantity);
  
      -- Update inventory
      UPDATE products SET inventory_count = inventory_count - p_order_items(i).quantity WHERE product_id = p_order_items(i).product_id;
    EXCEPTION
      WHEN insufficient_inventory THEN
          log_activity('Order failed: Insufficient inventory for product ID ' || p_order_items(i).product_id);
          RAISE;
    END;
  END LOOP;
  
  -- Insert the order record
  INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (v_order_id, p_customer_id, SYSDATE, 'Pending');
  
  -- Insert order items (using FORALL for bulk insert)
  FORALL i IN 1 .. p_order_items.COUNT
    INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (v_order_id, p_order_items(i).product_id, p_order_items(i).quantity,
    (SELECT price FROM products WHERE product_id = p_order_items(i).product_id)
    );
  
  COMMIT;
  log_activity('Order placed successfully with order ID ' || v_order_id || ' for customer ' || p_customer_id);
  
  -- Enqueue the order for fulfillment (using Advanced Queuing)
  enqueue_order(v_order_id);
  

  EXCEPTION WHEN insufficient_inventory THEN ROLLBACK; RAISE; WHEN OTHERS THEN ROLLBACK; log_activity(‘Order placement failed: ‘ || SQLERRM); RAISE; END place_order;

  – Procedure to fulfill an order (using Advanced Queuing) PROCEDURE fulfill_order (p_order_id NUMBER) AS BEGIN – Simulate order fulfillment process DBMS_OUTPUT.PUT_LINE(‘Fulfilling order ID: ‘ || p_order_id); –Update order status (if applicable) UPDATE orders SET status = ‘Shipped’ WHERE order_id = p_order_id; COMMIT; log_activity(‘Order fulfilled successfully with order ID ‘ || p_order_id);

  EXCEPTION WHEN OTHERS THEN ROLLBACK; log_activity(‘Order fulfillment failed: ‘ || SQLERRM); RAISE; END fulfill_order;

  – Function to get customer preference FUNCTION get_customer_preference (p_customer_id NUMBER, p_preference_name VARCHAR2) RETURN VARCHAR2 AS v_preference_value VARCHAR2(200); BEGIN SELECT preference_value INTO v_preference_value FROM customer_preferences WHERE customer_id = p_customer_id AND preference_name = get_customer_preference.p_preference_name; RETURN v_preference_value; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_customer_preference;

  – Function to get personalized product recommendations (simplified example) FUNCTION get_product_recommendations (p_customer_id NUMBER) RETURN SYS.ODCIVarchar2List AS v_recommendations SYS.ODCIVarchar2List := SYS.ODCIVarchar2List(); v_category VARCHAR2(50); CURSOR recommendation_cursor IS SELECT product_name FROM products WHERE product_category = v_category AND ROWNUM <= 5; BEGIN v_category := get_customer_preference(p_customer_id, ‘preferred_category’);

  IF v_category IS NOT NULL THEN
      OPEN recommendation_cursor;
      LOOP
          FETCH recommendation_cursor BULK COLLECT INTO v_recommendations LIMIT 10;
          EXIT WHEN recommendation_cursor%NOTFOUND;
      END LOOP;
      CLOSE recommendation_cursor;
  ELSE
      v_recommendations.EXTEND(1);
      v_recommendations(1) := 'No recommendations available.';
  END IF;
  
  RETURN v_recommendations;   END get_product_recommendations;
  

  – helper procedure to enqueue a process, this assumes the queues are set up as mentioned in AQ PROCEDURE enqueue_order(p_order_id NUMBER) AS enqueue_options DBMS_AQ.ENQUEUE_OPTIONS_T; message_properties DBMS_AQ.MESSAGE_PROPERTIES_T; message NUMBER; message_id RAW(16); BEGIN message := p_order_id;

  DBMS_AQ.ENQUEUE( queue_name => ‘order_fulfillment_queue’, enqueue_options => enqueue_options, message_properties => message_properties, payload => message, msgid => message_id );

  COMMIT; log_activity(‘Order ID ‘ || p_order_id || ‘ enqueued for fulfillment.’);

  EXCEPTION WHEN OTHERS THEN ROLLBACK; log_activity(‘Failed to enqueue order for fulfillment: ‘ || SQLERRM); RAISE; END enqueue_order; END order_management; /

  – Separate procedure for dequeueing