Why Use Recursion?

When the need to call the function by itself does not occur more than 10 to 15 times, recursion provides an elegant solution to a problem. However, there are many reasons why you will probably never use recursion:

• All recursion problems can be solved by a different method, such as writing custom functions, using FOR loops, or using IF statements, all of which are discussed on Day 5, "Using Functions, IF Statements, and Loops." You could also use WHILE or LOOP statements, covered on Day 6, "Implementing Loops and GOTOs," instead of recursion.
• The more complex the calculation, or the more times the function calls itself, the slower the execution of the PL/SQL code.
• Recursion can be difficult for other programmers to follow.

Review of Large Object Datatypes

Oracle8 added four large object datatypes. These datatypes are summarized in Table 15.2. All these datatypes can store data up to 4 gigabytes. This is more than enough to accommodate such items as sound, pictures, and video in your databases. Of the available large object datatypes, three of them are internal to Oracle, and one is external. External refers to a physical file, for which Oracle stores the filename and file location. Internal objects store a locator in the large object column of the table, which points to the actual location of the data in the table.

Table 15.2. Large object datatypes.

Using External Files in Your Oracle8
Database

The BFILE datatype is a large binary object that is external to Oracle. External means that the file is accessible to the operating system and not stored within the Oracle database. The information required to access an external object is the directory object and the filename. Of course, you need to make sure that you have all the privileges associated with accessing the directories and files.

Creating the Directory Object

Before you can access outside files, you need to create a directory object. This object maps a name to a path specified in the CREATE statement. The directory object name can be up to 30 characters, and the filename can be up to 2000 characters maximum.

The Syntax for Creating a Directory Object

CREATE (OR REPLACE) DIRECTORY Directory_Name AS Path

Directory_Name is the name of the directory object you are associating with the path. This directory name will be called by Oracle instead of specifying the actual path. Path is the physical path on the system of any secondary storage devices.

Before you can try several of the examples in this book, you will need to create a directory object called books. This object will hold the data and descriptions of future or current books required for purchase by your organization. To create the directory object, execute the code in Listing 15.3.

INPUT
Listing 15.3. Creating the directory object books.

CREATE OR REPLACE DIRECTORY books_Dir AS `C:\BOOKS'
/

After you have executed the SQL statement, you should see output that states that the directory was created.

Limitations of BFILE

The BFILE datatype does not offer transactional support for COMMIT or ROLLBACK. Also, files are opened as read-only, so you can't write or alter these external files in any manner. Another problem that could occur is a "too many files open" error. In order to avoid this error, you need to edit the INIT.ORA file and change the statement SESSION_MAX_OPEN_FILES=20 to whatever value you require. Also keep in mind any operating system requirements. When you do open files, make sure that you close them; otherwise, they are tracked as opened files even when not in use, and you might get the message "too many files open" when you really are accessing under the limit. This mostly occurs when programs terminate abnormally, and there is nothing in place to close all files when the error occurs.

The DBMS_LOB Package with BFILE

The DBMS_LOB package provided by Oracle allows you to manipulate all types of large objects (LOBs). Because the BFILE datatype is the only external datatype, Oracle provides functions used solely for external LOBs. These are summarized in Table 15.3.

Table 15.3. Functions and procedures used with BFILE.