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.

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;