Hi I have a big problem.Since a few weeks I am trying make a trigger which will be coping data from one table to other. The point is that this table contain column with blob data.I tried in many ways, but triggers don't support lob operation and I haven't get success.
My last idea is present below.It doesn't work becouse when I select blob source then its size is 0. I check, the select is right.When I select as blob_source blob inserted in previously transaction it works. Help me. I haven't idea what is wrong. Thank You. Sorry by my english, but I am desperate.
CREATE TABLE blob_tab (
id NUMBER,
blobek BLOB);
CREATE TABLE blob_tab_aud (
id NUMBER,
blobek BLOB);
CREATE OR REPLACE TRIGGER BLOB_TAB_TRG
BEFORE INSERT OR DELETE OR UPDATE ON BLOB_TAB
REFERENCING OLD AS OLDREKORD NEW AS NEWREKORD FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO BLOB_TAB_AUD VALUES(
:newRekoRd.ID,
EMPTY_BLOB(),
BLOB_TAB_SEQ.NEXTVAL, NULL, 'I', USER, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO BLOB_TAB_AUD VALUES(
:oldRekord.ID,
EMPTY_BLOB(),
BLOB_TAB_SEQ.NEXTVAL, NULL, 'U', USER, SYSDATE);
INSERT INTO BLOB_TAB_AUD VALUES(
:newRekord.ID,
EMPTY_BLOB(),
BLOB_TAB_SEQ.NEXTVAL,BLOB_TAB_SEQ.CURRVAL - 1, 'U', USER, SYSDATE);
ELSIF DELETING THEN
INSERT INTO BLOB_TAB_AUD VALUES(
:oldRekord.ID,
EMPTY_BLOB(),
BLOB_TAB_SEQ.NEXTVAL, NULL, 'D', USER, SYSDATE);
END IF;
END;
/
CREATE OR REPLACE PACKAGE audit_pkg AS
TYPE rowid_array IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
new_rows rowid_array;
empty rowid_array;
new_rows_aud rowid_array;
END audit_pkg;
/
CREATE OR REPLACE TRIGGER blob_tab_bi BEFORE INSERT OR UPDATE ON blob_tab
BEGIN
audit_pkg.new_rows := audit_pkg.empty;
END;
/
CREATE OR REPLACE TRIGGER blob_tab_aud_bi BEFORE INSERT OR UPDATE ON blob_tab
BEGIN
audit_pkg.new_rows_aud := audit_pkg.empty;
END;
/
CREATE OR REPLACE TRIGGER blob_tab_aud_ai AFTER INSERT ON blob_tab_aud FOR EACH ROW
BEGIN
audit_pkg.new_rows_aud (audit_pkg.new_rows_aud.count + 1) := :new.rowid;
END;
CREATE OR REPLACE TRIGGER BLOB_TAB_AFTER AFTER INSERT OR UPDATE ON BLOB_TAB FOR EACH ROW
BEGIN
audit_pkg.new_rows (audit_pkg.new_rows.count + 1) := :new.rowid;
END;
CREATE OR REPLACE TRIGGER blob_tab_aud_after AFTER INSERT ON blob_tab_aud FOR EACH ROW
BEGIN
audit_pkg.new_rows_aud (audit_pkg.new_rows_aud.count + 1) := :new.rowid;
END;
CREATE OR REPLACE TRIGGER blob_tab_ai
AFTER INSERT OR UPDATE ON BLOB_TAB
DECLARE
CURSOR c_cur(in_id blob_tab.id%TYPE) IS SELECT id FROM blob_tab WHERE id = in_id;
CURSOR c_cur1 IS SELECT id, blobek from blob_tab;
uv_id blob_tab.id %TYPE;
uv_id_aud blob_tab.id %TYPE;
uv_blob blob_tab.blobek %TYPE;
uv_blob_dest blob_tab.blobek %TYPE;
lobd BLOB;
lobs BLOB;
dest_offset INTEGER := 1;
src_offset INTEGER := 1;
amt INTEGER := 30000;
file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN('c:\temp','LOADER.LOG','w');
DBMS_OUTPUT.PUT_LINE('state_pkg.new_rows.COUNT : ' || audit_pkg.new_rows.COUNT);
DBMS_OUTPUT.PUT_LINE('state_pkg.new_rows_aud.COUNT : ' || audit_pkg.new_rows_aud.COUNT);
UTL_FILE.PUTF(file_handle,CHR(10) || 'state_pkg.new_rows.COUNT : ' || audit_pkg.new_rows.COUNT);
UTL_FILE.PUTF(file_handle,CHR(10) || 'state_pkg.new_rows_aud.COUNT : ' || audit_pkg.new_rows_aud.COUNT);
FOR i IN 1 .. audit_pkg.new_rows.COUNT
LOOP
SELECT id INTO uv_id FROM blob_tab WHERE rowid = audit_pkg.new_rows(i);
SELECT id_aud INTO uv_id_aud FROM blob_tab_aud WHERE rowid = audit_pkg.new_rows_aud(i);
SELECT BLOBEK INTO lobs FROM BLOB_tab WHERE ID = uv_id;
amt := DBMS_LOB.GETLENGTH(lobs);
DBMS_OUTPUT.PUT_LINE('amt = ' || amt);
IF amt > 0 THEN
SELECT BLOBEK INTO lobd FROM BLOB_tab_aud WHERE id_aud = uv_id_aud FOR UPDATE;
DBMS_LOB.COPY(lobd, lobs, amt, dest_offset, src_offset);
update blob_tab_aud set id_aud_2 = amt where id_aud = id_aud;
END IF;
END LOOP;
open c_cur1;
loop
fetch c_cur1 into uv_id, uv_blob;
exit when c_cur1 %notfound;
UTL_FILE.PUTF(file_handle,CHR(10) ||'uv_id = ' || uv_id);
UTL_FILE.PUTF(file_handle,CHR(10) || 'uv_blob_lenght = ' || dbms_lob.getlength(uv_blob));
end loop;
CLOSE c_cur1;
utl_file.fclose(file_handle);
END;
/
|
