But prior oracle they did not support the use sql expressions

	,ch08.25977 Page 156 Wednesday, April 11, 2001 1:45 PM
	156

SQL Expressions and Direct Path Loads

SQL expressions represent a powerful feature of SQL*Loader, but up until the Oracle9i release, you could only use them in conventional path loads. Direct path loads, which we’ll cover in detail in Chapter 10, Direct Path Loads, are in many cases more efficient, but prior to Oracle9i they did not support the use of SQL expressions. In the past, this has caused a great deal of consternation among programmers and DBAs who wanted to take advantage of both fea-tures—SQL expressions and direct path loads—at the same time.

You can use SQL expressions in Oracle8 or Oracle8i, but that forces you to do a conventional path load, so you’ll have to consider the performance hit that you’ll take from that. Weigh that against the performance hit that you’ll take from pursuing the other two options. Then consider your own capabilities. Sure, it may be more efficient to write a program to reformat your data, but what if you are not a programmer? Or, perhaps you have a PL/SQL program-mer in-house who can modify the data, but only after it hits the database. You have to weigh all these options and make some intelligent decision based on your performance needs and your capabilities.

Writing Your Own Functions

	This is the Title of the Book, eMatter Edition
	Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.

		157
	Writing Your Own Functions	157

	To get the minimum price for a given page count, you need to find the row with
	the highest page count that is less than or equal to the number of pages in the
	book. For example, any page count from 400 to 499 carries a minimum price of
	$34.95. At 500 pages, the minimum price jumps to $39.95. The following stored
	PL/SQL function takes current price and page counts as parameters, queries the

price_page_threshold table, and returns the greater of the current price or the mini-mum price:

CREATE OR REPLACE FUNCTION price_check
(price_in NUMBER, pages_in NUMBER)
RETURN NUMBER IS
min_price NUMBER;
BEGIN
/* Retrieve the mininum price for the number of pages in question. */ SELECT ppt_min_price INTO min_price
FROM price_page_threshold
WHERE pages_in >= ppt_pages
AND ppt_pages = (
SELECT MAX(ppt_pages)
FROM price_page_threshold
WHERE pages_in >= ppt_pages);

	This is the Title of the Book, eMatter Edition
	Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.