LOB Functions

FUNCTION clob_to_blob

CREATE OR REPLACE FUNCTION clob_to_blob (
    p_clob IN CLOB
) RETURN BLOB IS
    -- Temporary BLOB to store the converted data
    l_blob         BLOB;
    -- Offset for the destination BLOB
    l_dest_offset  INTEGER := 1;
    -- Offset for the source CLOB
    l_src_offset   INTEGER := 1;
    -- Language context for the conversion
    l_lang_context INTEGER := dbms_lob.default_lang_ctx;
    -- Character set ID for the BLOB
    l_blob_csid    INTEGER := dbms_lob.default_csid;
    -- Warning message from the conversion process
    l_warning      INTEGER;
    -- Custom exception for invalid CLOB length
    l_length_exception EXCEPTION;
BEGIN
    -- Check if the input CLOB is NULL or empty
    IF p_clob IS NULL
       OR dbms_lob.getlength(p_clob) = 0 THEN
        -- Raise an exception if the CLOB is invalid
        RAISE l_length_exception;
    END IF;
    -- Create a temporary BLOB to store the converted data
    dbms_lob.createtemporary(
        lob_loc => l_blob,
        cache   => TRUE
    );
    -- Convert the CLOB to a BLOB using the specified character set and language context
    -- Note: Using length(p_clob) instead of dbms_lob.lobmaxsize may be more efficient for smaller CLOBs
    dbms_lob.converttoblob(
        dest_lob     => l_blob,
        src_clob     => p_clob,
        amount       => dbms_lob.getlength(p_clob),  -- Changed to dbms_lob.getlength for accuracy
        dest_offset  => l_dest_offset,
        src_offset   => l_src_offset,
        blob_csid    => l_blob_csid,
        lang_context => l_lang_context,
        warning      => l_warning
    );
   -- Return the converted BLOB
    RETURN l_blob;
EXCEPTION
    -- Re-raise the custom exception for invalid CLOB length
    WHEN l_length_exception THEN
        RAISE;
    -- Re-raise any other exceptions that occur during execution
    WHEN OTHERS THEN
        RAISE;
END clob_to_blob;

FUNCTION blob_to_clob

CREATE OR REPLACE FUNCTION blob_to_clob (
    p_blob IN BLOB
) RETURN CLOB IS
    -- Temporary CLOB to store the converted data
    l_file_clob    CLOB;
    -- Maximum size for the CLOB
    l_file_size    INTEGER := dbms_lob.lobmaxsize;
    -- Offset for the destination CLOB
    l_dest_offset  INTEGER := 1;
    -- Offset for the source BLOB
    l_src_offset   INTEGER := 1;
    -- Character set ID for the BLOB
    l_blob_csid    NUMBER := dbms_lob.default_csid;
    -- Language context for the conversion
    l_lang_context NUMBER := dbms_lob.default_lang_ctx;
    -- Warning message from the conversion process
    l_warning      INTEGER;
    -- Length of the input BLOB
    l_length       NUMBER;
    -- Custom exception for invalid BLOB length
    l_length_exception EXCEPTION;
BEGIN
    -- Check if the input BLOB is NULL or empty
    IF p_blob IS NULL
       OR dbms_lob.getlength(p_blob) = 0 THEN
        -- Raise an exception if the BLOB is invalid
        RAISE l_length_exception;
    END IF;
    -- Create a temporary CLOB to store the converted data
    dbms_lob.createtemporary(l_file_clob, TRUE);
     -- Convert the BLOB to a CLOB using the specified character set and language context
    dbms_lob.converttoclob(l_file_clob, p_blob, l_file_size, l_dest_offset, l_src_offset,
                           l_blob_csid, l_lang_context, l_warning);
    -- Return the converted CLOB
    RETURN l_file_clob;
EXCEPTION
    -- Re-raise the custom exception for invalid BLOB length
    WHEN l_length_exception THEN
        RAISE;
    -- Re-raise any other exceptions that occur during execution
    WHEN OTHERS THEN
        RAISE;
END blob_to_clob;