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

I tried putting all of the SQL commands into a simple procedure, but the database got frozen while it was compiling this procedure. It did not make any complaints, but just hang there for a long time, and seemed to have frozen the database, because the web application which uses this dtabase did not work.

I have never attempted to write any stored procedure until today. So I am not sure if I did it right. All I did is this:

CREATE OR REPLACE PROCEDURE myprocedure
IS
BEGIN
// Here I insert all of the sql commands. END;
/

Procedures created through this format was OK, if I use much fewer sql commands between 'BEGIN' and 'END'.

So, any solution to this problem? There isn't anyway to shedule more than one jobs?

Thanks. Received on Thu Sep 14 2006 - 18:59:16 CDT