How to transform a SQL record set in BLOB?

Question

I am using an ORACLE database and I need to get the BLOB (or another LOB format) of a huge recordset (few thousands of rows), which I retrieve from a query. I need to populate the output format as a LOB to then provide it, as input to the DBMS_CRYPTO.Hash function, which generate the hash key I need.

Is it possible to populate the LOB directly with the data of the query parsed to a JSON type? Or do you advise me to use other format to populate the (B)LOB format in an efficient way?

Thanks

Answer 1

The immediate answer is you can serialize to your huge recordset to XML

SELECT DBMS_XMLGEN.getxmltype ('select * from EMPLOYEE') FROM DUAL

Output:

SELECT DBMS_XMLGEN.getxmltype ('select * from EMPLOYEE') FROM DUAL

Output:

<ROWSET>
  <ROW>
    <EMP_ID>1</EMP_ID>
    <EMP_NAME>Employee 1</EMP_NAME>
    <EMP_DEPT_ID>1</EMP_DEPT_ID>
    <EMP_LOC>1</EMP_LOC>
    <EMP_SAL>2000</EMP_SAL>
  </ROW>
  <ROW>
    <EMP_ID>2</EMP_ID>
    <EMP_NAME>Employee 2</EMP_NAME>
    <EMP_DEPT_ID>2</EMP_DEPT_ID>
    <EMP_LOC>2</EMP_LOC>
    <EMP_SAL>1000</EMP_SAL>
  </ROW>
</ROWSET>

After that you can use to_clob or_to_blob function

SELECT TO_CLOB (DBMS_XMLGEN.getxmltype ('select * from EMPLOYEE'))  FROM DUAL

output :(HUGECLOB)