Delimited Blob data in oracle

Question

I have got + delimited BLOB data in an oracle table.

Data:

2342-34-34+83898oov+4ncjj+jdjjd11kj+20-12-2017

I would like to extract this data and store it in an array

Expected output:

 Array[1]   Array[2]    Array[3]
2342-34-34  83898oov    4ncjj

Do we have any way to achieve this in oracle without extracting data outside DB?

Note: Number of delimiter text varies for each record.

Answer 1

Oracle Setup:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE TYPE cloblist IS TABLE OF CLOB;
/

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
  RETURN CLOB
AS
     c_buffer  CONSTANT PLS_INTEGER := 32767;
     v_clob    CLOB;
     v_varchar VARCHAR2(32767);
     v_start   PLS_INTEGER := 1;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

  FOR i IN 1 .. CEIL( DBMS_LOB.GETLENGTH(blob_in) / c_buffer ) LOOP
    v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, c_buffer, v_start));
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
    v_start := v_start + c_buffer;
  END LOOP;

  RETURN v_clob;
END blob_to_clob;
/
SHOW ERRORS;

CREATE OR REPLACE FUNCTION split_clob(
  i_str    IN  CLOB,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist /* or cloblist */ DETERMINISTIC PIPELINED
AS
  p_start        PLS_INTEGER := 1;
  p_end          PLS_INTEGER;
  c_len CONSTANT PLS_INTEGER := DBMS_LOB.GETLENGTH( i_str );
  c_ld  CONSTANT PLS_INTEGER := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := DBMS_LOB.INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      PIPE ROW ( DBMS_LOB.SUBSTR( i_str, p_end - p_start, p_start ) );
      p_start := p_end + c_ld;
      p_end := DBMS_LOB.INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      PIPE ROW ( DBMS_LOB.SUBSTR( i_str, c_len - p_start + 1, p_start ) );
    END IF;
  END IF;
END;
/

Example Data:

CREATE TABLE test ( data BLOB );

INSERT INTO test VALUES ( UTL_RAW.CAST_TO_RAW( '2342-34-34+83898oov+4ncjj+jdjjd11kj+20-12-2017' ) );

Query 1 - As rows:

SELECT x.COLUMN_VALUE AS value
FROM   test t,
       TABLE( split_clob( blob_to_clob( data ), '+' ) ) x;

Output:

VALUE    
----------
2342-34-34
83898oov  
4ncjj     
jdjjd11kj 
20-12-2017

Query 2 - As columns:

SELECT DBMS_LOB.SUBSTR( str, delimiter1 - 1, 1 ) AS A1,
       DBMS_LOB.SUBSTR( str, delimiter2 - delimiter1 - 1, delimiter1 + 1 ) AS A2,
       DBMS_LOB.SUBSTR( str, delimiter3 - delimiter2 - 1, delimiter2 + 1 ) AS A3
FROM   (
  SELECT str,
         DBMS_LOB.INSTR( str, '+', 1, 1 ) AS delimiter1,
         DBMS_LOB.INSTR( str, '+', 1, 2 ) AS delimiter2,
         DBMS_LOB.INSTR( str, '+', 1, 3 ) AS delimiter3
  FROM   (
    SELECT BLOB_TO_CLOB( data ) AS str
    FROM   test
  )
);

Output:

A1         A2         A3       
---------- ---------- ----------
2342-34-34 83898oov   4ncjj