Re: PL/SQL Procedure

It's hard to help you when you don't say what's wrong.

Here's how I collect from remote table:

procedure get_data is
begin
  insert into my_local_table
 select relevant_fields from remote_table_name_at_datbase_link; -- this will fail if the remote link is broken  end;

variable job number;
begin
  dbms_job.submit(:job,'get_data;',sysdate,'truncate(sysdate) + 1'); end;

<shoeb_baig_at_my-deja.com> wrote in message news:8sqag5$pkb$1_at_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 - 16:48:00 CDT