Convert BLOB into Varchar2 in Oracle

Hello,

In oracle database tables, we have data stored in BLOB datatype. I need to convert the BLOB into VARCHAR2 data type. How can I do that?

I tried this query below but it is throwing errors as I have attached in the screenshot.

Please assist. Thank you

Declare
b BLOB;
c clob;
n number;

cursor c1 is

   SELECT PZPVSTREAM INTO b
     FROM PC
     WHERE PZINSKEY like 'ASSIGN-WORKLIST';

begin
  if (b is null) then
    return null;
  end if;
  if (length(b)=0) then
    return empty_clob();
  end if;
  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  return c;
end;
/
error.PNG
angel7170Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
What you have is an anonymous PL/SQL block.  There is no RETURN clause for those since there is no return value.

Return values are only for FUNCITONS.

Just remove all the if checks and returns OR make it a function.
angel7170Author Commented:
ok, thank you. how do I just print the results? I removed the If's and the returns/

Declare
b BLOB;
c clob;
n number;

cursor c1 is

  SELECT PZPVSTREAM INTO b
     FROM PC
     WHERE PZINSKEY like 'ASSIGN-WORKLIST';
begin

  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));

end;
slightwv (䄆 Netminder) Commented:
If you just want to 'check' it, I would use dbms_output.

Right before the last end:
dbms_output.put_line(c);

You might need to tweak the environment to see everything depending on the tool you are using.

If sqlplus, for example:
set long 10000000
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

angel7170Author Commented:
Thank you. I added the dbms_output line but I get an error as attached. Not sure what I am missing.

Declare
b BLOB;
c clob;
n number;

cursor c1 is

   SELECT PZPVSTREAM INTO b
     FROM PC
     WHERE PZINSKEY like 'ASSIGN-WORKLIST';

begin

  dbms_lob.createtemporary(c,true);
  n:=1;
  while (n+32767<=length(b)) loop
    dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
    n:=n+32767;
  end loop;
  dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
  dbms_output.put_line(c);
end;
error.PNG
slightwv (䄆 Netminder) Commented:
OK, what is line 20?  I'm guessing the dbms_output line.

How big is the CLOB?

Does it run without the dbms_output.put_line?

You might be trying to display more data than is possible with dbms_output and will have to do it in chunks.

Try the first 1000 characters:
  dbms_output.put_line(substr(c,1,1000));
angel7170Author Commented:
I am not sure how big the BLOB column is. It doesn't even run without the output line.
Without the output line, it gives me the same error. so I think it has to do with dbms_log line.
slightwv (䄆 Netminder) Commented:
>>I am not sure how big the BLOB column is
select dbms_lob.getlength(PZPVSTREAM)
FROM PC
WHERE PZINSKEY like 'ASSIGN-WORKLIST';

>>Without the output line, it gives me the same error

OK, what is line 20?

I'm guessing you found that code on the web somewhere.  Maybe that code never worked?

What you have is similar to code I've seen before.  You might try a different web source.
angel7170Author Commented:
The length of BLOB column is 2568.

Line 20 is
dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));

I got this code from this site only. I tried the rest and none seems to work for me :(

Thank you for your help
slightwv (䄆 Netminder) Commented:
Missed the CURSOR.

You declare the cursor and never open it and fetch the contents.  Therefore 'b' doesn't have any data in it.

You also need to free the temporary clob.

If the select will only EVER return one row, try this:
Declare 
 b BLOB;
 c clob;
 n number;


 begin 

    SELECT PZPVSTREAM INTO b
      FROM PC
      WHERE PZINSKEY like 'ASSIGN-WORKLIST';


   dbms_lob.createtemporary(c,true);
   n:=1;
   while (n+32767<=length(b)) loop
     dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
     n:=n+32767;
   end loop;
   dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
   dbms_output.put_line(c);
   dbms_lob.createtemporary(c);

 end; 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
angel7170Author Commented:
Thank you.  Small change to line 22: number of arguments for createtemporary, I added "true"

But still I get an error. attached is the error I received



Declare
 b BLOB;
 c clob;
 n number;


 begin

       SELECT PZPVSTREAM INTO b
      FROM PC
      WHERE PZINSKEY like 'ASSIGN-WORKLIST';


   dbms_lob.createtemporary(c,true);
   n:=1;
   while (n+32767<=length(b)) loop
     dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
     n:=n+32767;
   end loop;
   dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
   dbms_output.put_line(c);
   dbms_lob.createtemporary(c, true);

 end;
error.PNG
slightwv (䄆 Netminder) Commented:
I'm not able to reproduce that error.

Here is the complete test case that I was using:
drop table tab1 purge;
create table tab1(col1 blob);

--create a dummy BLOB
declare
                junk blob;
begin
                dbms_lob.createtemporary(junk,true);
                for i in 1..1000 loop
                                dbms_lob.writeappend(junk,dbms_lob.getlength(utl_raw.cast_to_raw('Hello')),utl_raw.cast_to_raw('Hello'));
                end loop;

                insert into tab1 values(junk);
                commit;
                dbms_lob.freetemporary(junk);
end;
/


declare 
 b BLOB;
c clob;
n number;

begin 
    SELECT col1 INTO b
      FROM tab1;

   dbms_lob.createtemporary(c,true);
   n:=1;
   while (n+32767<=length(b)) loop
     dbms_lob.writeappend(c,32767,utl_raw.cast_to_varchar2(dbms_lob.substr(b,32767,n)));
     n:=n+32767;
   end loop;
   dbms_lob.writeappend(c,length(b)-n+1,utl_raw.cast_to_varchar2(dbms_lob.substr(b,length(b)-n+1,n)));
   dbms_output.put_line(c);
   dbms_lob.freetemporary(c);
end;
/

Open in new window

angel7170Author Commented:
Could this be a problem with what type of data is stored in that BLOB column? May be it is an image or file.

I am attaching what I see in the blob structure and the viewer. Not sure what it I see in Hex viewer.
blob-structure.PNG
blob-viewer.PNG
slightwv (䄆 Netminder) Commented:
>>Could this be a problem with what type of data is stored in that BLOB column?

I suppose it could be.  If it isn't text, what are you hoping to accomplish with converting it to a CLOB?
angel7170Author Commented:
True. How can I find if it does have only the text or not? I could not tell from looking into the structure. Please assist. Thank you
slightwv (䄆 Netminder) Commented:
I don't know of a way.  That is a problem with BLOBs.  They can store pretty much anything.

You sort of have to know what they are.  Some products add little 'hints' in the file headers that sort of point to what the file is but that really isn't a requirement.

If you have a database with a BLOB, some application put data into that column.  The application should also provide the necessary metadata about what it is storing.  If it doesn't, that is the fault of the application developers and/or requirements folks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.