Creating PL/SQL Block to query Object Tables

In this lesson, we will look at a PL/SQL block that uses a cursor to query an object table.
Here’s an example of a PL/SQL block to query "object tables" in Oracle 19c. This script assumes you have object types and object tables already created in your database. If not, I will include a quick setup for creating an object type and table for testing purposes.
Full Example

-- Step 1: Create an Object Type
CREATE OR REPLACE TYPE employee_obj AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    emp_salary NUMBER
);
/

-- Step 2: Create an Object Table
CREATE TABLE employee_table OF employee_obj;

-- Step 3: Insert Sample Data
INSERT INTO employee_table VALUES (employee_obj(1, 'Alice', 75000));
INSERT INTO employee_table VALUES (employee_obj(2, 'Bob', 65000));
INSERT INTO employee_table VALUES (employee_obj(3, 'Charlie', 85000));

COMMIT;

-- Step 4: PL/SQL Block to Query Object Table
DECLARE
    CURSOR obj_table_cursor IS
        SELECT VALUE(e) AS employee_obj_value
        FROM employee_table e;
    emp_obj employee_obj; -- Variable to hold object data
BEGIN
    -- Loop through the cursor
    FOR rec IN obj_table_cursor LOOP
        emp_obj := rec.employee_obj_value;
        -- Access object attributes
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_obj.emp_id);
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_obj.emp_name);
        DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_obj.emp_salary);
        DBMS_OUTPUT.PUT_LINE('---------------------------');
    END LOOP;
END;
/

Explanation:

1. Object Type Creation: The employee_obj defines the structure of the object with attributes like emp_id, emp_name, and emp_salary.
2. Object Table Creation: The employee_table is created based on the employee_obj type.
3. Sample Data: Insert statements add a few sample records into the object table.
4. PL/SQL Block:
   • A cursor (obj_table_cursor) selects the objects from the table using VALUE() to retrieve the object instance.
   • A loop iterates through the cursor, and the object attributes are accessed using dot notation (e.g., emp_obj.emp_id).
   • Output is printed using DBMS_OUTPUT.PUT_LINE.

Output (Example):

Employee ID: 1
Employee Name: Alice
Employee Salary: 75000
---------------------------
Employee ID: 2
Employee Name: Bob
Employee Salary: 65000
---------------------------
Employee ID: 3
Employee Name: Charlie
Employee Salary: 85000
---------------------------

Make sure `SERVEROUTPUT` is enabled in your SQL client to see the output. Use the following command if necessary:

SET SERVEROUTPUT ON;

The following series of images shows an example of such a PL/SQL block:

PL/SQL is appropriate or required for querying object tables in Oracle in certain situations, such as:

1. When complex business logic is involved: If the querying of an object table involves complex business logic that cannot be expressed using standard SQL queries, PL/SQL may be required. PL/SQL allows developers to write complex algorithms and manipulate data in ways that would be difficult or impossible to do using SQL alone.
2. When using user-defined types: If an object table contains user-defined types, PL/SQL may be required to manipulate the data. This is because SQL has limited support for user-defined types, and PL/SQL provides more flexibility and control over how the data is accessed and manipulated.
3. When using advanced features: Oracle provides advanced features such as pipelined table functions, which enable developers to process data in a streaming fashion, and row-level security, which restricts access to specific rows in a table. These features often require the use of PL/SQL code to implement.
4. When using dynamic SQL: If the query against an object table needs to be constructed dynamically at runtime, PL/SQL may be required. This is because SQL does not provide a way to dynamically generate queries, and PL/SQL must be used to generate the necessary SQL statements.

In summary, PL/SQL may be appropriate or required for querying object tables in Oracle when the querying involves complex business logic, user-defined types, advanced features, or dynamic SQL.

The central purpose of the Oracle PL/SQL language is to make it as easy and efficient as possible to query and change the contents of tables in a database. You must use the SQL language to access tables, and each time you do so, you use a cursor to execute your task.
Question:What is a cursor?
A cursor

1. is a pointer to a private SQL area that stores information about the processing of a SELECT or
2. (DML)data manipulation language statement (INSERT, UPDATE, DELETE, or MERGE).

Cursor management of DML statements is handled by Oracle Database, but PL/SQL offers several ways to define and manipulate cursors to execute SELECT statements.

We will now look at an example that uses a cursor to extend the example from the previous lesson. In this example, we define a cursor based on the SALE_HEADER table. This cursor finds a particular record (where sale_id = 34) and updates the record within the PL/SQL block.

DECLARE
the_customer customer_type;
id_for_sale number(10);
CURSOR cur_cust_type IS 
SELECT REF(sh) refer, sh.sale_id 
INTO   the_customer, id_for_sale
FROM   sale_header sh
FOR UPDATE OF sh.tax_amount;
BEGIN
    FOR get_cust_type IN cur_cust_type LOOP
     IF get_cust_type.sale_id = 34 THEN
UPDATE sale_header sh
SET tax_amount = 200
WHERE sale_id = 34
AND REF(sh) = get_cust_type.refer;
     END IF;
    END LOOP;
    COMMIT;
END;
/

The next lesson wraps up this module.
Read the following section to learn about how to build a cursor block that queries multiple object tables.
Build PL/SQL block

By default, PL/SQL code is compiled and stored in the form of byte code ready for execution. During the execution process, this byte code is interpreted, a process which requires time and resources. The process of native compilation converts PL/SQL stored procedures to Pro*C, which is then compiled to native code shared libraries, resulting in performance increases for the procedural code. The extent of the performance increase depends on the content of the PL/SQL, with the best results shown in code containing loops, logic, mathematical operations and fewer database operations (SQL).
The setup required for native compilation depends on the version of Oracle being used. In Oracle 9i several parameters must be set and on some platforms the associated makefile may need adjustment, whereas Oracle 10g has made several parameters obsolete and the makefile rarely needs modification.

-- Oracle 9i setup.

ALTER SYSTEM SET plsql_native_make_utility = 'make';
 ALTER SYSTEM SET plsql_native_make_file_name = 
   '/u01/app/oracle/product/9.2.0/plsql/spnc_makefile.mk';
 ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB9I/native';

-- Oracle 10g setup.
 ALTER SYSTEM SET plsql_native_library_dir = '/u01/oradata/DB10G/native'

One example of how to build your PL/SQL block looks like the following:

DECLARE
 prod_id number(10);
BEGIN
 SELECT product_id into prod_id
 FROM product_obj_table
 WHERE product_name = 'Dog';
 UPDATE pet_care_log_obj_table
 SET  log_text = 'This dog must be given special care 
  and attention since he is partially blind.'
 WHERE product_id = (SELECT REF(prod) FROM product_obj_table
  prod WHERE product_id = prod_id);
 COMMIT;
END;
/