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

[a***@yahoo.com]

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.

[Vladimir M. Zakharychev]

Post by a***@yahoo.com
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
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
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
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.

The easiest way is to encapsulate these commands into a stored
procedure (you may need to use Dynamic SQL, that is, EXECUTE IMMEDIATE
command, for DDL, like DROP/CREATE,) and schedule this procedure. This
will guarantee that all commands are executed in desired order, you
will not need to reschedule new block if the commands change - you will
simply edit the procedure - and scheduled action string will be as
short as possible: BEGIN my_procedure; END;.

Another way is to use cron or Windows Task Manager (depends on which
platform you're on) to schedule execution of the SQL*Plus script, maybe
right on the host where the target Oracle instance runs.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

[sybrandb]

Post by a***@yahoo.com
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
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
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
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.

[Brian Peasland]

Post by a***@yahoo.com
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
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
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
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
***@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

[a***@yahoo.com]

Post by Brian Peasland

Post by a***@yahoo.com
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
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
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
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
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.

[Vladimir M. Zakharychev]

Post by a***@yahoo.com
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?

No, not really. Oracle doesn't force you to do it in some particular
way, there are plenty of options. The problem is that SQL VARCHAR2 type
is constrained to 4000 bytes, so you can't schedule code longer than
that directly. However, wrapping it into a stored procedure will remove
this limitation. Note that you can't put DDL in PL/SQL stored
procedures as is, it has to be executed using Dynamic SQL (EXECUTE
IMMEDIATE command or a call to one of SYS.DBMS_SQL package
subprograms.) DML can be put into a stored procedure as is and doesn't
require Dynamic SQL. If you can't tell the difference between DDL and
DML, then a visit to http://tahiti.oracle.com and reading of Concepts
manual for your Oracle release is due. Actually, it's first thing you
should've done when you started working with Oracle, and then you
should re-read it for each new release you are going to migrate or
upgrade to (at least "what's new" section if you memorized the rest.)

As of your original question about possibility to schedule more than
one job - that's definitely possible, I have no idea why you can't do
it. Might be a defect in EM or you are doing something wrong.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

[a***@yahoo.com]

Post by Brian Peasland

Post by a***@yahoo.com
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
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
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
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
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.

[Sybrand Bakker]

Post by a***@yahoo.com
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
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.

Did you follow the previous directions

so
create or replace procedure blah as
begin
insert into foo select * from bar;
/* Note execute immediate mandatory!!! */
execute immediate 'drop table bar';
end;
/

If the database got 'frozen' there must be something seriously wrong
with the configuration of your database and/or your machine has
insufficient RAM, so is paging like hell.
This has, I am afraid, nothing to do with Oracle, but everything with
your configuration.


--
Sybrand Bakker, Senior Oracle DBA

[a***@yahoo.com]

Post by Sybrand Bakker

Post by a***@yahoo.com
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
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.

Did you follow the previous directions
so
create or replace procedure blah as
begin
insert into foo select * from bar;
/* Note execute immediate mandatory!!! */
execute immediate 'drop table bar';
end;
/
If the database got 'frozen' there must be something seriously wrong
with the configuration of your database and/or your machine has
insufficient RAM, so is paging like hell.
This has, I am afraid, nothing to do with Oracle, but everything with
your configuration.
--
Sybrand Bakker, Senior Oracle DBA

Hi, Sybrand, thanks for your note, but I am confused by your message.
What do you mean by "Note execute immediate mandatory" as shown below?

insert into foo select * from bar;
/* Note execute immediate mandatory!!! */
execute immediate 'drop table bar';

Do you mean that "execute immediate <some sql command>" is mandatory in
a stored procedure?

I've been trying to schedule the job for about 2 weeks, without good
luck. Apparently the scheduler won't let me put all of the sql
commands into a single job. So, let me ask one question:

Is it possible at all to schedule more than 1 jobs with Oracle
Scheduler and Oracle Database 10g? Yes or no? Thanks.

[Sybrand Bakker]

Post by a***@yahoo.com
Hi, Sybrand, thanks for your note, but I am confused by your message.
What do you mean by "Note execute immediate mandatory" as shown below?
insert into foo select * from bar;
/* Note execute immediate mandatory!!! */
execute immediate 'drop table bar';
Do you mean that "execute immediate <some sql command>" is mandatory in
a stored procedure?

I didn't. I meant 'drop table ... ' *must* be wrapped in execute
immediate. No direct DDL in stored procedures (nor anonymous blocks)

Post by a***@yahoo.com
I've been trying to schedule the job for about 2 weeks, without good
luck. Apparently the scheduler won't let me put all of the sql
commands into a single job.

Utter nonsense. It has be shown to you several times how to do it.

Post by a***@yahoo.com
Is it possible at all to schedule more than 1 jobs with Oracle
Scheduler and Oracle Database 10g? Yes or no? Thanks.

This is a nonsensical question. Dbms_job, the predecessor of
dbms_schedule, already allowed scheduling multiple jobs.
Apart from that: you dont' *need* multiple jobs in this particular
case, and you *shouldn't* use them is this particular case.

I won't respond any further in this particular thread. Me, and others,
have shown you the *exact* code, and still you don't get it and come
up with nonsensical questions, showing you never opened those manuals,
and are not prepared to do so.
That precludes a successful Oracle career. However, that is not a
problem with Oracle, nor is it a problem with this forum, but it is a
problem with *YOU*. Either start reading those manuals, or go away and
choose a different database product, as Oracle is apparently too
difficult for you.

--
Sybrand Bakker, Senior Oracle DBA

[a***@yahoo.com]

Post by Sybrand Bakker

Post by a***@yahoo.com
Hi, Sybrand, thanks for your note, but I am confused by your message.
What do you mean by "Note execute immediate mandatory" as shown below?
insert into foo select * from bar;
/* Note execute immediate mandatory!!! */
execute immediate 'drop table bar';
Do you mean that "execute immediate <some sql command>" is mandatory in
a stored procedure?

I didn't. I meant 'drop table ... ' *must* be wrapped in execute
immediate. No direct DDL in stored procedures (nor anonymous blocks)

Post by a***@yahoo.com
I've been trying to schedule the job for about 2 weeks, without good
luck. Apparently the scheduler won't let me put all of the sql
commands into a single job.

Utter nonsense. It has be shown to you several times how to do it.

Post by a***@yahoo.com
Is it possible at all to schedule more than 1 jobs with Oracle
Scheduler and Oracle Database 10g? Yes or no? Thanks.

This is a nonsensical question. Dbms_job, the predecessor of
dbms_schedule, already allowed scheduling multiple jobs.
Apart from that: you dont' *need* multiple jobs in this particular
case, and you *shouldn't* use them is this particular case.
I won't respond any further in this particular thread. Me, and others,
have shown you the *exact* code, and still you don't get it and come
up with nonsensical questions, showing you never opened those manuals,
and are not prepared to do so.
That precludes a successful Oracle career. However, that is not a
problem with Oracle, nor is it a problem with this forum, but it is a
problem with *YOU*. Either start reading those manuals, or go away and
choose a different database product, as Oracle is apparently too
difficult for you.
--
Sybrand Bakker, Senior Oracle DBA

Hmm, an old geezer who is experiencing bad menopause. If you think
that my questions are nonsensical, simply ignore it. Why bother
barking here? Did I in any way offend you? Anyway, this is off-topic
in this forum.

[Sybrand Bakker]

Post by a***@yahoo.com
Hmm, an old geezer who is experiencing bad menopause. If you think
that my questions are nonsensical, simply ignore it. Why bother
barking here? Did I in any way offend you? Anyway, this is off-topic
in this forum.

Yes, of course you DID offend me. I tried to help you out, I posted
the exact code and you continue to whine and ask the same question
over and over again, without even researching those parts of my code
you don't understand like 'execute immediate'
Apparently you are too lazy to look anything up in manuals, and as a
reward for my help I now have to endure your stupid flames.
Could you PLEASE GET LOST in this forum? Oracle is not for you, you
are just TOO STUPID AND TOO LAZY to use it!!!!

--
Sybrand Bakker, Senior Oracle DBA

[a***@yahoo.com]

Post by Sybrand Bakker

Post by a***@yahoo.com
Hmm, an old geezer who is experiencing bad menopause. If you think
that my questions are nonsensical, simply ignore it. Why bother
barking here? Did I in any way offend you? Anyway, this is off-topic
in this forum.

Yes, of course you DID offend me. I tried to help you out, I posted
the exact code and you continue to whine and ask the same question
over and over again, without even researching those parts of my code
you don't understand like 'execute immediate'
Apparently you are too lazy to look anything up in manuals, and as a
reward for my help I now have to endure your stupid flames.
Could you PLEASE GET LOST in this forum? Oracle is not for you, you
are just TOO STUPID AND TOO LAZY to use it!!!!
--
Sybrand Bakker, Senior Oracle DBA

Hmm, what a strong reason to get offended, which is quite normal during
menopause.

Hey, stop barking, you need to go see a doctor for your menopause
symptoms. You won't get it cured by barking here.

[DA Morgan]

Post by a***@yahoo.com
Hey, stop barking, you need to go see a doctor for your menopause
symptoms. You won't get it cured by barking here.

If you insist on pursuing this please do so off-line.

Thank you.

[joel garry]

Post by a***@yahoo.com
I've been trying to schedule the job for about 2 weeks, without good
luck. Apparently the scheduler won't let me put all of the sql
Is it possible at all to schedule more than 1 jobs with Oracle
Scheduler and Oracle Database 10g? Yes or no? Thanks.

Keeeeeeeriiiiist.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#i21263
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/limits004.htm
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:388480262167
(search asktom for scheduling jobs for more stuff).

jg
--
@home.com is bogus.
"If I could remove three things from the database - they would be:
Triggers
Autonomous Transactions
When Others (at least if not followed by RAISE or make when others
ALWAYS re-raise as part of the language itself!!)" - Tom Kyte