Passing BLOB/CLOB as parameter to PL/SQL function

database, oracle, plsql, sql

Solution

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

Problem

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?

Original source