Convert CLOB to BLOB in Oracle

Step 1:- Create function

CREATE OR REPLACE FUNCTION CLOB_TO_BLOB(P_CLOB_IN IN CLOB) RETURN BLOB IS

  V_BLOB           BLOB;

  V_OFFSET         INTEGER;

  V_BUFFER_VARCHAR VARCHAR2(32000);

  V_BUFFER_RAW     RAW(32000);

  V_BUFFER_SIZE    BINARY_INTEGER := 32000;

BEGIN

  IF P_CLOB_IN IS NULL THEN

    RETURN NULL;

  END IF;

   

  DBMS_LOB.CREATETEMPORARY(V_BLOB, TRUE);

  V_OFFSET := 1;

  FOR I IN 1 .. CEIL(DBMS_LOB.GETLENGTH(P_CLOB_IN) / V_BUFFER_SIZE) LOOP

    DBMS_LOB.READ(P_CLOB_IN, V_BUFFER_SIZE, V_OFFSET, V_BUFFER_VARCHAR);

    V_BUFFER_RAW := UTL_RAW.CAST_TO_RAW(V_BUFFER_VARCHAR);

    DBMS_LOB.WRITEAPPEND(V_BLOB,

                         UTL_RAW.LENGTH(V_BUFFER_RAW),

                         V_BUFFER_RAW);

    V_OFFSET := V_OFFSET + V_BUFFER_SIZE;

  END LOOP;

  RETURN V_BLOB;

END CLOB_TO_BLOB;

Step 2:- Test

DECLARE

  F_INBLOB     UTL_FILE.FILE_TYPE;

  S_INBLOB     CLOB;

  O_STRINGBLOB CLOB;

BEGIN

  -- Reading file from database server file directory path

  F_INBLOB := UTL_FILE.FOPEN('DirName', 'FileName.txt', 'R');

  LOOP

    BEGIN

      UTL_FILE.GET_LINE(F_INBLOB, S_INBLOB);

      O_STRINGBLOB := O_STRINGBLOB || S_INBLOB || CHR(10);

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        EXIT;

    END;

  END LOOP;

  UTL_FILE.FCLOSE(F_INBLOB);

  UPDATE TestTable

     SET BLOB_FILE = CLOB_TO_BLOB(O_STRINGBLOB)

   WHERE ID = '1';

  COMMIT;

END;

--Create directory in  database server

create directory DirName as 'D:\Test';

grant read on directory DirName to dba;

grant write on directory DirName to dba;

'D:\Test' is a file path on the Server, where the file is located.

--Drop directory from database server

drop directory DirName;

--To Get file directory name run following query

SELECT * FROM ALL_DIRECTORIES

--Note

You require DB Administrator right or DB Administrator has given you a right to access the file directory path from database server to read the file.

If you don't have right, you will get the error "invalid directory path"