Lesson 6	Creating a PL/SQL block to query object tables
Objective	Write a PL/SQL block to retrieve data from an object table.

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.
The following SlideShow shows an example of such a PL/SQL block:

PL/SQL Cursor Block

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.
Click the link below to learn about how to build a cursor block that queries multiple object tables.
Build PL/SQL block

Native compilation of PL/SQL

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;
/