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 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.

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;