Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

converting CLOB to BLOB

418190Jul 30 2004 — edited Jul 3 2007
I am trying to convert clob into blob through following code.
/*****************************************/
CREATE OR REPLACE FUNCTION clob_to_blob (clob_in IN CLOB)
RETURN BLOB
AS
v_blob BLOB;
v_varchar RAW(32767);
v_start BINARY_INTEGER := 1;
v_buffer BINARY_INTEGER := 32767;
BEGIN

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(clob_in) / v_buffer)
LOOP

v_varchar := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start)) ;
DBMS_OUTPUT.PUT_LINE('DATA :' ||DBMS_LOB.SUBSTR(clob_in, v_buffer, v_start));
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR :'|| v_VARCHAR);
DBMS_OUTPUT.PUT_LINE(' V_VARCHAR LENGTH :'|| LENGTH(v_VARCHAR));
DBMS_LOB.WRITEAPPEND(v_blob, LENGTH(v_VARCHAR), v_varchar);
DBMS_OUTPUT.PUT_LINE('after append');
v_start := v_start + v_buffer;
END LOOP;

RETURN v_blob;

END clob_to_blob;

calling code:
/**************************************************/

declare
var clob;
begin
var:='3433534534de';
testblob(var);
end;

It gives me following error:
declare
*
ERROR at line 1:
ORA-21560: argument 2 is null, invalid, or out of range

i don't know whats the prob?
thanx
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2007
Added on Jul 30 2004
7 comments
3,327 views