Hello,
I have a table T1 with a column (BLOB), I need ( your help) to update Blob string with replacing a string (in Blob) with another string;
The update Failed : ORA-00933: SQL command not properly ended
CREATE table T1
(
ID NUMBER(12) NOT NULL,
DELIVERY_CONTENT BLOB,
)
Update T1 SET DELIVERY_CONTENT =LOB_UTL_PCKG.BLOBREPLACE(DELIVERY_CONTENT, 'old_string', 'new_string')
Where ID in (…)
ORA-00933: SQL command not properly ended
I use a function LOB_UTL_PCKG.BLOBREPLACE from this package
CREATE OR REPLACE PACKAGE LOB_UTL_PCKG
IS
FUNCTION BLOBREPLACE (p_blob BLOB, p_what VARCHAR2, p_with_what VARCHAR2) RETURN BLOB;
FUNCTION BLOB2CLOB (p_blob BLOB) RETURN CLOB;
FUNCTION CLOB2BLOB (p_clob CLOB) RETURN BLOB;
END;
/
CREATE OR REPLACE PACKAGE BODY LOB_UTL_PCKG
IS
FUNCTION BLOBREPLACE (p_blob BLOB, p_what VARCHAR2, p_with_what VARCHAR2) RETURN BLOB
IS
BEGIN
RETURN CLOB2BLOB( REPLACE(BLOB2CLOB(p_blob), p_what, p_with_what) );
END BLOBREPLACE;
------------------------------ BLOB2CLOB -------------------------
FUNCTION BLOB2CLOB (p_blob in BLOB) RETURN CLOB
IS
v_clob CLOB;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
warning INTEGER;
BEGIN
DBMS_LOB.CREATETEMPORARY ( v_clob, TRUE );
DBMS_LOB.CONVERTTOCLOB(
v_clob,
p_blob,
DBMS_LOB.LOBMAXSIZE,
dest_offset,
src_offset,
DBMS_LOB.DEFAULT_CSID,
lang_context,
warning
);
RETURN v_clob;
END BLOB2CLOB;
------------------------------ CLOB2BLOB -------------------------
FUNCTION CLOB2BLOB (p_clob CLOB) RETURN BLOB
AS
l_blob BLOB;
l_dest_offset INTEGER := 1;
l_source_offset INTEGER := 1;
l_warning INTEGER;
lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
BEGIN
DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
DBMS_LOB.CONVERTTOBLOB(
l_blob,
p_clob,
DBMS_LOB.LOBMAXSIZE,
l_dest_offset,
l_source_offset,
DBMS_LOB.DEFAULT_CSID,
lang_context,
l_warning
);
RETURN l_blob;
END CLOB2BLOB;
END;
/
Regards
Djam