Desarrollo

Advanced PL/SQL and Oracle 9i ETL
DOUG COSMAN, SAGELOGIX, INC.



This paper will describe some of the more advanced PL/SQL capabilities, which can be exploited to improve application performance as well as add new functionality to the database. Topics covered will include advantages of nested tables over index_by tables, using table functions to return rows from a function, bulk binding,native compilation, returning cursors, and streaming data output using pipelined table functions. While these topics are important in their own right to the sophisticated PL/SQL developer, they are needed as background information for understanding Oracle’s strategy for implementing an ETL solution within the database. It will be shown how all these concepts come together in the new Oracle 9iETL functionality which uses PL/SQL and 9i external tables to transform data without the use of third party ETL tools.


Bulk Binding
Interaction with Oracle in any host language, including PL/SQL, involves the binding of host variables in and out of the SQL engine. An ‘in-bind’ is when we pass a value from a program to the SQL engine, often either to constrain on a column or to specify a valuefor a DML statement. The following UPDATE statement uses in-binds for both purposes.

DECLARE
v_quantity NUMBER := 0;
v_sales_id NUMBER := 2314;


BEGIN
UPDATE f_sales_detail
SET quantity = v_quantity
WHERE sales_id = v_sales_id;
END;


Commonly, in-binds are only of interest because they are essential for SQL statements to be sharable. When DBA’stalk of the importance of applications using ‘bind variables’ it is in the context of in-binds since, in applications that use dynamic SQL, using literals instead of bind variables causes each SQL statement to be parsed. While this is a critical consideration for overall database performance, the relative cost of the bind in this statement is trivial because only a single bind is required regardlessof how many rows are affected by the statement.

An ‘out-bind’ occurs when values are passed from the SQL engine back to the host language. Oracle makes the distinction between values that are passed back via a RETURNING clause in SQL as opposed to when values are passed back by during a fetch operation but for the purpose of this paper I will refer to both of these operations as out-binds.When processing a cursor, application developers can choose to either fetch back values one-at-a-time or returned in a batch operation which will bind back many rows to the host application in a single operation. Before the release of Oracle 8i values being bound out into PL/SQL host variables had to be fetched one at a time. The following CURSOR FOR-LOOP construct is a familiar one.DECLARE
CURSOR cust_cur (p_customer_id NUMBER) IS
SELECT *
FROM f_sales_detail
WHERE customer_id = p_customer_id;


v_customer_id NUMBER := 1234;


BEGIN
FOR rec IN cust_cur (v_customer_id) LOOP
INSERT INTO sales_hist
(customer_id, detail_id, process_date)
VALUES
(v_customer_id, rec.sales_id, sysdate);
END LOOP;END;


In a CURSOR FOR-LOOP, a record variable is implicitly declared that matches the column list of the cursor. On each iteration of the loop, the execution context is switched from the PL/SQL engine to the SQL engine, performing an out-bind of the column values into the record variable once for each loop iteration. Likewise, an in-bind for the insert statement will occur once on eachiteration. Although stored PL/SQL code has the advantage over other host languages of keeping this interaction within the same process, the context switching between the SQL engine and the PL/SQL engine is relatively expensive making the above code very inefficient. In addition, the cursor is defined as SELECT * instead of just selecting from the columns to be utilized which is also inefficient....