Oracle - LOB Datatype

Card Puncher Data Processing


LOB in Oracle

In Oracle, a LOB is simply a pointer that points to an index that is stored in a LOBINDEX segment. The index points to the chunks that make up the LOB that are stored in LOBSEGMENT segment.



Binary large object (BLOB): For unstructured binary data

--EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable
blob := empty_blob();
-- From Varchar
blob := TO_BLOB(UTL_RAW.CAST_TO_RAW('abc'));


Character large object (CLOB): For character data

-- EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable
clob := empty_clob();
-- To_clob
clob := to_clob('abc');


  • Binary file (BFILE): For external files

Discover More
Bi Server Architecture With Client
OBIEE - Blob data type

When using Blob data type, “DISTINCT” clause are not allowed. Let op: OBIEE includes “DISTINCT” clause in SQL queries as soon as the primary key of a table is not selected In the online modeler...
Card Puncher Data Processing
Oracle - DataType

Each value manipulated by Oracle Database has a datatype. Large Object: Data Type Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size...
Card Puncher Data Processing
Oracle - PL/SQL - DML error logging

LOG ERRORS handles errors quickly and simplifies batch loading. When you need to load millions of rows of data into a table, the most efficient way is usually to use an INSERT, UPDATE, or MERGE statement...
Oracle Segment Extent Data Block
Oracle Database - (Logical) Segment (Database Object Storage)

A segment is a logical storage structure that contains data structure (segment type) of database objects. For example: each table's data is stored in its own data segment, while each index's data...
Card Puncher Data Processing
Oracle Database - How to load a CLOB field with an external table

A CLOB (or character large object) is a Oracle datatype that can contain single-byte or multibyte characters with a maximum size of (4 gigabytes - 1) (database block size), then more than the varchar2...
Card Puncher Data Processing
Oracle Database - Java Stored Procedure

Application developers familiar with procedural programming styles developed business application logic using languages like PL/SQL. The business logic they developed was deployed as stored program units...
Card Puncher Data Processing
Oracle Database - Long (Text) and Long Raw Datatype

LOB Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release,...
Card Puncher Data Processing
SQL Loader - Lob File

How to load a lob file with sql loader where: FILLER is a field that fill a variable named ext_fname. It takes the relative path from the sqlloader running directory to the file to load RESUME...

Share this page:
Follow us:
Task Runner