Re: PL/SQL Procedure

Hi,

I think I missed the ";" in my post. I am sorry. but otherwise its is still not bringing data.

Thanks.

In article <8sqeh3$ekp$1_at_spiney.sierra.com>,   "Steve McDaniels" <steve.mcdaniels_at_sierra.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 22 2000 - 10:00:25 CDT