Re: PL/SQL Procedure

It would help if you could tell us what the error message is. If you run it as a procedure, you can then type "show error" and it gives a lot more info.

You're missing a ";" at the end of your insert.

Note: you can do an implied open by:

• No open command for vShip in c_SHIP loop insert ... values ( vShip.shipping_method_id, ....); end loop;

Len

>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 Sat Oct 21 2000 - 09:52:48 CDT