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

antonyliu2002_at_yahoo.com wrote:
>
> 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 Received on Sun Sep 17 2006 - 12:46:33 CDT