Querying Object Tables in Oracle

Object references are used to reference an object within an object table. To query a table that has a reference to an object, you must use PL/SQL instead of standard SQL. PL/SQL is also required when you query varrays and nested tables.
Let us look at an example of a PL/SQL block where we query a referenced object and update its value:

DECLARE
the_customer customer_type;
BEGIN
   SELECT REF(sh) INTO the_customer
   FROM   sale_header sh
   WHERE  sale_id = 36;
UPDATE sale_header sh
   SET    tax_amount = 200
   WHERE  sale_id = 36
   AND    REF(sh) = the_customer;
   COMMIT;
END;
/

In the example above, we assign the reference to an object to a local variable. Based on this reference, we update tax_amount. In the next lesson, we will look at a PL/SQL block that retrieves data from an object table.

PL/SQL is appropriate or required for querying object tables in Oracle in several situations where more advanced functionality or control is needed beyond simple SQL. Object tables in Oracle are part of Oracle's object-relational model, which allows users to define objects and use them as data types in tables. PL/SQL can be particularly useful when working with these structures.
When PL/SQL is Appropriate or Required for Object Tables:

1. Complex Business Logic:
   • PL/SQL is well-suited for implementing complex business logic that may involve multiple steps, conditional processing, and procedural logic when interacting with object tables.
   • Example: If you need to apply conditional logic or transformations while querying object attributes, PL/SQL allows for handling such tasks in a procedural manner.

       DECLARE
          obj my_object_type;
       BEGIN
          SELECT column_value INTO obj FROM object_table WHERE some_condition;
          -- Perform complex operations on the object
       END;
       

2. Iterating Over Collections or Nested Objects:
   • When you need to iterate over collections or manipulate nested objects (like attributes of objects within the table), PL/SQL provides the control flow mechanisms to manage these more complex data structures.
   • For example, querying an object table that contains nested collections or attributes requires PL/SQL to loop through collections and access or manipulate individual elements.

       DECLARE
          obj my_object_type;
       BEGIN
          FOR rec IN (SELECT * FROM object_table) LOOP
             -- Access or manipulate object attributes or nested collections
             DBMS_OUTPUT.PUT_LINE(rec.some_attribute);
          END LOOP;
       END;
       

3. Procedural Logic for Data Processing:
   • PL/SQL is needed when the querying process involves more than just fetching rows, such as when performing calculations, validations, or calling procedures based on the results of a query.
   • For example, if querying an object table requires invoking other stored procedures or triggers, PL/SQL is essential.

       BEGIN
          FOR rec IN (SELECT * FROM object_table) LOOP
             IF rec.some_attribute IS NOT NULL THEN
                some_procedure(rec);
             END IF;
          END LOOP;
       END;
       

4. Exception Handling:
   • PL/SQL is necessary when working with object tables if there is a need to handle exceptions or errors gracefully during querying or manipulation of objects. SQL alone does not provide robust exception handling, whereas PL/SQL can handle different types of exceptions that might arise when working with object data.

   BEGIN
      SELECT column_value INTO obj FROM object_table WHERE some_condition;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
   	  DBMS_OUTPUT.PUT_LINE('No data found for the specified condition.');
      WHEN OTHERS THEN
   	  DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
   END;
   

5. Data Manipulation and Object Methods:
   • When using object methods (member functions or procedures), you may need PL/SQL to invoke these methods on object instances. SQL cannot directly invoke these methods; PL/SQL is required to manage object lifecycle and operations.

   DECLARE
      obj my_object_type;
   BEGIN
      SELECT column_value INTO obj FROM object_table WHERE some_condition;
      -- Call object methods
      obj.some_method();
   END;
   

6. Bulk Data Processing:
   • PL/SQL’s bulk collection capabilities (BULK COLLECT and FORALL) are useful for efficient querying and manipulation of large volumes of data from object tables. SQL alone may not be efficient for such operations, whereas PL/SQL provides performance enhancements when dealing with bulk operations.

   DECLARE
      TYPE obj_table_type IS TABLE OF object_table%ROWTYPE;
      obj_tab obj_table_type;
   BEGIN
      SELECT * BULK COLLECT INTO obj_tab FROM object_table;
      FORALL i IN obj_tab.FIRST..obj_tab.LAST
   	  INSERT INTO another_table VALUES obj_tab(i);
   END;
   

Summary of When PL/SQL is Appropriate:

• Complex business logic or procedural operations involving object tables.
• Iterating over collections or nested objects within object tables.
• Exception handling when querying or manipulating object data.
• Using object methods (invoking member functions or procedures).
• Bulk processing or performance optimizations when handling large datasets from object tables.

PL/SQL becomes necessary when querying object tables in Oracle requires control flow, procedural logic, exception handling, or advanced data manipulation, which SQL alone cannot handle effectively.

Within a PL/SQL block, the first section is the Declarations section. Within the Declarations section, you define the variables and cursors that the block will use. The Declarations section starts with the keyword declare and ends when the Executable Commands section starts (as indicated by the keyword begin). The Executable Commands section is followed by the Exception Handling section; the exception keyword signals the start of the Exception Handling section. The PL/SQL block is terminated by the end keyword. The structure of a typical PL/SQL block is shown in the following listing:

declare
<declarations section>
begin
<executable commands>
exception
<exception handling>
end;