Lesson 6	Choose a technique for inserting LOB data
Objective	List techniques and tools available for inserting LOB data.

Choose Technique for inserting LOB data (Tools Available)

There are two ways to insert records into a LOB-based object table:

Use the INSERT and UPDATE statements
Use SQL*Loader

In this lesson, we will explore the use of the INSERT statement. We will examine the details of using SQL*Loader in the next lesson. We will examine the details of using the UPDATE statement later in this module.

Using the INSERT statement

With an INSERT statement, you can set the LOB column to NULL, as shown below:

INSERT INTO product_photo_obj_table VALUES 
(11, TO_DATE(‘11/12/99’, 
‘mm/dd/yy’), NULL);

In this case, a NULL value is inserted into the column, rather than a LOB locator. No actual storage is allocated for the LOB data, because there is no locator to point to it. As a result, you cannot use DBMS_LOB on a NULL value. The row must be updated to a valid locator first.

Empty_ functions

One way of updating the row to a valid locator within internal LOBs is with EMPTY_CLOB() and EMPTY_BLOB() functions. These functions act as constructors to generate a LOB locator for the column. Without a locator, the LOB cannot be accessed through PL/SQL. For example, consider the following INSERT statement for internal LOBs:

INSERT INTO product_photo_obj_table VALUES 
(11, TO_DATE(‘11/12/99’, ‘mm/dd/yy’),
EMPTY_BLOB());

For external LOBs, you can initialize the BFILE column to point to an external file by using the BFILENAME() function. For example, consider the following INSERT statement for external LOBs:

INSERT INTO customer_photo_obj_table values (25,
TO_DATE(‘12/11/2000’, ‘mm/dd/yyyy’),
BFILENAME(‘LOB_DIR’, ‘cust_25.gif’);

Note: To insert and update BFILE columns, you must create a directory object and have read permissions for it. This is because the BFILE data is not stored within the database, but within the file system outside the database.
Once a record is inserted into the table with an EMPTY_BLOB() value for the LOB-based column, you can execute an UPDATE statement for loading the correct BLOB (picture, video, or sound ) file from the file system. We will examine the UPDATE statement in greater detail later.
In the next lesson, we will look at loading data into LOB-based columns using SQL*Loader.

Querying Modifying LOBs-Quiz

Click the Quiz button to take a multiple-choice quiz about the material we’ve covered in this module.
Querying Modifying LOBs-Quiz
Constructor: Every time a user-defined data type is created, Oracle generates a method called the constructor method, which is a system-defined method. When invoked, the constructor method creates a new object based on the specification of the object type. Oracle names the new constructor method the same name as the new object type. The parameters are the same as the attributes named within the object type definition.