PL/SQL Procedure

From: <shoeb_baig_at_my-deja.com>
Date: Fri, 20 Oct 2000 20:58:51 GMT
Message-ID: <8sqbm7$qkr$1@nnrp1.deja.com>

I am in the process of updating tables on the local database and I am selecting data from the remote database from different tables. I have a dblink setup on my local database. I am using this procedure but its not working. I need to submit a procedure as a nightly job for multiple tables at the local oracle database on windows NT machine. I need to get the updated information from the remote database every night so that the new info. appears on reports in the morning. The code below is what i am trying to use. Please help me in building this procedure. Any help will be appriciated.
Thanks.

CREATE OR REPLACE PROCEDURE "SHIP"
DECLARE

v_ShippingInfo  SHIPPING_INFO.SHIPPING_METHOD_ID%TYPE;
v_ShippingMethodName  SHIPPING_INFO.SHIPPING_METHOD_NAME%TYPE;
v_ShippingAmount      SHIPPING_INFO.SHIPPING_AMOUNT%TYPE;

CURSOR c_SHIP IS
SELECT SHIPPING_INFO.SHIPPING_METHOD_ID,

        SHIPPING_INFO.SHIPPING_METHOD_NAME,
        SHIPPING_INFO.SHIPPING_AMOUNT

FROM SXWEB01.SHIPPING_METHOD_at_STG, SXWEB01.SHIPPING_INFO_at_STG; BEGIN
  OPEN c_SHIP;
  LOOP
  FETCH c_SHIP INTO v_ShippingInfo, v_ShippingMethodName, v_ShippingAmount;
  EXIT WHEN C_SHIP%NOTFOUND;
  INSERT INTO SHIPPING (SHIPPING_METHOD_ID, SHIPPING_METHOD_NAME, SHIPPING_AMOUNT)
   VALUES (v_ShippingInfo, v_ShippingMethodName, v_ShippingAmount)  END LOOP;
 CLOSE c_SHIP;
 EXCEPTION

     WHEN NO_DATA_FOUND THEN
       Null;
     WHEN OTHERS THEN
       Null;

 COMMIT;
END SHIP; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 20 2000 - 15:58:51 CDT