Home » SQL & PL/SQL » SQL & PL/SQL » Need help in creating a job. (Oracle 11g)
Need help in creating a job. [message #576072] Thu, 31 January 2013 05:33 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I am trying to schedule a job using DBMS_SCHEDULER.

The job has to start on SATURDAY 12:00 midnight and should run every on SATURDAY 12:00 midnight.

Even if I create the job on MONDAY first it should start on coming sunday and following sundays.

But I am getting the below error.

ORA-27452: BEGIN APPS_GLOBAL.POPULATE_TARGET144('APPS_XX','APPS_BE',5959); end; is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2


The Job script is

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'TEST_RAM',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'BEGIN APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959); END;',
      start_date        => TRUNC(SYSDATE),
      repeat_interval   => 'FREQ=DAILY; BYDAY=SAT; BYHOUR=24;',
      comments          => 'Job scheduler'
   );
END;


Please help me on logic and as we as on the error.

Thanks in advance.

Report message to a moderator

Re: Need help in creating a job. [message #576073 is a reply to message #576072] Thu, 31 January 2013 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Maybe frequency is weekly if you want to execute it once per week.

Quote:
t should start on coming sunday and following sundays


So maybe BYDAY is not SAT but SUNDAY.

Quote:
job_type => 'STORED_PROCEDURE',
job_action => 'BEGIN APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959); END;',


If job_type is a stored procedure then the action is a stored procedure name not a PL/SQL block.

I advice you to search on Google for examples on a how to use DBMS_SCHEDULER.

Regards
Michel

Report message to a moderator

Re: Need help in creating a job. [message #576074 is a reply to message #576072] Thu, 31 January 2013 06:00 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
The job has to start on SATURDAY 12:00 midnight and should run every on SATURDAY 12:00 midnight.

Even if I create the job on MONDAY first time it should start on coming SATURDAY and following SATURDAYs.

I have modified the action still I am getting the error.

BEGIN
   DBMS_SCHEDULER.create_job (
      job_name          => 'TEST_RAM',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959);',
      start_date        => TRUNC(SYSDATE),
      repeat_interval   => 'FREQ=DAILY; BYDAY=SAT; BYHOUR=24;',
      comments          => 'Job scheduler'
   );
END;

ORA-27452: APPS_GLOBAL.POPULATE_TARGET144('APPS_XX','APPS_BE',5959); is an invalid name for a database object.
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 2

Please help me.

Thanks

Report message to a moderator

Re: Need help in creating a job. [message #576087 is a reply to message #576074] Thu, 31 January 2013 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
"APPS_GLOBAL.POPULATE_TARGET144(''APPS_XX'',''APPS_BE'',5959);" is NOT a name, is it?
Use jot_type='PLSQL_BLOCK' and the job_action you given in your first post.

Regards
Michel

Report message to a moderator

Re: Need help in creating a job. [message #576095 is a reply to message #576087] Thu, 31 January 2013 07:31 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have written the following code and I am able to create the job.

DECLARE
   n1 VARCHAR2(20) :='APPS_XX';
   n2 VARCHAR2(20) :='APPS_BE';
   n3   NUMBER := 5959;
BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      'TEST_RAM',
      'STORED_PROCEDURE',
      job_action            => 'APPS_GLOBAL.POPULATE_TARGET144',
      number_of_arguments   => 3,
      start_date            => TRUNC(SYSTIMESTAMP),
      repeat_interval       => 'FREQ=WEEKLY;BYDAY=SAT',
      enabled               => FALSE,
      comments          => 'POPULATE_TARGET144 procedure scheduler'
   );
   DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name            => 'TEST_RAM',
                                          argument_position   => 1,
                                          argument_value      => n1);
   DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name            => 'TEST_RAM',
                                          argument_position   => 2,
                                          argument_value      => n2);
   DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name            => 'TEST_RAM',
                                          argument_position   => 3,
                                          argument_value      => n3);                                          
   DBMS_SCHEDULER.ENABLE ('TEST_RAM');
   DBMS_SCHEDULER.RUN_JOB ('TEST_RAM');
END;
/


After creating it should I do any thing?

Is there any difference between creating a job using jot_type='PLSQL_BLOCK' and jot_type='STORED_PROCEDURE' ?

Please help me thanks.

Report message to a moderator

Re: Need help in creating a job. [message #576096 is a reply to message #576095] Thu, 31 January 2013 07:39 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Please verify as per my below requirent my logic is correct or not?

The job has to start on SATURDAY 12:00 midnight and should run on every SATURDAY 12:00 A.M. midnight.
Even if I create the job on MONDAY first time it should start on coming SATURDAY and continuous on following SATURDAYs.
For example I have created a job on MONDAY 10:00 PM on 28/JAN/2013,then the job first time should run on
coming saturday 12:00 AM i.e. 02/FEB/2013 12:00 A.M.

start_date            => TRUNC(SYSTIMESTAMP),
repeat_interval       => 'FREQ=WEEKLY;BYDAY=SAT'


Please help me.

Thanks

Report message to a moderator

Re: Need help in creating a job. [message #576104 is a reply to message #576096] Thu, 31 January 2013 08:15 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You also have to specify the time (BYHOUR=0).

Regards
Michel

Report message to a moderator

Previous Topic: Order by Month
Next Topic: Convert to numberic/date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 26 00:48:26 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.