[suse-oracle] DB Job with remote Database

Hi @ all,

I have got the following procedure (see bellow).

Whenever I execute it by a SQL command like 

Begin

      Sp_get_leistung_stw(1);

End;

 

it works.

But whenever I try executing the statement written above by using a DB-Job I
only get an empty line.

Remote DB is Oracle 8, and the Host DB is an Oracle 10g.

 

What I am doing wrong?

 

PROCEDURE SP_GET_LEISTUNG_STW(WhatDate in number)

AS 

RecordOK           NUMBER := 1;

begin

------------------------------------------------

begin

    for RecCount in (

    select to_date(sysdate - WhatDate, 'dd.mm.yyyy') as Datum, a.ausbr_fl


    from Table_a@remote_DB c

        (SELECT to_date(sysdate - WhatDate, 'dd.mm.yyyy') as datum,

          sum(a.sbka_ausbr_fl) as ausbr_fl

          FROM table_D@Remote_DB a

          Where xyz) a

    where c.scag_ende) loop

        select count(*) into RecordOK from LocalTableName

        where Datum = RecCount.datum;

 

        if RecordOK = 0 then

            insert into LocalTableName values (

                        RecCount.Datum, RecCount.ausbr_fl);

            commit;

        else

            update LocalTableName

                SET SOll_Tonnage = RecCount.ausbr_fl         

        where TB_LEISTUNG_STW.datum = RecCount.datum;

            commit;

        end if;

     end loop;

EXCEPTION

    WHEN OTHERS THEN

             TB_SP_LOG_WRITE(p_vStatus=>'E',

                                     p_vName=>'SP_GET_LEISTUNG_STW',

                                    p_nErrCode=>SQLCODE,

                                    p_vErrMsg=>SQLERRM,

                                    p_vDate=>SYSDATE

                                    );

end;

 

end;

 

Kind Regards

_______________________________________________
suse-oracle mailing list
[email protected]
http://listx.novell.com/mailman/listinfo/suse-oracle