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"

Returning multiple record sets from Oracle

• Depending on your results set you need to use a number of out parameter cursor in your store procedure.
• In demo store procedure 2 out parameter cursor used.
• When you call store procedure, you will get the results set in the dataset object.

Step 1:- Create Store Procedure

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT1 OUT PKG_NONPINS.CUR_REF,

                                     P_OUT2 OUT PKG_NONPINS.CUR_REF) AS

  Query VARCHAR2(1000);

BEGIN

  Query := ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

  OPEN P_OUT1 FOR Query;

  --OR

  OPEN P_OUT2 FOR ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

END;

Step 2:- Call Store Procedure

OracleConnection con = new OracleConnection(“Connection String”);

con.Open();

OracleCommand cmd = new OracleCommand("USP_TEST", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("P_OUT1", OracleType.Cursor).Direction = ParameterDirection.Output;

cmd.Parameters.Add("P_OUT2", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

Create temporary table in PL/SQL

1. Transaction-specific

create global temporary table tblTempTest

(

Column1 VARCHAR2(50),

Column2 VARCHAR2(50),

Column3 VARCHAR2(50)

)  ON COMMIT DELETE ROWS;

INSERT INTO tblTempTest VALUES ('Row1', 'Row1', 'Row1');

INSERT INTO tblTempTest VALUES ('Row2', 'Row2', 'Row2');

INSERT INTO tblTempTest VALUES ('Row3', 'Row3', 'Row3');

SELECT COUNT(*) FROM tblTempTest;

-- RESULT: 3

COMMIT;

SELECT COUNT(*) FROM tblTempTest;

-- RESULT: 0

2. Session-specific

create global temporary table tblTempTest

(

Column1 VARCHAR2(50),

Column2 VARCHAR2(50),

Column3 VARCHAR2(50)

)  ON COMMIT PRESERVE ROWS;

INSERT INTO tblTempTest VALUES ('Row1', 'Row1', 'Row1');

INSERT INTO tblTempTest VALUES ('Row2', 'Row2', 'Row2');

INSERT INTO tblTempTest VALUES ('Row3', 'Row3', 'Row3');

SELECT COUNT(*) FROM tblTempTest;

-- RESULT: 3

COMMIT;

SELECT COUNT(*) FROM tblTempTest;

-- RESULT: 3

Execute dynamic query in PL/SQL

1. SIMPLE DYNAMIC QUERY

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT OUT PKG_NONPINS.CUR_REF) AS

  Query VARCHAR2(1000);

BEGIN

  Query := ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

  OPEN P_OUT FOR Query;

  --OR

  OPEN P_OUT FOR ' select trunc(sysdate) "sysDate" ,

             trunc(current_date) "curDate"

             from dual';

END;

2. PASS VARIABLE IN DYNAMIC QUERY

CREATE OR REPLACE PROCEDURE USP_TEST(P_OUT OUT PKG_NONPINS.CUR_REF) AS

  Query   VARCHAR2(1000);

  Date    VARCHAR2(1000);

  C_Date  VARCHAR2(1000);

BEGIN

  Date  := trunc(sysdate);

  C_Date:= trunc(current_date);

  Query := ' select ''' || Date || ''' "sysDate" ,

             ''' || C_Date || ''' "curDate"

             from dual';

  OPEN P_OUT FOR Query;

  --OR

  OPEN P_OUT FOR ' select ''' || Date || ''' "sysDate" ,

             ''' || C_Date || ''' "curDate"

             from dual';

END;

Read whole file into a string using utl_file in PL/SQL

declare

  V_FileHandle  utl_file.file_type;

  V_FileDirName VARCHAR2(100);

  V_FileName    VARCHAR2(100);

  V_LineString  CLOB;

  V_OutString   CLOB;

begin

  V_FileDirName  := 'DirName';

  V_FileName := 'FileName.txt';

  V_FileHandle := utl_file.fopen(V_FileDirName, V_FileName, 'R');

  loop

    begin

      utl_file.get_line(V_FileHandle, V_LineString);

   

      V_OutString := V_OutString || V_LineString || chr(10);

   

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        EXIT;

    end;

  end loop;

  --dbms_output.put_line(V_OutString);

  utl_file.fclose(V_FileHandle);

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"

You can use other appropriate data type instead of CLOB, If your file data is lesser to read.

Chr(10) is use for New Line.