Extract BLOB with length greater than 4000 in Oracle SQL

Question

I am trying to extract a BLOB variable using the below Query.

select utl_raw.cast_to_varchar2(BLOB_VAR) from Dual

However I am getting an error.

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4060, maximum: 2000)

Can you please let if it is possible extract the BLOB value greater than 4000 chars as the limit of varchar2 is 4000.

I tried to use concat option

select concat(concat(utl_raw.cast_to_varchar2(dbms_lob.substr(BYTES_,2000,1)),utl_raw.cast_to_varchar2(dbms_lob.substr(BYTES_,2000,2001))),utl_raw.cast_to_varchar2(dbms_lob.substr(BYTES_,2000,4001)))from ACT

But I get this error

01489. 00000 -  result of string concatenation is too long```

Is there any way to get a longer string value?

check the question and the answer here https://stackoverflow.com/questions/56129728/buffer-too-small-for-clob-to-char-or-blob-to-raw-conversion — hotfix, May 16 '19 at 09:37
hi @vijay kumar, you can find the solution for exactly the same error on this link: https://stackoverflow.com/questions/25518780/error-ora-22835-buffer-too-small-for-clob-to-char-or-blob-to-raw-conversion — F.Lazarescu, May 16 '19 at 10:07
Hi @F.Lazarescu I can't use a procedure. Is there any way to get without using a procedure. — vijay kumar, May 16 '19 at 10:26

F.Lazarescu · Answer 1 · 2019-05-17T06:56:16.607

0

You can try to create function, instead of procedure. Example (extract from link)

create or replace function F(B BLOB) 
return clob is 
  c clob;
  n number;
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;
/

Then use the function in whatever query do you need.

edited May 17 '19 at 06:56

answered May 16 '19 at 11:02

F.Lazarescu

1,385
2
16
31

Sorry I can't use function as well. – vijay kumar May 16 '19 at 12:11
1

if you copy and paste the code of some one else, you should also add link to the ressource. – hotfix May 16 '19 at 18:41
I always do. I was in a hurry. I will search the link and put it! – F.Lazarescu May 16 '19 at 19:37
@vijay kumar, are you sure that you can't use function? In your attempt to resolve, you wrote: `select utl_raw.cast_to_varchar2(BLOB_VAR) from Dual`. cast_to_varchar2 it's a function. If you create your own function, you will have `your_function_name`, instead of `cast_to_varchar2` – F.Lazarescu May 17 '19 at 07:00
@F.Lazarescu I can use only the built in functions and have no access to create a new one – vijay kumar May 17 '19 at 07:31
If possible could you help me above concat option? – vijay kumar May 17 '19 at 07:36
Hi, about the concat option you can find here some useful information: https://stackoverflow.com/questions/13795220/listagg-function-result-of-string-concatenation-is-too-long – F.Lazarescu May 17 '19 at 08:19

score 0 · Accepted Answer · answered May 17 '19 at 08:00

0

I found a simple way to get the output by using to_clob function

select to_clob(BYTES_) from ACT

answered May 17 '19 at 08:00

vijay kumar

203
1
6
16

Extract BLOB with length greater than 4000 in Oracle SQL

2 Answers2