Hi There,
We're experimenting with Oracle Streams (Real-Time Downstream Capture) and we have a question regarding BLOB support.
Both databases (source and target/downstream) are using Oracle 12.1
SYS@kec1devdb> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
According to Oracle, BLOB is a supported data type for both the Capture and Apply processes:
https://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_capture.htm#i1006263
https://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_apply.htm#i1006373
We created a simple table to test BLOB DML and propagation from the source to the downstream database and we did a simple insert:
CREATE TABLE BLOB_TEST
("B_ID" NUMBER,
"B_DATA" BLOB);
SYS@SRC_DB>
declare
v_b blob;
aaa raw(32767);
longLine varchar2(32767);
begin
longLine := LPAD('aaaa', 9000,'x');
aaa := UTL_RAW.CAST_TO_RAW(longLine);
insert into tstusr.blob_test values(5,empty_blob()) returning B_DATA into v_b;
dbms_lob.open(v_b,dbms_lob.lob_readwrite);
dbms_lob.writeappend(v_b,UTL_RAW.LENGTH (aaa) ,aaa);
dbms_lob.close(LOB_LOC=>v_b);
commit;
end;
/
SYS@SRC_DB>
PL/SQL procedure successfully completed.
SYS@SRC_DB> select * from tstusr.blob_test;
B_ID B_DATA
---------- ----------------------------------------
5 7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
as we can see, the table on the source database was updated properly. Now, when we check the results on the down stream side, we can see the key/id is there but not the BLOB data:
SYS@TRG_DB> r
1* select * from tstusr.blob_test
B_ID B_DATA
---------- ----------------------------------------
5
We tried out different values, and a string size of 3960 was the max limit that was actually propagating through successfully:
declare
v_b blob;
aaa raw(32767);
longLine varchar2(32767);
begin
longLine := LPAD('aaaa', 3960,'x');
aaa := UTL_RAW.CAST_TO_RAW(longLine);
insert into tstusr.blob_test values(6,empty_blob()) returning B_DATA into v_b;
dbms_lob.open(v_b,dbms_lob.lob_readwrite);
dbms_lob.writeappend(v_b,UTL_RAW.LENGTH (aaa) ,aaa);
dbms_lob.close(LOB_LOC=>v_b);
commit;
end;
/
SYS@SRC_DB>
PL/SQL procedure successfully completed.
SYS@SRC_DB> select * from tstusr.blob_test;
B_ID B_DATA
---------- ----------------------------------------
5 7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
6 7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
SYS@TRG_DB> select * from tstusr.blob_test
B_ID B_DATA
---------- ----------------------------------------
5
6 7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
7878787878787878787878787878787878787878
Looking at Oracle data types, "A BLOB without a specified length is defaulted to two gigabytes" - so I would imagine that we're not going over limit! Also, the table (and its columns) doesn't appear under dba_streams_unsupported:
select * from DBA_STREAMS_UNSUPPORTED where owner='TSTUSR';
OWNER TABLE_NAME REASON AUT
------------------------- ------------------------------ --------------------------------------- ---
TSTUSR EXPORT_EMPL_INFO external table NO
TSTUSR XTERN_EMPL_RPT external table NO
So, any idea what is going on here please?
Cheers