The Syntax for the WRITE Procedure

PROCEDURE WRITE(BLOB_Locator,
                 Amount IN OUT INTEGER,
                    Starting_Position IN INTEGER,
                 Buffer IN RAW);

PROCEDURE WRITE(CLOB_Locator CHARACTER SET Set_Desired,
                 Amount IN OUT INTEGER,
                     Starting_Position IN INTEGER,
                 Buffer IN VARCHAR2 CHARACTER SET CLOB_Locator%CHARSET);

The parameter BLOB/CLOB_Locator is the locator assigned to the LOB. The Set_Desired parameter is the character set you want to use. If no character set is specified, the Oracle server's character set is used. The variable Amount is how many bytes to write to the LOB. Starting_Position is the position you want to write in the LOB. The Buffer parameter is the buffer of the data to write to the LOB.

Examples of Internal LOBs Using the
DBMS_LOB Package

You can now test all these functions and procedures using an anonymous PL/SQL block in the next several examples for internal LOBs.

Creating the Table

Before you can work with LOBs, you first need to create and populate a table. Before you run any of these examples, make sure that you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt so that you can see that the examples are working as they execute. Execute the code in Listing 15.6 to create the table called LOBS.

INPUT
Listing 15.6. Internal LOB table creation.

CREATE TABLE LOBS(
     lob_index INTEGER,
     CLOB_Locator CLOB)
/

After the code has completed execution, your output should say Table Created. You just created a table with two columns. The first column, lob_index, will store an integer value that will be used to identify the LOB. The second column is where the locator is stored for the CLOB datatype.

Adding Data to the Table

You can now add data of type CLOB to the table. Execute the code in Listing 15.7 to populate the LOBS table with some preliminary data.

INPUT
Listing 15.7. Populating the CLOB table.

INSERT INTO LOBS VALUES(1,'Teach Yourself Oracle8 in 21 Days')
/
INSERT INTO LOBS VALUES(2,'Oracle Data Warehousing Unleashed')
/
INSERT INTO LOBS VALUES(3,'Teach Yourself Database Development with
     Oracle in 21 Days')
/
INSERT INTO LOBS VALUES(4,'Oracle Unleashed 2E')
/
INSERT INTO LOBS VALUES(5,EMPTY_CLOB())
/
INSERT INTO LOBS VALUES(6,'EMPTY_CLOB())
/

The last two items initialize a locator with no data. To verify that the LOBS table was populated, at the SQL prompt, enter

SELECT * FROM LOBS;

OUTPUT
Your output should be

LOB_INDEX CLOB_LOCATOR
--------- -----------------------------------------------------------
        1 Teach Yourself Oracle8 in 21 Days
        2 Oracle Data Warehousing Unleashed
        3 Teach Yourself Database Development with Oracle in 21 Days
        4 Oracle Unleashed 2E
        5
        6

Populating the LOBS Table with the COPY Procedure

Listing 15.8 contains an example with internal LOBs, which uses the procedure COPY to copy two rows where no data exists.

INPUT
Listing 15.8. Copying internal LOBs.

DECLARE
    Source_Lob   CLOB;
    Dest_Lob   CLOB;
    Copy_Amount INTEGER;
BEGIN
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 1;
    Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
    DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
    COMMIT;

-- Start second copy process
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE;
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 2;
    Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
    DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
    COMMIT;
END;

To verify that the COPY procedure worked, at the SQL prompt type

SELECT * FROM LOBS;

OUTPUT
Your output should be

LOB_INDEX CLOB_LOCATOR
--------- ---------------------------------------------------------------
        1 Teach Yourself Oracle8 in 21 Days
        2 Oracle Data Warehousing Unleashed
        3 Teach Yourself Database Development with Oracle in 21 Days
        4 Oracle Unleashed 2E
        5 Teach Yourself Oracle8 in 21 Days
        6 Oracle Data Warehousing Unleashed

ANALYSIS
Two LOBs are defined as type CLOB, which will store the locator for the source and destination LOBs. The Copy_Amount variable will store how much of the Source_Lob is to be copied. In this case, you assign this value to the length of the Source_Lob by using the GETLENGTH function. The locators are then read into the Source_Lob and Dest_Lob. The COPY procedure then copies from the source to the destination LOB, and then COMMIT commits the transaction.

Manipulating Internal LOBs with APPEND and WRITE

Now, you can practice appending from one LOB to another, and even writing to a LOB. Execute the code in Listing 15.9.

INPUT
Listing 15.9. Appending and writing to LOBs.

DECLARE
/* This appends the contents of Row 1 to the contents of Row 5.
   In addition, it writes text at the end of the values in
   Row 6.  */

    Source_Lob   CLOB;
    Dest_Lob   CLOB;
    Write_Amount INTEGER := 10;
    Writing_Position INTEGER ;

                                               continues

Listing 15.9. continued

    Buffer VARCHAR2(10) := `Added Text';
BEGIN
-- Append from Row 1 to Row 5
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 1;
    DBMS_LOB.APPEND(Dest_LOB, Source_LOB);
    COMMIT;
-- Write to a LOB
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE;  -- Locks Row for Update

    Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1;
    DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer);
    COMMIT;
END;

To verify that the WRITE and APPEND procedures worked, at the SQL prompt type

SELECT * FROM LOBS;

OUTPUT
Your output should be

LOB_INDEX CLOB_LOCATOR
--------- -----------------------------------------------------------
        1 Teach Yourself Oracle8 in 21 Days
        2 Oracle Data Warehousing Unleashed
        3 Teach Yourself Database Development with Oracle in 21 Days
        4 Oracle Unleashed 2E
        5 Teach Yourself Oracle8 in 21 DaysTe
          ach Yourself Oracl
          e8 in 21 Days
        6 Oracle Data Warehousing UnleashedAdded Text

ANALYSIS
As you can see from the output, you appended row 1 to row 5, and you also added text to row 6. For the purposes of the APPEND procedure, two variables of type CLOB are created for the source and destination location. For the WRITE procedure, three additional variables are created to hold how much data to write stored in Write_Amount, where to start writing in the CLOB stored in Writing_Position, and the text to write to the CLOB stored in the Buffer variable. The procedure then copies the source and destination locators into the corresponding variables and then calls the APPEND procedure. The transaction is then committed.

The second part of the procedure selects the row where text will be added, locks the row for updating, assigns the starting position to the length of the contents + 1 (so no data is overwritten), and then calls the WRITE procedure. This transaction is then committed.