Skip to main content

Convert BLOB to CLOB in Oracle

Solution:

Step 1: Create plsql function blob_to_clob. It should return the CLOB.

 CREATE OR REPLACE
  FUNCTION blob_to_clob(
      blob_in IN BLOB)
    RETURN CLOB
  AS
    v_clob CLOB;
    v_varchar VARCHAR2(32767);
    v_start pls_integer  := 1;
    v_buffer pls_integer := 32767;
  BEGIN
    dbms_lob.createtemporary(v_clob, TRUE);
    FOR i IN 1..ceil(dbms_lob.getlength(blob_in) / v_buffer)
    loop
      v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(blob_in, v_buffer, v_start));
      dbms_lob.writeappend(v_clob, LENGTH(v_varchar), v_varchar);
      v_start := v_start + v_buffer;
    END loop;
    RETURN v_clob;
  END blob_to_clob;

Step 2: Call the function and test it.

Many ways to check this funtionality,

Method 1: 
  • Generate XML data from relational content
  • Convert CLOB (XML data) to BLOB
  • Convert BLOB to CLOB
  • Print the CLOB
DECLARE
  l_xml CLOB;
  l_clob_to_blob BLOB;
  l_blob_to_clob CLOB;
BEGIN
  -- Generate XML
  
  SELECT dbms_xmlgen.getxml('select * from emp') xml 
    INTO l_xml 
    FROM dual;
  
  -- Convert CLOB to BLOB
  
  l_clob_to_blob := clob_to_blob (l_xml);
  
  -- Convert BLOB to CLOB
  
  l_blob_to_clob := blob_to_clob(l_clob_to_blob);
  
  -- Print the CLOB
  print_clob_to_output (l_blob_to_clob);

END;

Method 2: 
  • Store BLOB in database
  • Convert BLOB to CLOB
  • Print the CLOB
CREATE TABLE store_blob_clob_files
  (
    ID NUMBER,
    blob_content BLOB,
    clob_content CLOB
  );

 DECLARE
  l_blob BLOB;
  l_clob_to_blob BLOB;
  l_blob_to_clob CLOB;
BEGIN
  -- Get BLOB file
  
  SELECT blob_content
    INTO l_blob 
    FROM store_blob_clob_files
   WHERE id = 1;
  
    -- Convert BLOB to CLOB
  
  l_blob_to_clob := blob_to_clob(l_blob);
  
  UPDATE store_blob_clob_files 
     SET clob_content = l_blob_to_clob
   WHERE ID = 1;
  COMMIT;
  
  -- Print the CLOB
  print_clob_to_output (l_blob_to_clob);

END;

Output:
Fig 1: Empty CLOB Content
Fig 2: CLOB Loaded

Related Posts:

Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  
5 comments
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename
9 comments
Read more