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.
>

Have thought about putting your SQL code in a stored procedure? Then running the commands in Scheduler is as simple as executing the stored proc. The other advantage to this tactic is that you can execute the proc in any other app (like SQL*Plus) at other times as necessary, without having to sign on to EM and manually running the job.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown