Tuesday 3 July 2018

Sql Script to read BLOB which contains multiple double type arrays data stored in it.

SET serveroutput ON size unlimited;
DECLARE
    v_raw_length     NUMBER := 0;
    v_length         NUMBER := 0;
    v_counter        NUMBER := 0;
TYPE REC_LONG_RAW
IS
  RECORD
  (
    V_LONG_RAW BLOB := empty_blob);
TYPE RAW_TABLE_TYPE
IS
  TABLE OF REC_LONG_RAW INDEX BY BINARY_INTEGER;
  RAW_table RAW_TABLE_TYPE;
BEGIN
 
    BEGIN
      BEGIN
        FOR j IN
        (

select  id,blob_Data raw_blob,
        dbms_lob.getlength(blob_Data) raw_length
        from Blob_Array
        where dbms_lob.getlength(Bulk_Data)>0
        and Blob_Array.Id = 180998
        )
        LOOP
          BEGIN

            v_raw_length := j.raw_length;
              V_LENGTH :=1;
              V_COUNTER:=1;
              WHILE V_RAW_LENGTH>0
              LOOP
              v_raw_length:=v_raw_length -8;
                dbms_output.put_line('v_counter ' || v_counter || '-----' || TO_CHAR(utl_raw.cast_to_binary_double(utl_raw.substr(j.raw_blob,v_length,8),2),'9999999999999999999999999999999999999.999') );
                V_LENGTH := V_LENGTH+8;
                V_COUNTER:=V_COUNTER+1;
              END LOOP;
          END;
        END LOOP;
     
      END;
    EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE (CHR(13)||' ****** Other Error(s) ************ '|| SQLERRM || CHR(13)) ;
    END;

END;

No comments:

Post a Comment