Re: We can only schedule one job to run with Oracle 10g?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 14 Sep 2006 07:18:32 -0700
Message-ID: <1158243512.690509.214840@i3g2000cwc.googlegroups.com>

antonyliu2002_at_yahoo.com wrote:
> Mark D Powell wrote:
> > antonyliu2..._at_yahoo.com wrote:
> > > Mark D Powell wrote:
> > > > antonyliu2002_at_yahoo.com wrote:
> > > > > Mark D Powell wrote:
> > > > > > antonyliu2002_at_yahoo.com wrote:
> > > > > > > I have 2 questions concerning Oracle scheduler.
> > > > > > >
> > > > > > > 1. I was attempting to schedule multiple jobs to run from midnight to
> > > > > > > 7AM each day, but it looks like if I create a new job, any other
> > > > > > > scheduled job will be automatically disabled.
> > > > > > >
> > > > > > > Does this mean that we can only schedule one job at to run at a time?
> > > > > > >
> > > > > > > 2. Then I tried combining these separate jobs into one, but only to
> > > > > > > find that Oracle keeps complaining about a string being too long:
> > > > > > >
> > > > > > > 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
> > > > > > >
> > > > > > > What's the reason?
> > > > > >
> > > > > > The first message implies that you have an error in your attribute
> > > > > > list. How are you attempting to schedule the job, manually or via one
> > > > > > of the tools: dbconsole?.
> > > > > >
> > > > > > If manualy, how about posting the command in use to give someone
> > > > > > something to look at?
> > > > > >
> > > > > > If via a tool, what tool from which screen? Can you perform the same
> > > > > > task manually? What happens?
> > > > > >
> > > > > > HTH -- Mark D Powell --
> > > > >
> > > > > Thanks Mark.
> > > > >
> > > > > I forgot to mention that I was attempting to do that through Oracle Web
> > > > > service by logging into the DBA account from localhost:5500/em
> > > > >
> > > > > And then select administration, under scheduler, select jobs and then
> > > > > hit create, and then copy-paste the SQl script.
> > > > >
> > > > > You have an idea now?
> > > > >
> > > > > Thanks.
> > > >
> > > > How about posting the paste? Doesn't the screen allow you to schedule
> > > > a task by filling in blanks and marking check boxes? Unfortunately my
> > > > 10g system had to be reused and I do not have it back yet so I cannot
> > > > bring up the tool.
> > > >
> > > > Have you tried to just use dbms_scheduler.create_job via SQL*Plus?
> > > >
> > > > -- Mark D Powell --
> > >
> > > There are no check boxes to check. I've never tried dbms_scheduler
> > > with SQL*Plus.
> >
> > Where is the paste (command)?
> >
> > -- Mark D Powell --

>

> Hi, Mark,

>

> I am sorry that I cannot paste the SQL query here, it is for students'
> privacy. Although I can modify it and put it here, but then I would be
> showing you a different SQL command. Also, it is really lengthy, I am
> not sure if you would have the patience to read it through. The text
> file that contains the sql commands is 8K bytes.

>

> That said, I have no problem running exactly the same query at SQL*Plus
> console. But, if I put the same sql commands in the job I create, I
> get the error message.

>

> And if I create a job with each single sql query, Oracle Scheduler does
> not complain. It complains only if I attempt to combine all sql
> commands into a single job.

Then perhaps the parameter "too long" error refers to the command itself when entered via the tool. SQL*Plus provides you an alternate so at least you were able to schedule the task. If you are entering the pl/sql that makes up the job body as part of the command you might try creating a stored procedure and then scheduling the procedure and see if that works.

HTH -- Mark D Powell -- Received on Thu Sep 14 2006 - 09:18:32 CDT