PL/SQL Procedure

From: <shoeb_baig_at_my-deja.com>
Date: Fri, 20 Oct 2000 20:38:30 GMT
Message-ID: <8sqag5$pkb$1@nnrp1.deja.com>

Hi,
I am in the porcess of Writing a Procedure to populate a local database table selecting from the remote database columns from multiple tables in multiple schemas. I have to run this procedure as a nightly job. I have a dblink setup on my local database pointing to the remote database and its working fine.
any help will be appriciated.
The following is the code that I am using to upload the shipping table and its not working. stg is my dblink.

CREATE OR REPLACE PROCEDURE "SHIP" IS  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
WHERE ( PERSONAL_INFO.PERSONAL_INFO_ID = CUSTOMER.PERSONAL_INFO_ID);
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;
/

Thanks for your help in advance.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 20 2000 - 15:38:30 CDT