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

Sybrand Bakker wrote:
> On 14 Sep 2006 16:59:16 -0700, antonyliu2002_at_yahoo.com wrote:
>
> >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.
>
>
> 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. Received on Fri Sep 15 2006 - 23:52:36 CDT