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.

Actually the error message seems to be spot on. Your
begin <command1>; <command2>; <command3>; end; string is just too long.

I would just
describe dbms_isched
to verify how long the job_action string can be. Then I would convert the series of commands to a stored procedure, just as described in the link you provided.

Disregard the advice of Vladimir with respect to the Windows Scheduler and cron.
They won't resubmit your job when the job fails, they will create clutter ( cron emails any output to your account) and have way less functionality.
Also, when you don't take extra measures, they will expose passwords.

-- 
Sybrand Bakker
Senior Oracle DBA