Databases
StackOverflow2013
Postsdbo
POoracle delete data from remote SQL Server table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POoracle delete data from remote SQL Server table
    primarykey
    data
    text
    <p>Using the procedure below, we are trying to transfer data from SQL Server to oracle, but we get the error :</p> <blockquote> <p>Tarih : 20/01/2011, Hata yeri :<br> pr_get_customer, Hata Açıklaması:<br> -28500 => v_trace : 2,v_transfer_id :1ORA-28500: ORACLE sisteminden Oracle olmayan sisteme bağlantı şu mesajı<br> verdi: [Oracle][ODBC SQL Server<br> Driver][SQL Server]The multi-part identifier<br> "PUBLIC.TMP_CUSTOMER.TRANSFER_ID" could not be bound. {42000,NativeErr = 4104} ORA-02063: önceki 2 lines, kaynağı DBLINK_NAV2</p> </blockquote> <p>v trace is our variable to locate the region of the error, and transfer_id gets value .</p> <p>The procedure is listed here : </p> <pre><code>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" &gt; (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; </code></pre> <p>Any ideas are appreciated,</p> <hr> <p>UPDATE : Working procedure is as below </p> <pre><code>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" &gt; (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; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USkayhan yüksel
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat's the point of a Rollback in the error handler when you're commiting TWICE in the loop?
      singulars
      1. POoracle delete data from remote SQL Server table
      1. USStephanie Page
    2. COStephanie, logical point. We tried to add by iteration to the procedure. This procedure is derived from a procedure alike, and at that procedure - same bug remains - we tested to get data first and delete remote db later.
      singulars
      1. POoracle delete data from remote SQL Server table
      1. USkayhan yüksel
    3. COAnother point is that it gave an error that I didn't take the exact note of it but we could recover that only by dropping table. If that was a production db, it would be unacceptible :( .. The error occured while tracing the code with debug at remote delete.
      singulars
      1. POoracle delete data from remote SQL Server table
      1. USkayhan yüksel
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload