oracle delete data from remote SQL Server table
Using the procedure below, we are trying to transfer data from SQL Server to oracle, but we get the error :
Tarih : 20/01/2011, Hata yeri :
pr_get_customer, Hata Açıklaması: -28500 => v_trace : 2,v_transfer_id :1ORA-28500: ORACLE sisteminden Oracle olmayan sisteme bağlantı şu mesajı verdi: [Oracle][ODBC SQL Server Driver][SQL Server]The multi-part identifier "PUBLIC.TMP_CUSTOMER.TRANSFER_ID" could not be bound. {42000,NativeErr = 4104} ORA-02063: önceki 2 lines, kaynağı DBLINK_NAV2
v trace is our variable to locate the region of the error, and transfer_id gets value .
The procedure is listed here :
PROCEDURE pr_get_customer
IS
CURSOR cur_tmp_customer
IS
SELECT "TRANSFER_ID" AS transfer_id, "INSERT_DATE" AS insert_date,
"OP_TYPE" AS op_type, "STATUS" AS status, "No_" AS NO,
"Name" AS NAME, "Name 2" AS name_2, "Address" AS address,
"Address 2" AS address_2, "City" AS city,
"Phone No_" AS phone_no, "Chain Name" AS chain_name,
"Customer Price Group" AS customer_price_group,
"Blocked" AS blocked,
"Bill-to Customer No_" AS bill_to_customer_no,
"Fax No_" AS fax_no, "SUBE" AS sube, "Alt Grup" AS alt_grup,
"Location Code" AS location_code
FROM nav_tmp_customer
WHERE "STATUS" = 0
AND ("TRANSFER_ID" > (SELECT NVL (MAX (transfer_id), 0)
FROM t_tmp_customer));
r_tmp_customer cur_tmp_customer%ROWTYPE;
TYPE tmp_customer_tbl_typ IS TABLE OF cur_tmp_customer%ROWTYPE;
tmp_customer_tbl tmp_customer_tbl_typ := tmp_customer_tbl_typ
();
v_trace INTEGER := 0;
v_transfer_id NUMBER (28, 0) := 0;
BEGIN
OPEN cur_tmp_customer;
FETCH cur_tmp_customer
BULK COLLECT INTO tmp_customer_tbl;
CLOSE 开发者_开发问答cur_tmp_customer;
FOR x IN 1 .. tmp_customer_tbl.COUNT ()
LOOP
BEGIN
v_trace := 1;
INSERT INTO esiparis.t_tmp_customer
(transfer_id,
insert_date,
op_type,
status,
NO, NAME,
name2,
address,
address2,
city,
phone_no,
chain_name,
customer_price_group,
blocked,
bill_to_customer_no,
location_code,
fax_no,
sube,
alt_grup
)
VALUES (tmp_customer_tbl (x).transfer_id,
tmp_customer_tbl (x).insert_date,
tmp_customer_tbl (x).op_type,
tmp_customer_tbl (x).status,
tmp_customer_tbl (x).NO, tmp_customer_tbl (x).NAME,
tmp_customer_tbl (x).name_2,
tmp_customer_tbl (x).address,
tmp_customer_tbl (x).address_2,
tmp_customer_tbl (x).city,
tmp_customer_tbl (x).phone_no,
tmp_customer_tbl (x).chain_name,
tmp_customer_tbl (x).customer_price_group,
tmp_customer_tbl (x).blocked,
tmp_customer_tbl (x).bill_to_customer_no,
tmp_customer_tbl (x).location_code,
tmp_customer_tbl (x).fax_no,
tmp_customer_tbl (x).sube,
tmp_customer_tbl (x).alt_grup
);
COMMIT;
v_trace := 2;
v_transfer_id := tmp_customer_tbl (x).transfer_id;
DELETE FROM nav_tmp_customer
WHERE "TRANSFER_ID" = v_transfer_id;
COMMIT;
v_trace := 3;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
pck_helper.pr_log_error (SQLCODE,
'v_trace : '
|| v_trace
|| ',v_transfer_id :'
|| v_transfer_id
|| SQLERRM,
'pr_get_customer'
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
pck_helper.pr_log_error (SQLCODE,
'v_trace : ' || v_trace || ',' || SQLERRM,
'pr_get_customer'
);
END pr_**strong text****strong text****strong text**get_customer;
Any ideas are appreciated,
UPDATE : Working procedure is as below
PROCEDURE pr_get_item
IS
CURSOR cur_tmp_item
IS
SELECT "TRANSFER_ID" AS transfer_id, "INSERT_DATE" AS insert_date,
"OP_TYPE" AS op_type, "STATUS" AS status, "No_" AS NO,
"Description" AS description,
"Base Unit of Measure" AS base_unit_of_measure,
"Inventory Posting Group" AS inventory_posting_group,
"Net Weight" AS net_weight,
"Genel Ürün Sahibi" AS genel_urun_sahibi,
"Tab Code" AS tab_code,
"Tab Unit of Measure" AS tab_unit_of_measure,
"TAB Qty_ per Unit of Measure"
AS tab_qty_per_unit_of_measure,
"Blocked 2" AS blocked_2
FROM nav_tmp_item
WHERE "STATUS" = 0
AND ("TRANSFER_ID" > (SELECT NVL (MAX (transfer_id), 0)
FROM t_tmp_item));
r_tmp_item cur_tmp_item%ROWTYPE;
TYPE tmp_item_tbl_typ IS TABLE OF cur_tmp_item%ROWTYPE;
tmp_item_tbl tmp_item_tbl_typ := tmp_item_tbl_typ ();
v_trace INTEGER := 0;
v_count NUMBER (28, 0) := 1;
BEGIN
OPEN cur_tmp_item;
FETCH cur_tmp_item
BULK COLLECT INTO tmp_item_tbl;
CLOSE cur_tmp_item;
FOR x IN 1 .. tmp_item_tbl.COUNT ()
LOOP
BEGIN
v_trace := 1;
INSERT INTO esiparis.t_tmp_item
(transfer_id,
insert_date,
op_type, status,
NO, description,
base_unit_of_measure,
inventory_posting_group,
net_weight,
genel_urun_sahibi,
tab_code,
tab_unit_of_measure,
tab_qty_per_unit_of_measure,
blocked_2
)
VALUES (tmp_item_tbl (x).transfer_id,
tmp_item_tbl (x).insert_date,
tmp_item_tbl (x).op_type, tmp_item_tbl (x).status,
tmp_item_tbl (x).NO, tmp_item_tbl (x).description,
tmp_item_tbl (x).base_unit_of_measure,
tmp_item_tbl (x).inventory_posting_group,
tmp_item_tbl (x).net_weight,
tmp_item_tbl (x).genel_urun_sahibi,
tmp_item_tbl (x).tab_code,
tmp_item_tbl (x).tab_unit_of_measure,
tmp_item_tbl (x).tab_qty_per_unit_of_measure,
tmp_item_tbl (x).blocked_2
);
COMMIT;
v_trace := 2;
DELETE FROM nav_tmp_item
WHERE "TRANSFER_ID" = tmp_item_tbl (x).transfer_id;
COMMIT;
v_trace := 3;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
pck_helper.pr_log_error (SQLCODE,
'v_trace : ' || v_trace || ','
|| SQLERRM,
'pr_get_item'
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
pck_helper.pr_log_error (SQLCODE,
'v_trace : ' || v_trace || ',' || SQLERRM,
'pr_get_item'
);
END pr_get_item;
I think the better question is: What's the best way to move data from SQL Server to Oracle which deletes from MSSS every successfully delivered row?
You do these in a loop to makes sure that when you add to one, you delete from the other.
If some unknown random thing happens to error on the insert, you'll avoid removing the source record.
That's the gist, right?
There are much better ways to handle this kind of data movement then committing after each insert/delete distributed transaction loop.
First off a commit in the middle of a loop is just bad. In Oracle it's a good way to cause other sessions to get ORA-01555 errors. So I would avoid that if possible.
Second you should absolutely know what are the possible causes for reasonable errors on insert. You violate a column length, a FK, a UK... there's a finite list of what could break on insert. Big hairy bad breaks like Unable to extend tablespace SHOULD cause the system to grind to a halt. No need to trap for that. But application issues like I mentioned are easy to manage for.
If I were writing this, I would bulk insert all of the rows using Oracle's LOG ERRORS INTO to avoid a full ROLLBACK when only one/few row(s) fail.
Now you have a table which contains every failed row and the reason it failed. Now you can on the SQL Server side, delete all the rows NOT IN the list of failed ID's.
You've accomplished the same thing but with Set based operations instead of SLOW-BY-SLOW, ooops, I mean row by row.
精彩评论