Passing BLOB/CLOB as parameter to PL/SQL function

Question

I have this procedure i my package:

PROCEDURE pr_export_blob(
    p_name              IN      VARCHAR2,
    p_blob              IN      BLOB,
    p_part_size         IN      NUMBER);

I would like for parameter p_blob to be either BLOB or CLOB.

When I call this procedure with BLOB parameter, everything is fine. When I call it with CLOB parameter, I get compilation error:

PLS-00306: wrong number or types of arguments in call to 'pr_export_blob'

Is there a way to write a procedure, that can take either of those types as parameter? Some kind of a superclass maybe?

Answer 1

Stupid question first, are you actually changing the procedure in the package to accept a CLOB? A CLOB is not interchangable with a BLOB.

It is possible to convert a CLOB to BLOB:

create or replace procedure CLOB2BLOB (p_clob in out nocopy clob, p_blob in out nocopy blob) is
-- transforming CLOB â BLOB
l_off number default 1;
l_amt number default 4096;
l_offWrite number default 1;
l_amtWrite number;
l_str varchar2(4096 char);
begin
  begin
    loop
      dbms_lob.read ( p_clob, l_amt, l_off, l_str );

      l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
      dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
      utl_raw.cast_to_raw( l_str ) );

      l_offWrite := l_offWrite + l_amtWrite;

      l_off := l_off + l_amt;
      l_amt := 4096;
    end loop;
    exception
      when no_data_found then
        NULL;
 end;
end;

(Example by Victor on OTN forums).

Answer 2

Why don't you just overload the procedure to have a CLOB implementation as well

PROCEDURE pr_export_lob(
    p_name              IN      VARCHAR2,
    p_blob              IN      BLOB,
    p_part_size         IN      NUMBER);

PROCEDURE pr_export_lob(
    p_name              IN      VARCHAR2,
    p_clob              IN      CLOB,
    p_part_size         IN      NUMBER);

You'll then need to work out the logic of what to do with in each procedure. As Colin says, a CLOB is not a BLOB - so I'm not sure what you plan to do with this

Answer 3

create or replace procedure CLOB2BLOB (p_clob in out nocopy clob, p_blob in out nocopy blob) is -- transforming CLOB â BLOB l_off number default 1; l_amt number default 4096; l_offWrite number default 1; l_amtWrite number; l_str varchar2(4096 char); begin begin loop dbms_lob.read ( p_clob, l_amt, l_off, l_str );

  l_amtWrite := utl_raw.length ( utl_raw.cast_to_raw( l_str) );
  dbms_lob.write( p_blob, l_amtWrite, l_offWrite,
  utl_raw.cast_to_raw( l_str ) );

  l_offWrite := l_offWrite + l_amtWrite;

  l_off := l_off + l_amt;
  l_amt := 4096;
end loop;
exception
  when no_data_found then
    NULL;

end; end;