Once the objects are successfully created, the following demonstration code will work.

-- Here’s some things you can do with LOB data.





SET SERVEROUTPUT ON



DECLARE



    ClobLocator CLOB;

    BlobLocator BLOB;

    BfileLocator BFILE;



    ClobBuffer VARCHAR2(255);

    BlobBuffer RAW(32767);

    BfileBuffer RAW(32767);



    Offset NUMBER;



BEGIN

    -- First we need to populate some LOBs



    INSERT INTO lobs (id, clobs)

        VALUES (1, 'A doo doo doo, a daa daa daa.  That’s all I want to

say to you.’);



    INSERT INTO lobs (id, blobs)

        VALUES (2, HEXTORAW('04CC13FF65AB’));



    -- What we’re really doing below is linking the temp.logs file to row

3 using

    -- the BFILENAME function.  The contents of the file will appear when

this column

    -- is queried.



    INSERT INTO lobs (id, bfiles)

        VALUES (3, BFILENAME('BFILE_DIR’, 'test.log’));



    -- Now lets do some stuff with DBMS_LOB!

    -- First well get locators for the LOB data we created.



    -- Notice I’ve selected the CLOB and BLOB using the FOR UPDATE clause

    -- in order to lock the rows.  This is necessary if you plan to do any

       updates.



    SELECT clobs INTO ClobLocator FROM lobs WHERE id = 1 FOR UPDATE;

    SELECT blobs INTO BlobLocator FROM lobs WHERE id = 2 FOR UPDATE;

    SELECT bfiles INTO BfileLocator FROM lobs WHERE id = 3;



    -- Find out how long it is and have a look at it.



    Offset := 1 + DBMS_LOB.GETLENGTH(ClobLocator);

    DBMS_LOB.READ(ClobLocator, Offset, 1, ClobBuffer);

        DBMS_OUTPUT.PUT_LINE(ClobBuffer);



    -- Now lets write some stuff to the end of the CLOB and read it again.



    DBMS_LOB.WRITE(ClobLocator, 34, Offset + 1, '  Sting is quite a

lyricist -- no?’);

        Offset := DBMS_LOB.GETLENGTH(ClobLocator);

    DBMS_LOB.READ(ClobLocator, Offset, 1, ClobBuffer);

    DBMS_OUTPUT.PUT_LINE(ClobBuffer);



    -- Lets get something out of the BFILE.

        -- Unfortunately we can’t look at what we got because it’s RAW

data.



    DBMS_LOB.FILEOPEN(BfileLocator);



    Offset := 1 + DBMS_LOB.GETLENGTH(BfileLocator);

    DBMS_LOB.READ(BfileLocator, Offset, 1, BfileBuffer);

    DBMS_LOB.FILECLOSE(BfileLocator);



    -- We could go on forever, but just one more.

    -- We’ll get a string from the CLOB.



    Offset := DBMS_LOB.INSTR(ClobLocator, 'no’, 1, 1);

    ClobBuffer := DBMS_LOB.SUBSTR(ClobLocator, 2, Offset);

    DBMS_OUTPUT.PUT_LINE(ClobBuffer);



EXCEPTION



    WHEN DBMS_LOB.ACCESS_ERROR THEN

        RAISE_APPLICATION_ERROR(-20100, 'Maximum Lob Size Exceeded’);



    WHEN DBMS_LOB.INVALID_DIRECTORY THEN

        RAISE_APPLICATION_ERROR(-20101, 'Directory is Invalid’);



    WHEN DBMS_LOB.NOEXIST_DIRECTORY THEN

        RAISE_APPLICATION_ERROR(-20103, 'Directory Does Not Exist’);



    WHEN DBMS_LOB.NOPRIV_DIRECTORY THEN

        RAISE_APPLICATION_ERROR(-20104, 'Insufficient Directory

Privileges’);



    WHEN DBMS_LOB.OPERATION_FAILED THEN

        RAISE_APPLICATION_ERROR(-20105, 'Operation on File Failed’);



    WHEN DBMS_LOB.UNOPENED_FILE THEN

        RAISE_APPLICATION_ERROR(-20106, 'File Not Open’);



    WHEN OTHERS THEN

    RAISE;

END;

/

Issuing Dynamic SQL and Data Definition Language Statements

Normally, a PL/SQL block can only contain static DML statements. You cannot issue DDL or dynamic SQL without using the DBMS_SQL package. The reason for this is that PL/SQL blocks are compiled into a format called p-code before they are executed. This process checks object definitions and binds values to all variables. Although this early binding increases the code’s efficiency, it makes it impossible to use dynamic or DDL statements because the object definitions and bind variables of such statements cannot be resolved until runtime.

In order to overcome this limitation, PL/SQL 2.1 introduced the DBMS_SQL package. This package gives the programmer control over the execution phase of SQL statements so that all object definition checks and binding can be done at runtime.