This discussion is archived
9 Replies Latest reply: Dec 3, 2012 10:33 PM by 846231 RSS

DBMS scheduler 5

846231 Newbie
Currently Being Moderated
Hi All,

Oracle 11.2.0.1

I still can not get how dbs scheduler works. :(

I have this proc:
create or replace procedure check_dblinks
is
  cursor dblink_cur is select db_link from user_db_links;
  stmt varchar2(100);
begin
  for dblink_rec in dblink_cur
  loop
  begin
    stmt := 'select count(*) from tab@' || dblink_rec.db_link;
    dbms_output.put_line('Running:' || stmt || ' ...');
    execute immediate stmt;
    dbms_output.put_line('OK');
       exception 
           when others then
             dbms_output.put_line(stmt||' - NOT OK');
              commit;
       end;
  end loop;
end;
/ 
And I have this output:
SQL> set serverouput on;
SP2-0735: unknown SET option beginning "serveroupu..."
SQL> set serveroutput on;
SQL> exec check_dblinks;
Running:select count(*) from tab@ACCDB ...
OK
Running:select count(*) from tab@ADWEADB ...
OK
Running:select count(*) from tab@AURODB ...
OK
Running:select count(*) from tab@BGAISDV ...
OK
Running:select count(*) from tab@CAPGEMDV ...
select count(*) from tab@CAPGEMDV - NOT OK
Running:select count(*) from tab@COADV231 ...
OK
Running:select count(*) from tab@COADVST ...
select count(*) from tab@COADVST - NOT OK
Running:select count(*) from tab@CONEDDV ...
OK
Running:select count(*) from tab@DLCDV ...
OK
Running:select count(*) from tab@DLCMDMDV ...
OK
Running:select count(*) from tab@DLCSTG ...
OK
Running:select count(*) from tab@DTODV ...
select count(*) from tab@DTODV - NOT OK
Running:select count(*) from tab@DTOSTG ...
OK
Running:select count(*) from tab@EACDV ...
OK
Running:select count(*) from tab@EDFEDB ...
OK
Running:select count(*) from tab@EESTIDV ...
OK
Running:select count(*) from tab@ENERDV ...
select count(*) from tab@ENERDV - NOT OK
Running:select count(*) from tab@ENERGDB ...
OK
Running:select count(*) from tab@ENERGSTG ...
OK
Running:select count(*) from tab@ESKOMDV ...
OK
Running:select count(*) from tab@GSTONEDV ...
OK
Running:select count(*) from tab@HRSDDB ...
select count(*) from tab@HRSDDB - NOT OK
Running:select count(*) from tab@HWCV2DV ...
select count(*) from tab@HWCV2DV - NOT OK
Running:select count(*) from tab@HYDRODV ...
OK
Running:select count(*) from tab@HYDROSTG ...
OK
Running:select count(*) from tab@JEADB ...
OK
Running:select count(*) from tab@LADWPDB ...
OK
Running:select count(*) from tab@LADWPSTG ...
select count(*) from tab@LADWPSTG - NOT OK
Running:select count(*) from tab@LGCDB ...
OK
Running:select count(*) from tab@LGCSTG ...
OK
Running:select count(*) from tab@MDADEDV ...
OK
Running:select count(*) from tab@MDADESTG ...
select count(*) from tab@MDADESTG - NOT OK
Running:select count(*) from tab@MDCDB ...
OK
Running:select count(*) from tab@MDCPROD ...
OK
Running:select count(*) from tab@PACIFDB ...
OK
Running:select count(*) from tab@PACIFSTG ...
OK
Running:select count(*) from tab@PARDV ...
select count(*) from tab@PARDV - NOT OK
Running:select count(*) from tab@PGNIGDV ...
OK
Running:select count(*) from tab@PGNIGSTG ...
select count(*) from tab@PGNIGSTG - NOT OK
Running:select count(*) from tab@PNIXDV ...
OK
Running:select count(*) from tab@REDDB ...
OK
Running:select count(*) from tab@SNGPLDB ...
OK
Running:select count(*) from tab@SSEDB ...
OK
Running:select count(*) from tab@THCDB ...
select count(*) from tab@THCDB - NOT OK
Running:select count(*) from tab@TRUIBMD2 ...
OK
Running:select count(*) from tab@TRUM2CDB ...
OK
Running:select count(*) from tab@TURBDV2 ...
select count(*) from tab@TURBDV2 - NOT OK
Running:select count(*) from tab@WESTPDV ...
OK
Running:select count(*) from tab@YVWDV ...
select count(*) from tab@YVWDV - NOT OK
Running:select count(*) from tab@YVWDV231 ...
OK
Running:select count(*) from tab@YVWSTG ...
OK

PL/SQL procedure successfully completed.
My question is how is this displayed in the logs


Thanks a lot,
Kinz
  • 1. Re: DBMS scheduler 5
    Aman.... Oracle ACE
    Currently Being Moderated
    Not sure what's confusing? Your code is simply using all the db link names in the dictionary, doing a check and displaying a message "ok" for them.

    Aman....
  • 2. Re: DBMS scheduler 5
    rp0428 Guru
    Currently Being Moderated
    You've posted enough to know you need to provide your 4 digit Oracle version.
    >
    I still can not get how dbs scheduler works
    >
    What does any of what you posted have to do with DBMS_SCHEDULER?
    >
    My question is how is this displayed in the logs
    >
    What logs? Nothing of what you posted has anything to do with any logs.
  • 3. Re: DBMS scheduler 5
    846231 Newbie
    Currently Being Moderated
    I thank you all....


    What I mean is.....the serveroutput on is using interactive process while you are on the sqlplus window and it will display all the process results based on what you wanted to be display.
    But If I run the job as background batch in dmbs_schedule where can I see the serverputput shown in the list?
    or is there a default file, folder, table_logs, where all the batch activities are recorded? Similar to data_pump_dir where all the expdp logs are saved?


    Thanks
    Kinz
  • 4. Re: DBMS scheduler 5
    Aman.... Oracle ACE
    Currently Being Moderated
    KinsaKaUy? wrote:
    I thank you all....


    What I mean is.....the serveroutput on is using interactive process while you are on the sqlplus window. But If I run the job as background batch in dmbs_schedule where can I see the serverputput shown in the list?
    or is there a default file, folder, table_logs, where all the batch activities are recorded?
    No, there isn't any such thing. If you want to get an output like the one is coming in sqlplus, you may want to use , for example, dbms_file and write into it. This code has to be written by you. The only other thing that you can check from the log of the scheduler is that whether the job ran successfully or not, which should be sufficient if you just want to check the status of the job.

    HTH
    Aman....
  • 5. Re: DBMS scheduler 5
    846231 Newbie
    Currently Being Moderated
    Thanks aman :)


    Yes that is what I wanted. Where can I configure the dbms_file for scheduled jobs output in sqldeveloper. So I can capture that output displayed when using "set serveroutput on" at interactive mode? I am using sqldeveloper in scheduling jobs because it is very easy to use ;)

    I am beginning to love sqldeveloper very much!
  • 6. Re: DBMS scheduler 5
    sb92075 Guru
    Currently Being Moderated
    KinsaKaUy? wrote:
    Thanks aman :)


    Yes that is what I wanted. Where can I configure the dbms_file for scheduled jobs output in sqldeveloper. So I can capture that output displayed when using "set serveroutput on" at interactive mode? I am using sqldeveloper in scheduling jobs because it is very easy to use ;)

    I am beginning to love sqldeveloper very much!
    how do you automate sqldeveloper?
  • 7. Re: DBMS scheduler 5
    846231 Newbie
    Currently Being Moderated
    how do you automate sqldeveloper?
    fyi, sqldeveloper is just a tool to configure automation of batch jobs and other things :)
    It is very user friendly. Instead of manually code things like procedures, or job schedules, you can just click the buttons.
    See this good example:
    http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r30/DBMSScheduler/DBMSScheduler.htm

    hth ;)
  • 8. Re: DBMS scheduler 5
    Aman.... Oracle ACE
    Currently Being Moderated
    But if all what you need to know that did the job ran properly or not, it would be already logged in the views of the scheduler, for example, dba_scheduler_jobs, so why you want to do this extra coding in the first place? Second, sqldeveloper can't automate everything for you. This task, if you want to do it in your way would need you to type the code by yourself.

    Aman....
  • 9. Re: DBMS scheduler 5
    846231 Newbie
    Currently Being Moderated
    Thanks,

    I got it clearer now. So maybe you can give me samples on how to configure dbms_scheduler to output logs to file, like what you said using dbms_file? :)

    I got this note from Justin in other post thread.
    It would generally make much more sense to use the UTL_FILE package if you want to generate a flat file from PL/SQL rather than using DBMS_OUTPUT and SQL*Plus's SPOOL command. 
    You're not going to be limited by the size of the DBMS_OUTPUT buffer and you're not going to risk your code breaking when some function that you call decides to add calls to dbms_output for debugging.
    
    Justin
    Which is better to use? dbms_file or utl_file? Can you give me sample on both?




    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points