Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using database link via dbms_scheduler

dhalekFeb 24 2012 — edited Feb 24 2012
Hello out there,
I have a problem calling a stored procedure via dbms_scheduler that pulls some rows over a public database link.
The setup is the following:
create public database link mediabase using 'mediabase';

create or replace procedure hole_kurse as
mdatum date;
begin
   select max(datum) into mdatum
   from dt_wechselkurs;

   insert into dt_wechselkurs l
      (waehrung,
       datum,
       wechselkurs)
   (select
       r.waehrung,
       r.datum,
       r.wechselkurs
    from
       dt_wechselkurs@mediabase r
    where
       datum>mdatum);

    commit;
end hole_kurse;
/

begin
   dbms_scheduler.create_job(job_name          =>  'wechselkurse',
                             job_type          =>  'STORED_PROCEDURE',
                             job_action        =>  'hole_kurse',
                             start_date        =>  sysdate,
                             repeat_interval   =>  'FREQ=DAILY; BYHOUR=7; BYMINUTE=0; BYSECOND=0');
   dbms_scheduler.enable(name => 'wechselkurse');

   commit;
end;
/
I can access the database link in SQL and I can call the procedure hole_kurse from SQL without any errors. But the job fails writing "ORA-01017: invalid username/password; logon denied" into alert.log. I also tried using dbms_job which used to work with Oracle 10g but now fails with the same error.
My Oracle version is 11.2.0.2 64bit on Window Server 2008R2.

So what do I have to change that my job will run?

Many thanks in advance,
dhalek
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 23 2012
Added on Feb 24 2012
3 comments
2,011 views