Appendix AF

Overview

DBMS_LOB is a package supplied to manipulate Large OBjects (LOBs) in the database. LOBs are new data types available with Oracle 8, and upwards. LOBs support the storage, and retrieval of up to 4 GB of arbitrary data in a single database column. They replace the, now deprecated, data types LONG and LONG RAW. LONG types in Oracle had many shorting comings, such as:

  • You could only have one per table

  • You could not manipulate them in a stored procedure once they grew beyond 32 KB

  • You could not piece-wise modify them readily

  • Many database operations, such as INSERT INTO T SELECT LONG_COL FROM T2, were not supported

  • You could not reference them in a WHERE clause

  • You could not replicate them

  • And so on...

The LOB data type overcomes all of these limitations.

Rather than go over each and every function/procedure of the DBMS_LOB package (there are some 25 of them), I am going to answer the most common questions that come up regarding using the DBMS_LOB package and LOBs. Much of it is either self-explanatory, or is well covered in the standard Oracle documentation. For LOBs there are two main documents you are concerned with:

  • Oracle8i Supplied PL/SQL Packages Reference - An overview of the DBMS_LOB package, and every procedure within, along with a definition of all of the inputs and outputs. Handy to have for reference purposes. You should give this a quick read through to get an understanding of the functions you can perform on LOBs.

  • Oracle8i Application Developer's Guide - Large Objects (LOBs) - An entire document dedicated to explaining how to program using LOBs in various languages and environments. A must read for the developer who will be using LOBs.

Additionally, many of the nuances of working with LOBs is language-specific. How you do something in Java, will be different in C, will be different in PL/SQL, and so on. To this end, Oracle Corporation has actually developed an Application Developer's Guide by language, for languages such as PL/SQL, OCI, Pro*C, COBOL, VB, and Java detailing how LOBs interact with each language. There is also a comprehensive Application Developer's Guide on LOBs, as mentioned above, that is useful, regardless of the language used. I would urge anyone who is considering using LOBs in their applications to read this document, as well as the language-specific guide for their language of choice. These documents answer most of the questions you will ask.

What I will cover here are the answers to the frequently asked questions about LOBs, from, 'How can I show them on the web?', to, 'How can I convert between BLOBs and CLOBs?' - things that aren't covered so well in the standard documentation. LOBs are extremely easy to use once you familiarize yourself with the DBMS_LOB package (see the Oracle 8i Supplied PL/SQL Packages Reference for an overview of this package) and if you haven't done so already, you should do so now before reading this section as it assumes you are ready to go and do things with LOBs.

How do I Load LOBs?

There are quite a few methods available for loading LOBs. In Chapter 9 on Data Loading for example, I demonstrate how the SQLLDR tool may be used to load LOBs into the database. Additionally, the Application Developer's Guide for each language provided by Oracle demonstrate how to create and retrieve a LOB using a specific host language (it's a little different in each). In my opinion however, if I had a directory full of files to load, the use of a BFILE, a DIRECTORY object, and the LOADFROMFILE routine would by far be the way to go.

In Chapter 9 on Data Loading, we covered the topic of using DBMS_LOB.LOADFROMFILE in depth. I will refer you to that section for all of the details. Also, the section on Conversions here, contains a full example of loading a CLOB using LOADFROMFILE.

substr

This is just a quick note on the substr function provided by the DBMS_LOB package. Every other substr function I have ever seen (including the one provided with SQL and PL/SQL) has the following arguments in the following order:

substr( the-string, from-character, for-number-of-characters ); 

So, the substr('hello', 3, 2) would be ll - the third and fourth characters (from character 3, for 2 characters). DBMS_LOB.SUBSTR however, defines them as:

dbms_lob.substr( the-lob, for-number-of-characters, from-character ) 

So that same substr with DBMS_LOB would return ell. A very small simple test confirms this behavior:

tkyte@TKYTE816> create table t ( str varchar2(10), lob clob );      Table created.      tkyte@TKYTE816> insert into t values ( 'hello', 'hello' );      1 row created.      tkyte@TKYTE816> select substr( str, 3, 2 ),   2         dbms_lob.substr( lob, 3, 2) lob   3    from t   4  /      SU LOB -- -------------------- ll ell 

I am constantly doing it backwards myself. It is just one of those things we have to remember to watch out for!

SELECT FOR UPDATE and Java

In order to modify a database-based LOB (not a temporary LOB), the row that contains the LOB in the database must be locked by our session. This is a common point of confusion to Java/JDBC programmers. Consider the small Java program below. It simply:

As it turns out - this Java program will always encounter the error:

java Test java.sql.SQLException: ORA-22920: row containing the LOB value is not locked ORA-06512: at "SYS.DBMS_LOB", line 715 ORA-06512: at line 1 

Apparently, the LOB we inserted is not locked by our session any more. This is an unfortunate side effect of the default 'transactional' mode of JDBC - by default it does not support transactions! After every statement, it commits work immediately. In the following application, unless you add conn.setAutoCommit (false); immediately after the getConnection - it will fail. That one line of code should (in my opinion) be the first line of code after every connect in a JDBC program!

import java.sql.*; import java.io.*; import oracle.jdbc.driver.*; import oracle.sql.*;      // You need a table: // create table demo ( id int primary key, theBlob blob ); // in order for this application to execute. class Test {      public static void main (String args [])    throws SQLException , FileNotFoundException, IOException {     DriverManager.registerDriver       (new oracle.jdbc.driver.OracleDriver());          Connection conn = DriverManager.getConnection          ("jdbc:oracle:thin:@aria:1521:ora8i",           "scott", "tiger");          // If this program is to work, uncomment this next line!     // conn.setAutoCommit(false);          Statement stmt = conn.createStatement();          // Insert an empty BLOB into the table     // create it new for the very first time.     stmt.execute     ( "insert into demo (id,theBlob) " +       "values (1,empty_blob())" );          // Now, we will read it back out so we can     // load it.     ResultSet rset = stmt.executeQuery                      ("SELECT theBlob " +                         "FROM demo "+                        "where id = 1 ");          if(rset.next())     {         // Get the BLOB to load into.         BLOB l_mapBLOB = ((OracleResultSet)rset).getBLOB(1);              // Here is the data we will load into it.         File binaryFile = new File("/tmp/binary.dat");         FileInputStream instream =               new FileInputStream(binaryFile);              // We will load about 32 KB at a time. That's         // the most dbms_lob can handle (PL/SQL limit).         int chunk = 32000;         byte[] l_buffer = new byte[chunk];              int l_nread = 0;              // We'll use the easy writeappend routine to add         // our chunk of file to the end of the BLOB.         OracleCallableStatement cstmt =             (OracleCallableStatement)conn.prepareCall             ( "begin dbms_lob.writeappend( :1, :2, :3 ); end;" );              // Read and write, read and write, until done.         cstmt.registerOutParameter( 1, OracleTypes.BLOB );         while ((l_nread= instream.read(l_buffer)) != -1)         {             cstmt.setBLOB(  1, l_mapBLOB );             cstmt.setInt(   2, l_nread );             cstmt.setBytes( 3, l_buffer );             cstmt.executeUpdate();                  l_mapBLOB = cstmt.getBLOB(1);         }         // Close up the input file and callable statement.         instream.close();         cstmt.close();     }     // Close out the statements.     rset.close();     stmt.close();     conn.close (); }      } 

This is a general shortcoming of JDBC, and it affects LOB operations in particular. I cannot tell you how many people are surprised to find that an API would presume to commit for them - something that must be done by the application itself. Only an ex-ODBC programmer might be expecting that! The same thing will happen in ODBC in its default mode of auto commit as well.

Conversions

Frequently, people have their data in a BLOB, and need it for some reason to appear as a CLOB. Typically, someone has loaded a mixture of text and binary data into a BLOB column, and this person would like to parse the text. Parsing the BLOB is difficult since the database will constantly try to convert the raw BLOB data into hexadecimal, which is not the desired effect. In other cases, people have data in a LONG or LONG RAW that they would like to process as if it were a CLOB or BLOB, given the APIs for these types are so superior to anything available for LONGs and LONG RAWs.

Fortunately, these conversions are easy to solve. We can convert:

We'll deal first with the BLOB to VARCHAR2, and vice versa, conversion and then look at the LONG to CLOB, or LONG RAW to BLOB conversion.

From BLOB to VARCHAR2 and Back Again

The UTL_RAW package has two very handy routines in it for us to use with BLOBs. We'll cover this package in more depth later on in thesection on UTL_RAW. These two routines are:

So, if you know the BLOB you have is actually text information, and in the right characterset, and everything, these functions are truly useful. Let's say someone used the LOADFROMFILE routine we briefly looked at earlier to load a series of files into a BLOB column. We would like to have the ability to view them in SQL*PLUS (masking out any 'bad' characters that would cause SQL*PLUS to behave improperly). We can use UTL_RAW to do this for us. First, we will load up some files into a DEMO table:

scott@DEV816> create table demo   2  ( id           int primary key,   3    theBlob      blob   4  )   5  /      Table created.      scott@DEV816> create or replace directory my_files as '/export/home/tkyte';      Directory created.      scott@DEV816> create sequence blob_seq;      Sequence created.      scott@DEV816> create or replace   2  procedure load_a_file( p_dir_name in varchar2,   3                         p_file_name in varchar2 )   4  as   5      l_blob    blob;   6      l_bfile   bfile;   7  begin   8      -- First we must create a LOB in the database. We   9      -- need an empty CLOB, BLOB, or a LOB created via the  10      -- CREATE TEMPORARY API call to load into.  11  12      insert into demo values ( blob_seq.nextval, empty_blob() )  13      returning theBlob into l_Blob;  14  15      -- Next, we open the BFILE we will load  16      -- from.  17  18      l_bfile := bfilename( p_dir_name, p_file_name );  19      dbms_lob.fileopen( l_bfile );  20  21  22      -- Then, we call LOADFROMFILE, loading the CLOB we  23      -- just created with the entire contents of the BFILE  24      -- we just opened.  25      dbms_lob.loadfromfile( l_blob, l_bfile,  26                             dbms_lob.getlength( l_bfile ) );  27  28      -- Close out the BFILE we opened to avoid running  29      -- out of file handles eventually.  30  31      dbms_lob.fileclose( l_bfile );  32  end;  33  / Procedure created.      scott@DEV816> exec load_a_file( 'MY_FILES', 'clean.sql' );      PL/SQL procedure successfully completed.      scott@DEV816> exec load_a_file( 'MY_FILES', 'expdat.dmp' );      PL/SQL procedure successfully completed. 

So, now I have two files loaded up. One is the script I am working on right here - clean.sql. The other is some expdat.dmp (export file) I have. Now I will write a routine that is callable from SQL to allow me to view any arbitrary 4000 byte slice of a BLOB in SQL*PLUS. We can only view 4,000 bytes, as this is a SQL limitation on the size of a VARCHAR2 data type. The CLEAN function below works much as SUBSTR would work on a regular string, but it takes a BLOB as input and optionally FROM_BYTE and FOR_BYTES arguments. These allow us to pick off an arbitrary substring of the BLOB to display. Note here how we use UTL_RAW.CAST_TO_VARCHAR2 to convert the RAW into a VARCHAR2. If we did not use this routine, the RAW bytes would be converted into hexadecimal before being placed into the VARCHAR2 field. By using this routine, we simply 'change the data type' from RAW to VARCHAR2, and no translation whatsoever takes place:

scott@DEV816> create or replace   2  function clean( p_raw in blob,   3                  p_from_byte in number default 1,   4                  p_for_bytes in number default 4000 )   5  return varchar2   6  as   7      l_tmp varchar2(8192) default   8             utl_raw.cast_to_varchar2(   9                 dbms_lob.substr(p_raw,p_for_bytes,p_from_byte)  10                                     );  11      l_char   char(1);  12      l_return varchar2(16384);  13      l_whitespace varchar2(25) default  14                   chr(13) || chr(10) || chr(9);  15      l_ws_char    varchar2(50) default  16                   'rnt';  17  18  begin  19      for i in 1 .. length(l_tmp)  20      loop  21          l_char := substr( l_tmp, i, 1 );  22  23          -- If the character is 'printable' (ASCII non-control)  24          -- then just add it. If it happens to be a \, add another  25          -- \ to it, since we will replace newlines and tabs with  26          -- \n and \t and such, so need to be able to tell the  27          -- difference between a file with \n in it, and a newline.  28  29          if ( ascii(l_char) between 32 and 127 )  30          then  31              l_return := l_return || l_char;  32              if ( l_char = '\' ) then  33                  l_return := l_return || '\';  34              end if;  35  36          -- If the character is a 'whitespace', replace it  37          -- with a special character like \r, \n, \t  38  39          elsif ( instr( l_whitespace, l_char ) > 0 )  40          then  41              l_return := l_return ||  42                     '\' ||  43                     substr( l_ws_char, instr(l_whitespace,l_char), 1 );  44  45          -- Else for all other non-printable characters  46          -- just put a '.'.  47  48          else  49              l_return := l_return || '.';  50          end if;  51      end loop;  52  53      -- Now, just return the first 4000 bytes as  54      -- this is all that the SQL will let us see. We  55      -- might have more than 4000 characters since CHR(10) will  56      -- become \n (double the bytes) and so, this is necessary.  57  58      return substr(l_return,1,4000);  59  end;  60  /      Function created.      scott@DEV816> select id,   2         dbms_lob.getlength(theBlob) len,   3         clean(theBlob,30,40) piece,   4         dbms_lob.substr(theBlob,40,30) raw_data   5    from demo;              ID   LEN PIECE                RAW_DATA ---------- ----- -------------------- ------------------------------          1  3498 \ndrop sequence      0A64726F702073657175656E636520                  blob_seq;\n\ncreate  626C6F625F7365713B0A0A63726561                  table d              7465207461626C652064               2  2048 TE\nRTABLES\n1024\n0 54450A525441424C45530A31303234                  \n28\n4000\n........ 0A300A32380A343030300A0001001F                  ......               00010001000000000000 

As you can see, we can view the textual component of the BLOB in SQL*PLUS as clear text now using CLEAN. If we just use DBMS_LOB.SUBSTR, which returns a RAW, we get a hexadecimal dump. Looking at the hexadecimal dump, we can see the first byte of the first BLOB is 0A, which is a CHR(10), which is a newline. We can see in our text dump of the BLOB, that our CLEAN function converted the 0A into \n (newline). This just confirms our routine is working as expected. Further, in the second BLOB, we can see many binary zeroes (hexadecimal 00) in the raw dump of the expdat.dmp data. We can see that we turned them into . in our CLEAN function, as many of these special characters, if dumped to the terminal directly, would display in a non-sensical fashion.

In addition to the CAST_TO_VARCHAR2 function, UTL_RAW contains the CAST_TO_RAW function. As demonstrated above, you may have plain ASCII text stored in a BLOB. If you want to be able to use STRINGs to update this data, you would have to know how to encode the string in hexadecimal. For example:

scott@DEV816> update demo   2     set theBlob = 'Hello World'   3   where id = 1   4  /    set theBlob = 'Hello World'                  * ERROR at line 2: ORA-01465: invalid hex number 

does not work. The implicit conversion from VARCHAR2 to RAW assumes the string Hello World is a string of hexadecimal characters. Oracle would take the first two bytes, convert them from hexadecimal to decimal, and assign this number as byte 1 of the RAW data, and so on. We could either take the time to figure out what the hexadecimal representation of Hello World was, or we could simply cast our VARCHAR2 into a RAW type - just change the data type and don't change the bytes contained therein. For example:

scott@DEV816> update demo   2     set theBlob = utl_raw.cast_to_raw('Hello World')   3   where id = 1   4  /      1 row updated.      scott@DEV816> commit;      Commit complete.      scott@DEV816> select id,   2         dbms_lob.getlength(theBlob) len,   3         clean(theBlob) piece,   4         dbms_lob.substr(theBlob,40,1) raw_data   5    from demo   6   where id =1;              ID   LEN PIECE                RAW_DATA ---------- ----- -------------------- ------------------------------          1    11 Hello World          48656C6C6F20576F726C64 

Using UTL_RAW.CAST_TO_RAW('Hello World') is typically much easier than converting Hello World into 48656C6C6F20576F726C64.

Converting From LONG/LONG RAW to a LOB

Converting from a LONG or LONG RAW to a LOB is rather straightforward. The supplied SQL function TO_LOB does the job for us. TO_LOB is a rather restricted function however, in that:

The ramification of the first restriction is that you cannot perform a statement such as:

alter table t add column clob_column; update t set clob_column = to_lob( long_column ); alter table t drop column long_column; 

The above will fail with:

ORA-00932: inconsistent datatypes 

during the UPDATE. In order to bulk convert existing tables with LONGs/LONG RAWs, you must create a new table. This is probably for the best in any case, since LONGs and LONG RAWs were stored 'inline', in other words, with the table data itself. If we simply converted them to LOBs and then removed the LONG column, we would leave the table in pretty bad shape. There would be lots of allocated, but not used, space in the table now. Rebuilding these objects is for the best.

The ramification of the second restriction is that you cannot use TO_LOB in a PL/SQL block. In order to use TO_LOB in PL/SQL we must use dynamic SQL. We'll demonstrate this in a moment.

We will take a look at two ways of using TO_LOB in the following examples. One is in the use of the TO_LOB function in a CREATE TABLE AS SELECT or INSERT INTO statement. The other is useful when the source data must remain in a LONG or LONG RAW column for the time being. For example, a legacy application needs it to be in a LONG. You would like other applications to be able to access it as a LOB, giving PL/SQL the opportunity to have full access to it via the piece-wise DBMS_LOB functions, such as READ and SUBSTR for example.

We'll start by synthesizing some LONG and LONG RAW data:

ops$tkyte@DEV816> create table long_table   2  ( id         int primary key,   3    data       long   4  )   5  /      Table created.      ops$tkyte@DEV816> create table long_raw_table   2  ( id        int primary key,   3    data      long raw   4  )   5  /      Table created.      ops$tkyte@DEV816> declare   2      l_tmp    long := 'Hello World';   3      l_raw   long raw;   4  begin   5      while( length(l_tmp) < 32000 )   6      loop   7          l_tmp := l_tmp || ' Hello World';   8      end loop;   9  10      insert into long_table  11      ( id, data ) values  12      ( 1, l_tmp );  13  14      l_raw := utl_raw.cast_to_raw( l_tmp );  15  16      insert into long_raw_table  17      ( id, data ) values  18      ( 1, l_raw );  19  20      dbms_output.put_line( 'created long with length = ' ||  21                             length(l_tmp) );  22  end;  23  / created long with length = 32003      PL/SQL procedure successfully completed. 

Performing a Mass One-Time Conversion Illustration

So, we have two tables, each with one row and either a LONG or a LONG RAW column. We can do a conversion from LONG to CLOB as easily as a CREATE TABLE AS SELECT statement now:

ops$tkyte@DEV816> create table clob_table   2  as   3  select id, to_lob(data) data   4    from long_table;      Table created. 

Additionally, we could have created the table at another point in time, and use the INSERT INTO variant to populate this table:

ops$tkyte@DEV816> insert into clob_table   2  select id, to_lob(data)   3    from long_table;      1 row created. 

The following simply shows that the TO_LOB function does not operate in a PL/SQL block, and that this is to be expected:

ops$tkyte@DEV816> begin   2      insert into clob_table   3      select id, to_lob(data)   4        from long_table;   5  end;   6  / begin * ERROR at line 1: ORA-06550: line 3, column 16: PLS-00201: identifier 'TO_LOB' must be declared ORA-06550: line 2, column 5: PL/SQL: SQL Statement ignored 

This is easy to work around using dynamic SQL (you will just have to dynamically execute the INSERT, not statically as above). Now that we've seen how to convert a LONG or LONG RAW into a CLOB or BLOB, we'll consider performance of the conversion. Typically, tables with LONGs and LONG RAWs are huge. By definition they are big tables - we are using them to store very large objects. They are in many cases, many gigabytes in size. The question is, how can we perform a bulk conversion in a timely fashion? I suggest using the following features:

Here is an example using these features. I have a rather large IMAGE table, which contains many hundreds of uploaded files (uploaded from the Web). The fields in this table are the NAME of the document, the MIME_TYPE (for example, application/MS-Word), the IMG_SIZE of the document in bytes, and finally the document itself in a LONG RAW. I would like to convert this table into an equivalent table where the document is stored in a BLOB column. I might start by creating the new table:

scott@DEV816> CREATE TABLE "SCOTT"."T"   2  ("NAME" VARCHAR2(255),   3   "MIME_TYPE" VARCHAR2(255),   4   "IMG_SIZE" NUMBER,   5   "IMAGE" BLOB)   6  PCTFREE 0 PCTUSED 40   7  INITRANS 1   8  MAXTRANS 255   9  NOLOGGING  10  TABLESPACE "USERS"  11  LOB ("IMAGE") STORE AS  12  (TABLESPACE "USERS"  13   DISABLE STORAGE IN ROW CHUNK 32768  14   PCTVERSION 10  15   NOCACHE  16   NOLOGGING  17  ) ;      Table created. 

Notice the TABLE and the LOB are NOLOGGING - this is important. You can alter them instead of creating them this way. Now, to convert the data from the existing IMAGE table, I would execute:

scott@DEV816> ALTER SESSION ENABLE PARALLEL DML;      Session altered.      scott@DEV816> INSERT /*+ APPEND PARALLEL(t,5) */ INTO t   2  SELECT /*+ PARALLEL(long_raw,5) */   3             name, mime_type, img_size, to_lob(image)   4    FROM long_raw; 

This performs a direct path, parallel insert into non-logged BLOBs. As a matter of comparison, I ran the INSERT INTO with and without logging enabled, and this was the result (using a subset of rows to be converted):

scott@DEV816> create table t   2  as   3  select name, mime_type, img_size, to_lob(image) image   4  from image where 1=0; Table created.      scott@DEV816> set autotrace on      scott@DEV816> insert into t   2  select name, mime_type, img_size, to_lob(image) image   3  from image; 99 rows created.      Execution Plan ----------------------------------------------------------    0      INSERT STATEMENT Optimizer=CHOOSE    1    0   TABLE ACCESS (FULL) OF 'IMAGE'      Statistics ----------------------------------------------------------        1242  recursive calls       36057  db block gets       12843  consistent gets        7870  physical reads    34393500  redo size        1006  bytes sent via SQL*Net to client         861  bytes received via SQL*Net from client           4  SQL*Net roundtrips to/from client           2  sorts (memory)           0  sorts (disk)          99  rows processed 

Note how that generated 34 MB of redo (if you add up the bytes of the 99 images, then I have 32 MB of data). Now, using the CREATE for T I have above with the NOLOGGING clauses and just using a direct path insert, I find:

scott@DEV816> INSERT /*+ APPEND */ INTO t   2  SELECT name, mime_type, img_size, to_lob(image)   3    FROM image;      99 rows created.      Execution Plan ----------------------------------------------------------    0      INSERT STATEMENT Optimizer=CHOOSE    1    0   TABLE ACCESS (FULL) OF 'IMAGE'      Statistics ----------------------------------------------------------        1242  recursive calls       36474  db block gets       13079  consistent gets        6487  physical reads     1355104  redo size        1013  bytes sent via SQL*Net to client         871  bytes received via SQL*Net from client           4  SQL*Net roundtrips to/from client           2  sorts (memory)           0  sorts (disk)          99  rows processed 

I generated about 1 MB of log. This conversion ran dramatically faster, and generated much less redo log. Of course, as is the case with all unrecoverable operations, you must ensure that a database backup takes place in the near future to ensure the recoverability of these new objects. Otherwise, you may find yourself reconverting the converted data in the event of a disk failure!

Note 

The above example is not actually executable by itself. I just happened to have an IMAGE table lying around, which had about 200 MB of data in it. This is used to demonstrate large, one-time conversions, and the differences that NOLOGGING clauses had on the size of the redo log generated.

Performing an 'on the fly' Conversion

In many cases, you would like to be able to access (read) a LONG or LONG RAW from various environments, but find that you cannot. For example, when using PL/SQL, if the LONG RAW exceeds 32KB in size, you will find it to be quite impossible to access it. Other languages and interfaces have issues with LONGs and LONG RAWs as well. Well, using the TO_LOB function and a temporary table, we can easily convert a LONG or LONG RAW into a CLOB or BLOB on the fly. This is very handy for example when using OAS4.x or WebDB with its file upload functionality. These tools will upload documents over the Web into a database table, but unfortunately, the data type of the column they upload into is a LONG RAW. This makes accessing this column via PL/SQL virtually impossible. The functions below show how to provide access to this data via a BLOB, a snap.

We will start with a temporary table to hold the converted CLOB/BLOB, and a sequence to identify our row:

ops$tkyte@DEV816> create global temporary table lob_temp   2  ( id    int primary key,   3    c_lob clob,   4    b_lob blob   5  )   6  /      Table created.      ops$tkyte@DEV816> create sequence lob_temp_seq;      Sequence created. 

Now we'll create functions TO_BLOB and TO_CLOB. These functions use the following logic to convert a LONG or LONG RAW on the fly:

Here is the code for TO_BLOB and TO_CLOB:

ops$tkyte@DEV816> create or replace   2  function to_blob( p_cname in varchar2,   3                    p_tname in varchar2,   4                    p_rowid in rowid ) return blob   5  as   6      l_blob blob;   7      l_id   int;   8  begin   9      select lob_temp_seq.nextval into l_id from dual;  10  11      execute immediate  12         'insert into lob_temp (id,b_lob)  13          select :id, to_lob( ' || p_cname || ' )  14            from ' || p_tname ||  15         ' where rowid = :rid '  16       using IN l_id, IN p_rowid;  17  18      select b_lob into l_blob from lob_temp where id = l_id ;  19  20      return l_blob;  21  end;  22  /      Function created.      ops$tkyte@DEV816> create or replace   2  function to_clob( p_cname in varchar2,   3                    p_tname in varchar2,   4                    p_rowid in rowid ) return clob   5  as   6      l_clob clob;   7      l_id   int;   8  begin   9      select lob_temp_seq.nextval into l_id from dual;  10  11      execute immediate  12         'insert into lob_temp (id,c_lob)  13          select :id, to_lob( ' || p_cname || ' )  14            from ' || p_tname ||  15         ' where rowid = :rid '  16       using IN l_id, IN p_rowid;  17  18      select c_lob into l_clob from lob_temp where id = l_id ;  19  20      return l_clob;  21  end;  22  /      Function created. 

Now, to demonstrate their usage, we can use a simple PL/SQL block. We convert the LONG RAW into a BLOB, and show its length and a little of the data it holds:

ops$tkyte@DEV816> declare   2      l_blob    blob;   3      l_rowid rowid;   4  begin   5      select rowid into l_rowid from long_raw_table;   6      l_blob := to_blob( 'data', 'long_raw_table', l_rowid );   7      dbms_output.put_line( dbms_lob.getlength(l_blob) );   8      dbms_output.put_line(   9           utl_raw.cast_to_varchar2(  10               dbms_lob.substr(l_blob,41,1)  11                                   )  12                          );  13  end;  14  / 32003 Hello World Hello World Hello World Hello      PL/SQL procedure successfully completed. 

The code to test TO_CLOB is virtually the same, with the exception that we do not need to utilize the UTL_RAW functionality:

ops$tkyte@DEV816> declare   2      l_clob    clob;   3      l_rowid rowid;   4  begin   5      select rowid into l_rowid from long_table;   6      l_clob := to_clob( 'data', 'long_table', l_rowid );   7      dbms_output.put_line( dbms_lob.getlength(l_clob) );   8      dbms_output.put_line( dbms_lob.substr(l_clob,41,1) );   9  end;  10  / 32003 Hello World Hello World Hello World Hello      PL/SQL procedure successfully completed. 

How to Write a BLOB/CLOB to Disk

This functionality is missing from the DBMS_LOB package. We have methods to load LOBs from files, but not create a file from a LOB. I mention it here, simply because we have a solution for it in this book. If you refer to Chapters 18 and 19 on C-Based External Procedures and Java Stored Procedures, I provide both the C and Java code for an external procedure that will write any BLOB, CLOB, or TEMPORARY LOB to a file on the server's file system. Both implementations perform the same function - just using different languages. Use whichever is appropriate with your server (for example, if you do not have the Java option, but you have Pro*C and a C compiler, then the C-based external procedure would be more appropriate for you).

Displaying a LOB on the Web Using PL/SQL

This is a frequently asked question. This example assumes you have one of the following installed and running on your system:

Without one of the above three, this example will not work. It relies on the PL/SQL Web Toolkit (commonly referred to as the HTP functions), and the PL/SQL cartridge or module.

Another assumption we must make is that the character set of the web server (the client of the database) is the same as the database itself. This is due to the fact that the PL/SQL cartridge or module uses VARCHAR2s as the data type to return pages from the database. If the client's character set (the web server is the client in this case) is different from the database's character set, then character set conversion will take place. This conversion will typically corrupt a BLOB. For example, say you are running the web server on Windows NT. The typical character set for a client on Windows NT is WE8ISO8859P1 - Western European 8bit. Now, say the database is running on Solaris. The default and typical character set on that platform is US7ASCII - a 7bit character set. If you attempt to return a BLOB through a VARCHAR2 interface given these two character sets, you'll find that the 'high bit' is stripped off of the data as it comes out of the database. The data will be changed. Only if both the client (the web server) and the database server have the same character set will the data be passed 'as is', unchanged.

So, given that you have the above two assumptions satisfied, we can now see how to use the PL/SQL web toolkit to display a BLOB on the Web. We'll continue using the example from above (conversions) with the DEMO table. We'll load one more file:

ops$tkyte@DEV816> exec load_a_file( 'MY_FILES', 'demo.gif' );      PL/SQL procedure successfully completed. 

a GIF file. Now, we need a package that can retrieve this GIF, and display it on the Web. It might look like this:

ops$tkyte@DEV816> create or replace package image_get   2  as   3      -- You might have a procedure named   4      -- after each type of document you want   5      -- to get, for example:   6      -- procedure pdf   7      -- procedure doc   8      -- procedure txt   9      -- and so on. Some browsers (MS IE for example)  10      -- seem to prefer file extensions over  11      -- mime types when deciding how to handle  12      -- documents.  13      procedure gif( p_id in demo.id%type );  14  end;  15  /      Package created.      ops$tkyte@DEV816> create or replace package body image_get   2  as   3   4  procedure gif( p_id in demo.id%type )   5  is   6      l_lob   blob;   7      l_amt   number default 32000;   8      l_off   number default 1;   9      l_raw   raw(32000);  10  begin  11  12      -- Get the LOB locator for  13      -- our document.  14      select theBlob into l_lob  15        from demo  16       where id = p_id;  17  18      -- Print out the mime header for this  19      -- type of document.  20      owa_util.mime_header( 'image/gif' );  21  22      begin  23          loop  24              dbms_lob.read( l_lob, l_amt, l_off, l_raw );  25  26              -- It is vital to use htp.PRN to avoid  27              -- spurious line feeds getting added to your  28              -- document.  29              htp.prn( utl_raw.cast_to_varchar2( l_raw ) );  30              l_off := l_off+l_amt;  31              l_amt := 32000;  32          end loop;  33      exception  34          when no_data_found then  35              NULL;  36      end;  37  end;  38  39  end;  40  /      Package body created. 

So, now if I had a DAD (Database Access Descriptor; part of the normal setup for the PL/SQL cartridge and module) set up called mydad I can use the URL:

http://myhost:myport/pls/mydata/image_get.gif?p_id=3 

to retrieve my image. Here we are passing P_ID=3 argument into image_get.gif, asking it to find the LOB locator we stored in the row with id=3. We could embed this image in a page using the IMG tag as such:

<html> <head><title>This is my page</title></head> <body> Here is my GIF file <img src=http://myhost:myport/pls/mydata/image_get.gif?p_id=3> </body> </html>

Summary

LOBs provide much more functionality than the now deprecated LONG data type. This section answered some of the questions I receive frequently regarding LOB manipulations. We discussed how to load LOBs into the database. We saw how to convert from a BLOB to a CLOB, and back again. We investigated how you might efficiently convert all of your existing legacy LONG and LONG RAW data into CLOB and BLOB data using unrecoverable and parallel operations. Lastly, we discussed how you might use the PL/SQL Web Toolkit to retrieve the contents of a CLOB or BLOB, and display this on a web page.



Expert One on One Oracle
Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
Year: 2005
Pages: 41
Authors: Richard Laermer, Michael Prichinello
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net