Menu ▾ ▴

Home

Michael

Welcome to LOB2Table!

Requirements

LOB2Table is designed to work with Oracle Database 10g Release 1 and up in any edition.
To successfully install LOB2Table your installation schema has to be granted the following system privileges:

  • CREATE TYPE
  • CREATE PROCEDURE

Additionally the EXECUTE object privilege on the following packages is required:

  • SYS.DBMS_LOB
  • SYS.UTL_I18N

By default, these privileges are already granted to PUBLIC so it may not be necessary to grant them explicitly.

Components

LOB2Table consists of the following components:

  • Object Types:
    • lob_row
    • lob_rows
    • lob_column
    • lob_columns
  • Package: lob2table

You use the various functions that the package lob2table provides to get the CSV or fixed column data from LOBs as tables.

For every function in lob2table there is a "sibling" function with a "2"-suffix that returns the same data in a non-pipelined way. This simplifies usage in pure PL/SQL code.

Usage

LOB2Table supports the following data strcuture in LOBs:

  • Separated columns (by using the "separatedcolumns" function)
  • Fixed columns (by using the "fixedcolumns" function)
    • with a row separator, or
    • with a fixed row width (in characters)

The separatedcolumns and fixedcolumns functions are overloaded for CLOBs, BLOBs, BFILEs and VARCHAR2s as data sources.
For binary data sources (like BLOBs or BFILEs) you should specify the correct character set. If you do not do this, LOB2Table will assume that the data is encoded with the database character set.
LOB2Table will automatically ignore (i.e. remove) any Byte Order Mark (aka BOM or Unicode character U+FEFF) at the beginning of LOBs or VARCHAR2s.
For details about the provided functions take a look at the package specification.

Examples

The following examples assume that a table DEPT with the following columns and data exists.

Column Data Type
DEPTNO NUMBER(2,0)
DNAME VARCHAR2(14 BYTE)
LOC VARCHAR2(13 BYTE)
MYCLOB CLOB
DEPTNO DNAME LOC MYCLOB
10 ACCOUNTING NEW YORK 1234567890,Data,12.3↵
0987654321,"Sampe•1,•started",9.5↵
456,"Test""1""",7↵
20 RESEARCH DALLAS 12345Data1••321↵
123••Data32112↵
30 SALES CHICAGO 123456ABCDEF789XYZ654321GHIJ••987ZXY123456ABCDEF78•XYZ
40 OPERATIONS BOSTON (null)

As you may know this is the famous DEPT table of the SCOTT schema where I added a CLOB column named "MYCLOB". Note that - for clarity - spaces in the MYCLOB column are denoted as bulls (•).
Consider the content of the MYCLOB column:

  • The row with DEPTNO = 10 contains separated data where the row separator is the new line character (↵), i.e. chr(10), and the column separator is the comma character. Additionally, as the (optional) text delimiter the double-quote character is used.
  • The row with DEPTNO = 20 contains fixed column width data where the row separator is the new line character (↵), i.e. chr(10). The widths of the columns are 5, 7 and 3 characters respectively.
  • The row with DEPTNO = 30 contains fixed column width data with a fixed row width of 18 characters. The widths of the columns are 6, 6, 3 and 3 characters respectively.
  • The rows with DEPTNO = 40 does not contain any data, in other words is NULL.

The non-printable new lines (↵) inside the MYCLOB column are UNIX-like LF, i.e. chr(10), characters.
Now consider the following query and its result:

select d.deptno, d.dname,
       t.row_no, t.column1, t.column2, t.column3, t.column4
from   dept d
       cross join table(
         lob2table.separatedcolumns(
           d.myclob, /* the data LOB */
           chr(10),  /* row separator */
           ',',      /* column separator */
           '"'       /* delimiter (optional) */
         )
       ) t
where  d.deptno in(10, 40)
DEPTNO DNAME ROW_NO COLUMN1 COLUMN2 COLUMN3 COLUMN4
10 ACCOUNTING 1 1234567890 Data 12.3 (null)
10 ACCOUNTING 2 0987654321 Sampe 1, started 9.5 (null)
10 ACCOUNTING 3 456 Test"1" 7 (null)

Also an outer join logic is possible:

select d.deptno, d.dname,
       t.row_no, t.column1, t.column2, t.column3, t.column4
from   dept d
       left outer join table(
         lob2table.separatedcolumns(
           d.myclob, /* the data LOB */
           chr(10),  /* row separator */
           ',',      /* column separator */
           '"'       /* delimiter (optional) */
         )
       ) t on(1=1)
where  d.deptno in(10, 40)
DEPTNO DNAME ROW_NO COLUMN1 COLUMN2 COLUMN3 COLUMN4
10 ACCOUNTING 1 1234567890 Data 12.3 (null)
10 ACCOUNTING 2 0987654321 Sampe 1, started 9.5 (null)
10 ACCOUNTING 3 456 Test"1" 7 (null)
40 OPERATIONS (null) (null) (null) (null) (null)

Let's query the fixed column width data:

select d.deptno, d.dname,
       t.row_no, t.column1, t.column2, t.column3, t.column4
from   dept d
       cross join table(
         lob2table.fixedcolumns(
           d.myclob /* the data LOB */,
           chr(10), /* row separator */
           lob_columns(lob_column(1, 5),  /* first column */
                       lob_column(6, 7),  /* second column */
                       lob_column(13, 3)) /* third column */
         )
       ) t
where  d.deptno = 20
DEPTNO DNAME ROW_NO COLUMN1 COLUMN2 COLUMN3 COLUMN4
20 RESEARCH 1 12345 Data1•• 321 (null)
20 RESEARCH 2 123•• Data321 12 (null)

Note here the trailing spaces (denoted as •) - LOB2Table does not trim them. It's up to you, the developer, to decide if, how and when you want to trim them away.

And now we'll query the fixed row width content:

select d.deptno, d.dname,
       t.row_no, t.column1, t.column2, t.column3, t.column4
from   dept d
       cross join table(
         lob2table.fixedcolumns(
           d.myclob /* the data LOB */,
           18, /* row width */
           lob_columns(lob_column(1, 6),  /* first column */
                       lob_column(7, 6),  /* second column */
                       lob_column(13, 3), /* third column */
                       lob_column(16, 3)) /* forth column */
         )
       ) t
where  d.deptno = 30
DEPTNO DNAME ROW_NO COLUMN1 COLUMN2 COLUMN3 COLUMN4
30 SALES 1 123456 ABCDEF 789 XYZ
30 SALES 2 654321 GHIJ•• 987 ZXY
30 SALES 3 123456 ABCDEF 78• XYZ

Note here again the trailing spaces.

Limitations

Because of some SQL and PL/SQL restrictions in Oracle there are two limitations of LOB2Table:

  • A data row inside the LOBs cannot consist of more than 32767 bytes.
    If a larger row is encountered then the exception row_error is thrown. There is currently no way to evade this restriction.
  • A data column cannot consist of more than 4000 bytes.
    If a larger column is encountered then the exception column_error is thrown.
    There is, however, the optional parameter p_truncate_columns in all the provided functions that allows you to get these columns, but they are truncated to fit into 4000 bytes.
    To active this behaviour that is not active by default, set the parameter to a not null value unequal to zero, i.e. 1.
    Using this functionality is seldomly useful or necessary and may degrade performance.

In almost all cases these limitations are not a problem.

Project Members: