Convert Oracle CLOB data to string in c#

Question

I am trying to run the below query which returns a DataTable object in C#.

select TO_CLOB(BLOB) FROM TestBlobData where BSIZE=5000

When I try to extract the value out of datatable object, a junk value is seen.

DataTable dataTable = RunQuery(QueryMentionedAbove);
var str = dataTable.Rows[0]["BLOB"].ToString();

When I look at Str , I could not see the converted string. The BLOB is actually a JSON string. I cannot use TO_CHAR or TO_NCHAR because my blob size will be greater than 4000. My expectation is to see the JSON string which I stored it as BLOB.

Please help me in converting the CLOB to string in C# code.

`Rows[0]["BLOB"]` is *already* a string so `ToString()` is essentially just a cast. `ToString()` *doesn't generate JSON* either, which means the contents are *already* JSON — Panagiotis Kanavos, Oct 27 '20 at 16:23
I think you need convert BLOB to CLOB after that use CLOB for Datatable C# — Hung Le, Oct 27 '20 at 16:35
@PanagiotisKanavos, Yes you are correct. Upon peeking into the database output(which I assumed as junk value) I noticed that I am getting the output with NULL character after each character.(Ex: T\0E\0S\0T\0D\0A\0T\0A). Upon replacing the "\0" with "", I could see the JSON string which I stored it as BLOB. — sivavuyyuru, Oct 28 '20 at 15:21

score 0 · Answer 1 · answered Oct 27 '20 at 16:43

You can use the function (the reverse of this answer):

CREATE FUNCTION blob_to_clob(
  value            IN BLOB,
  charset_id       IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID,
  error_on_warning IN NUMBER  DEFAULT 0
) RETURN CLOB
IS
  result       CLOB;
  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  warning      INTEGER;
  warning_msg  VARCHAR2(50);
BEGIN
  DBMS_LOB.CreateTemporary(
    lob_loc => result,
    cache   => TRUE
  );

  DBMS_LOB.CONVERTTOCLOB(
    dest_lob     => result,
    src_blob     => value,
    amount       => LENGTH( value ),
    dest_offset  => dest_offset,
    src_offset   => src_offset,
    blob_csid    => charset_id,
    lang_context => lang_context,
    warning      => warning
  );
  
  IF warning != DBMS_LOB.NO_WARNING THEN
    IF warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
      warning_msg := 'Warning: Inconvertible character.';
    ELSE
      warning_msg := 'Warning: (' || warning || ') during BLOB conversion.';
    END IF;
    
    IF error_on_warning = 0 THEN
      DBMS_OUTPUT.PUT_LINE( warning_msg );
    ELSE
      RAISE_APPLICATION_ERROR(
        -20567, -- random value between -20000 and -20999
        warning_msg
      );
    END IF;
  END IF;

  RETURN result;
END blob_to_clob;
/

Then, if you have the CLOB_TO_BLOB function from this answer and the data:

CREATE TABLE table_name ( value BLOB );

INSERT INTO table_name (value ) VALUES ( CLOB_TO_BLOB( 'abcdefg' ) );

Then:

SELECT BLOB_TO_CLOB( value ) FROM table_name;

Outputs:

| BLOB_TO_CLOB(VALUE) |
| :------------------ |
| abcdefg             |

db<>fiddle here

Thanks a lot. But I cannot use procedure in my case. I figured out the issue. Upon peeking into the database output I noticed that I am getting the output with NULL character after each character.(Ex: T\0E\0S\0T\0D\0A\0T\0A). Upon replacing the "\0" with "", I could see the JSON string which I stored it as BLOB. — sivavuyyuru, Oct 28 '20 at 15:24

Convert Oracle CLOB data to string in c#

1 Answers1

Linked