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

Brian Peasland wrote:
> 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

Many thanks to all of you who gave me hints. I am learning to create a stored procedure for the jobs I would like to create. Hopefully, using stored procedure will solve the problem.

I suspect that Oracle will force people to use stored procedure for scheduled jobs instead of raw sql commands if the commands tend to be too lengthy. Is this a reasonable conjecture?

Thanks again. Received on Thu Sep 14 2006 - 14:40:11 CDT