Re: I can't schedule more than one jobs with Oracle Scheduler

antonyliu2002_at_yahoo.com wrote:
> I've asked this question at oracle.misc, but haven't got this problem
> solved. So, I would like to try my luck here.
>
> Here is the situation.
>
> The office where I work uses Oracle 10g.
>
> I need to run a series of sql commands each night to update the Oracle
> database. These sql commands are basically like:
>
> insert into some_table blah blah;
> update some_other_table blah blah;
> drop yet_another_table blah blah;
> create a_table blah blah;
>
> I have no problem running all these sql commands from SQL*Plus console.
>
> I attempted to put all these sql commands into one scheduled job. I
> followed the instructions at the following link:
>
> http://www.oracle.com/technology/obe/obe10gdb/manage/scheduler/schuser.htm
>
> by checking out the oracle web service via http://<hostname>:5500/em ,
> I then select administration, under scheduler heading, I select jobs
> and then in the "Command" textarea
> (with command type "PL/SQL Block"), right between "begin" and "end;", I
> simply copy-paste the series of sql commands which I manually run every
> day from SQL*Plus console.
>
> But then, when I hit the "Apply" button, the scheduler always whines as
> below:
>
> SQL Error
>
> Failed to commit: ORA-16612: string value too long for attribute
> "job_action" ORA-06512: at "SYS.DBMS_ISCHED", line 814 ORA-06512: at
> "SYS.DBMS_SCHEDULER", line 1209 ORA-06512: at line 3
>
> I googled hard, but the only solution to this problem I've found is
> "use a shorter string" :-).
>
> But, I can successfully create a job by using only one of those sql
> commands, and it runs with no problem.
>
> Then, I thought, OK, let me just create a series of jobs, each of which
> executes only one sql commands.
>
> But, I am not lucky. It looks like I can only schedule one job. If I
> hit "create" and schedule another job, then previously scheduled jobs
> will be automatically disabled.
>
> I am new to Oracle, any idea on how to shoot this problem? Thanks a
> lot.

The easiest way is to encapsulate these commands into a stored procedure (you may need to use Dynamic SQL, that is, EXECUTE IMMEDIATE command, for DDL, like DROP/CREATE,) and schedule this procedure. This will guarantee that all commands are executed in desired order, you will not need to reschedule new block if the commands change - you will simply edit the procedure - and scheduled action string will be as short as possible: BEGIN my_procedure; END;.

Another way is to use cron or Windows Task Manager (depends on which platform you're on) to schedule execution of the SQL*Plus script, maybe right on the host where the target Oracle instance runs.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu Sep 14 2006 - 01:28:38 CDT