Once you have a valid LOB locator, you can write data into that LOB using one of two procedures from the built-in DBMS_LOB package:
DBMS_LOB.WRITE
Allows you to write data randomly into a LOB
DBMS_LOB.WRITEAPPEND
Allows you to append data to the end of a LOB
Following is an extension of the previous examples in this chapter. It begins by creating a LOB locator for the directions column in the waterfalls table. After creating the locator, we use DBMS_LOB.WRITE to begin writing directions to Munising Falls into the CLOB column. We then use DBMS_LOB.WRITEAPPEND to finish the job:
DECLARE directions CLOB; amount BINARY_INTEGER; offset INTEGER; first_direction VARCHAR2(100); more_directions VARCHAR2(500);BEGIN --Delete any existing rows for 'Munising Falls' so that this --example can be executed multiple times DELETE FROM waterfalls WHERE falls_name='Munising Falls'; --Insert a new row using EMPTY_CLOB() to create a LOB locator INSERT INTO waterfalls (falls_name,falls_directions) VALUES ('Munising Falls',EMPTY_CLOB()); --Retrieve the LOB locator created by the previous INSERT statement SELECT falls_directions INTO directions FROM waterfalls WHERE falls_name='Munising Falls'; --Open the LOB; not strictly necessary, but best to open/close LOBs. DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE); --Use DBMS_LOB.WRITE to begin first_direction:= 'Follow I-75 across the Mackinac Bridge.'; amount:= LENGTH(first_direction); --number of characters to write offset:= 1; --begin writing to the first character of the CLOB DBMS_LOB.WRITE(directions, amount, offset, first_direction); --Add some more directions using DBMS_LOB.WRITEAPPEND more_directions:= ' Take US-2 west from St. Ignace to Blaney Park.' || ' Turn north on M-77 and drive to Seney.' || ' From Seney, take M-28 west to Munising.'; DBMS_LOB.WRITEAPPEND(directions, LENGTH(more_directions), more_directions); --Add yet more directions more_directions:= ' In front of the paper mill, turn right on H-58.' || ' Follow H-58 to Washington Street. Veer left onto' || ' Washington Street. You''ll find the Munising' || ' Falls visitor center across from the hospital at' || ' the point where Washington Street becomes' || ' Sand Point Road.'; DBMS_LOB.WRITEAPPEND(directions, LENGTH(more_directions), more_directions); --Close the LOB, and we are done. DBMS_LOB.CLOSE(directions);END;In this example, we used both WRITE and WRITEAPPEND solely to demonstrate the use of both procedures. Because our LOB had no data to begin with, we could have done all the work using only WRITEAPPEND. Notice that we opened and closed the LOB; while this is not strictly necessary, it is a good idea, especially if you are using Oracle Text. Otherwise, any Oracle Text domain- and function-based indexes will be updated with each WRITE or WRITEAPPEND call, rather than being updated once when you call CLOSE.
|
When writing to a LOB, as we have done here, there is no need to update the LOB column in the table. That's because the LOB locator does not change. We did not change the contents of falls_directions (the LOB locator). Rather, we added data to the LOB to which the locator pointed.
LOB updates take place within the context of a transaction. We did not COMMIT in our example code. You should issue a COMMIT after executing the PL/SQL block if you want the Munising Falls directions to remain permanently in your database. If you issue a ROLLBACK after executing the PL/SQL block, all the work done by this block will be undone.
Our example writes to a CLOB column. You write BLOB data in the same manner, except that your inputs to WRITE and WRITEAPPEND should be of the RAW type instead of the VARCHAR2 type.
The following SQL*Plus example shows one way you can see the data just inserted by our example. The next section will show you how to retrieve the data using the various DBMS_LOB procedures.
SQL> SET LONG 2000 SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70 SQL> SELECT falls_directions 2 FROM waterfalls 3 WHERE falls_name='Munising Falls'; FALLS_DIRECTIONS----------------------------------------------------------------------Follow I-75 across the Mackinac Bridge. Take US-2 west from St. Ignaceto Blaney Park. Turn north on M-77 and drive to Seney. From Seney,take M-28 west to Munising. In front of the paper mill, turn right onH-58. Follow H-58 to Washington Street. Veer left onto WashingtonStreet. You'll find the Munising Falls visitor center across from thehospital at the point where Washington Street becomes Sand Point Road.
