How to read and write BLOB and CLOB in Oracle

Reading and writing of BLOB and CLOB in Oracle

In Oracle databases, large-type fields (also known as large objects or LOBs) are used to store large amounts of data, such as text, images, videos, etc.

Oracle provides several different large types of fields, mainly including:

（Character Large Object）：

Stores large amounts of character data and can store up to 4 GB of text.
Suitable for scenarios where large segments of text information need to be stored, such as documents, log records, etc.

（Binary Large Object）：

Stores large amounts of binary data and can store up to 4 GB of binary information.
Commonly used to store media files such as pictures, audio, and video.

（National Character Large Object）：

Similar to CLOB, but is used to store data for multibyte character sets (such as Unicode character sets).
Suitable for applications that require storing text in multiple languages.

（Binary File）：

Store references to external files, rather than storing the file contents directly in the database.
BFILE can be stored in the external file system of the database, and the database stores only its path and file name.

Read and insert these large-type fields based on SQL and Java, and convert the read data into string types.

Implementing the insertion and reading of CLOB and BLOB based on SQL

1. Insert large types of data

Insert CLOB data

CLOB is used to store large segments of text, and data can be inserted through simple SQL insert statements:

INSERT INTO my_table (id, clob_column)
VALUES (1, 'This is a large text that can go up to 4 GB');

Insert BLOB data

BLOB is used to store binary data. Since it is more complicated to insert BLOB data directly through SQL, data is usually inserted through files or other methods.

Suppose we want to insert a piece of binary data represented by a hexadecimal string:

INSERT INTO my_table (id, blob_column)
VALUES (1, hextoraw('48656C6C6F20576F726C64')); -- 'Hello World' in hexadecimal

2. Read large-type data and convert it to string

Read CLOB data and convert it to a string

The data in the CLOB field can be read directly and treated as a string:

SELECT clob_column
FROM my_table
WHERE id = 1;

Read BLOB data and convert it to a string (UTL_RAW.CAST_TO_VARCHAR2)

BLOB data is usually binary, if you need to convert it to a string, you can use theUTL_RAW.CAST_TO_VARCHAR2Function, convert it to VARCHAR2 type (note that the BLOB size cannot exceed the limit of VARCHAR2[2000]）：

SELECT UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(blob_column, 4000, 1))
FROM my_table
WHERE id = 1;

heredbms_lob.substrUsed to extract data in BLOB, up to 4000 bytes can be extracted.

You can also use it firstdbms_lob.substr(blob_column)To determine the data length of the BLOB type field, if not exceeding2000, can be used directlyUTL_RAW.CAST_TO_VARCHAR2(blob_column)To convert BLOB type data to character type.

SELECT UTL_RAW.CAST_TO_VARCHAR2(blob_column)
FROM my_table
WHERE id = 1;

Implementing the insertion and reading of CLOB and BLOB based on Java

In Java,PreparedStatementInsert, passResultSetPerform reading.

1. Insert CLOB and BLOB data in Java

Insert CLOB data

Using JavaPreparedStatementInsert string data into the CLOB field:

import ;
import ;
import ;

public class ClobInsertExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "INSERT INTO my_table (id, clob_column) VALUES (?, ?)";
            pstmt = (sql);
            (1, 1);
            (2, "This is a large text for CLOB field.");

            ();
        } catch (Exception e) {
            ();
        } finally {
            try {
                if (pstmt != null) ();
                if (conn != null) ();
            } catch (Exception e) {
                ();
            }
        }
    }
}

Insert BLOB data

BLOB is usually used to store binary data, such as images or files.

Through JavaPreparedStatementInsert binary data:

import ;
import ;
import ;
import ;

public class BlobInsertExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = ("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "INSERT INTO my_table (id, blob_column) VALUES (?, ?)";
            pstmt = (sql);
            (1, 1);

            FileInputStream inputStream = new FileInputStream("path/to/your/");
            (2, inputStream, ());

            ();
            ();
        } catch (Exception e) {
            ();
        } finally {
            try {
                if (pstmt != null) ();
                if (conn != null) ();
            } catch (Exception e) {
                ();
            }
        }
    }
}

2. Read CLOB and BLOB data in Java and convert it into strings

Read CLOB data and convert it to a string

It's very simple to read CLOB data and convert it to a string:

import ;
import ;
import ;
import ;

public class ClobReadExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "SELECT clob_column FROM my_table WHERE id = ?";
            pstmt = (sql);
            (1, 1);
            rs = ();

            if (()) {
                String clobData = ("clob_column");
                (clobData);
            }
        } catch (Exception e) {
            ();
        } finally {
            try {
                if (rs != null) ();
                if (pstmt != null) ();
                if (conn != null) ();
            } catch (Exception e) {
                ();
            }
        }
    }
}

Read BLOB data and convert it to a string

Since BLOB is binary data, you need to read as a byte array first and then convert it into a string

import ;
import ;
import ;
import ;
import ;
import ;

public class BlobReadExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = ("jdbc:oracle:thin:@localhost:1521:orcl", "username", "password");

            String sql = "SELECT blob_column FROM my_table WHERE id = ?";
            pstmt = (sql);
            (1, 1);
            rs = ();

            if (()) {
                InputStream inputStream = ("blob_column");
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

                byte[] buffer = new byte[4096];
                int bytesRead = -1;

                while ((bytesRead = (buffer)) != -1) {
                    (buffer, 0, bytesRead);
                }

                byte[] imageBytes = ();
                String blobAsString = new String(imageBytes, "UTF-8");
                (blobAsString);

                ();
                ();
            }
        } catch (Exception e) {
            ();
        } finally {
            try {
                if (rs != null) ();
                if (pstmt != null) ();
                if (conn != null) ();
            } catch (Exception e) {
                ();
            }
        }
    }
}

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.