| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Longs and snapshots
Here's how I've done it. Create a second table comprised of the columns
which make up the primary key of the table with the long fields: a field to
track the type DML operation on the long field (INSERT, UPDATE, or DELETE);
a field which tracks whether a row from the long table has been copied to
the remote table; a field which tracks when a row is processed.
Your new table should look something like
DESCRIBE PS_SL_COMMENTS_TBL_STATUS
Name Null? Type ------------------------------- -------- ---- OPRID NOT NULL VARCHAR2(8) COMMENT_ID DATE RANDOM_CMMT_NBR NOT NULL NUMBER OPERATION NOT NULL VARCHAR2(6) PROCESS_FLAG NOT NULL VARCHAR2(1) PROCESS_DATE NOT NULL DATE
N.B. The above is used to snapshot a Peoplesoft table. Peoplesoft databases have no primary key constraints. The first three columns of the table are based on the unique index for the Peoplesoft table.
A trigger is placed on the table with the long field to populate this new table
create or replace trigger popcommtblstat_trig
after insert or delete or update
on ps_comments_tbl
for each row
Begin
if inserting then
insert into oracle.ps_sl_comments_tbl_status values
(:new.oprid, :new.comment_id, :new.random_cmmt_nbr,
'INSERT', 'N', SYSDATE);
elsif updating then
update oracle.ps_sl_comments_tbl_status set
OPERATION = 'UPDATE',
PROCESS_FLAG = 'N', PROCESS_DATE = SYSDATE
where oprid = :old.oprid and
comment_id = :old.comment_id and
random_cmmt_nbr = :old.random_cmmt_nbr;
else
update oracle.ps_sl_comments_tbl_status set
OPERATION = 'DELETE',
PROCESS_FLAG = 'N', PROCESS_DATE = SYSDATE
where oprid = :old.oprid and
comment_id = :old.comment_id and
random_cmmt_nbr = :old.random_cmmt_nbr;
end if;
On the remote database you need to write a procedure to do the copying. The following was pulled form a package
PROCEDURE load_comments_tbl
IS
cursor popcommtbl_cur is
select a.oprid, a.comment_id, a.random_cmmt_nbr, a.shared_flg,
b.operation, sysdate, comments_2000
from ps_comments_tbl_at_db_link A,
PS_SL_comments_tbl_STATUS_at_db_link B
WHERE A.OPRID(+) = B.OPRID
AND A.COMMENT_ID(+) = B.COMMENT_ID
AND A.RANDOM_CMMT_NBR(+) = B.RANDOM_CMMT_NBR
AND B.PROCESS_FLAG = 'N';
POPRID VARCHAR2(8);
pcomment_id date;
prandom_cmmt_nbr number;
pshared_flg varchar2(1);
poperation varchar2(6);
prundate date;
pcomments_2000 varchar2(32767);
Begin
open popcommtbl_cur;
loop
fetch popcommtbl_cur into poprid,
pcomment_id, prandom_cmmt_nbr,pshared_flg,
poperation,prundate, pcomments_2000;
EXIT when popcommtbl_cur%notfound;
if (poperation = 'DELETE') THEN
DELETE FROM PS_COMMENTS_TBL
where oprid = poprid
and comment_id = pcomment_id
and RANDOM_CMMT_NBR = prandom_cmmt_nbr;
DELETE FROM PS_SL_COMMENTS_TBL_STATUS_at_db_link
WHERE
OPRID = POPRID AND
comment_id = pcomment_id
and random_cmmt_nbr = pRANDOM_CMMT_NBR
AND PROCESS_DATE < PRUNDATE;
ELSE
DELETE FROM PS_COMMENTS_TBL
where oprid = poprid
and comment_id = pcomment_id
and random_cmmt_nbr = prandom_cmmt_nbr;
INSERT INTO PS_COMMENTS_TBL
VALUES (POPRID, PCOMMENT_ID, PRANDOM_CMMT_NBR,
PSHARED_FLG, PCOMMENTS_2000);
UPDATE PS_SL_COMMENTS_TBL_STATUS_at_db_link
SET PROCESS_FLAG='Y'
WHERE
OPRID = POPRID AND
COMMENT_ID = PCOMMENT_ID
AND RANDOM_CMMT_NBR = PRANDOM_CMMT_NBR
AND PROCESS_DATE < PRUNDATE;
end if;
end loop;
commit;
close popcommtbl_cur;
EXCEPTION
WHEN VALUE_ERROR
THEN INSERT INTO LONG_COLUMN_ERRORS
VALUES ('PS_COMMENTS_TBL', SYSDATE);
end load_comments_tbl;
A few things of note. The error handling needs to be made more robust; you
also need a table to track the errors. This
only works for tables in which the lings are 32767 bytes or less. It is
possible to use this method with longer longs; just
write the code to chunk the long. You'll need to schedule the procedure via
dbms_jobs. You'll need to
snapshot the current rows; write an insert statement to populate the
snapshot log type table from the table with the long fields setting
operation to 'INSERT', process_flag to 'N' and process_date to SYSDATE, and
then execute the procedure.
The snapshot log type table must be remotely updateable.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
From: Browett, Darren [mailto:dbrowett_at_city.coquitlam.bc.ca]
Sent: Wednesday, December 13, 2000 8:16 AM
To: Multiple recipients of list ORACLE-L
Subject: Longs and snapshots
Hi,
I need to be able to snapshot a table that contains a long. I realize that you cannot do that.
I have tried one workaround, create a trigger/procedure on table A (the one
with the long) that copies the data
to table B, then snapshot table B to the warehouse.
It almost works, but I get a morphing error. Can somebody direct me, or
provide me
with examples and/or experiences with this situation. Maybe there is a
different way to do it.
Thanks
Darren
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: dbrowett_at_city.coquitlam.bc.ca Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Wed Dec 13 2000 - 13:24:39 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
 |
 |