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.