Home » RDBMS Server » Server Administration » dbms job not created (10.2.0.4)
dbms job not created [message #529768] Wed, 02 November 2011 15:54 Go to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Hi Experts,
I am running the follwing statment and it is displaying
job no.But when i am checking in dba_jobs,I am not able
to find it.Is it an oracle bug?
SQL>   DECLARE
  2  	       X NUMBER;
  3  	     BEGIN
  4  	       SYS.DBMS_JOB.SUBMIT
  5  		 ( job	     => X
  6  		  ,what      => 'PURGE_CONTROLLER(''A'');'
  7  		  ,next_date => to_date('02/11/2011 14:00:00','dd/mm/yyyy hh24:mi:ss')
  8  		  ,interval  => '/*1:Days*/ sysdate + 1'
  9  		  ,no_parse  => TRUE
 10  		 );
 11  	       SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12  	     END;
 13  	     /
Job Number is: 122                                                              

PL/SQL procedure successfully completed.
Re: dbms job not created [message #529770 is a reply to message #529768] Wed, 02 November 2011 16:20 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe you should read the usage notes in the documentation
Re: dbms job not created [message #529780 is a reply to message #529770] Wed, 02 November 2011 22:23 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
my test is not any problem.
SQL> set serveroutput on;
SQL> Declare
  2    x Number;
  3  Begin
  4    Sys.Dbms_Job.Submit(Job       => x,
  5                        What      => 'PURGE_CONTROLLER(''A'');',
  6                        Next_Date => To_Date('02/11/2011 14:00:00',
  7                                             'dd/mm/yyyy hh24:mi:ss'),
  8                        Interval  => 'sysdate + 1',
  9                        No_Parse  => True);
 10    Sys.Dbms_Output.Put_Line('Job Number is: ' || To_Char(x));
 11  End;
 12  /
Job Number is: 104

PL/SQL procedure successfully completed.

SQL> Select job From dba_jobs aa
  2  Where aa.JOB =104;

       JOB
----------
       104

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Re: dbms job not created [message #529781 is a reply to message #529780] Wed, 02 November 2011 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>my test is not any problem.
CONGRATULATIONS!
problem has been solved.
Re: dbms job not created [message #529797 is a reply to message #529768] Thu, 03 November 2011 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Is it an oracle bug?

No, it is a user bug.

In addition to documentation you can read an introduction in DBMS_JOB wiki page.

Regards
Michel
Re: dbms job not created [message #529824 is a reply to message #529797] Thu, 03 November 2011 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
@andy_huang - try querying the job from another session.
Re: dbms job not created [message #529866 is a reply to message #529768] Thu, 03 November 2011 08:08 Go to previous messageGo to next message
varunvir
Messages: 389
Registered: November 2007
Senior Member
Thanks Experts for providing the documents.
It was due to missing commit.

-Varun
Re: dbms job not created [message #529867 is a reply to message #529824] Thu, 03 November 2011 08:09 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
cookiemonster, you must commit your job after job.
 Declare
   x Number;
 Begin
   Sys.Dbms_Job.Submit(Job       => x,
                       What      => 'PURGE_CONTROLLER(''A'');',
                       Next_Date => To_Date('02/11/2011 14:00:00',
                                            'dd/mm/yyyy hh24:mi:ss'),
                       Interval  => 'sysdate + 1',
                       No_Parse  => True);
   commit; -- it must commit!                 
   Sys.Dbms_Output.Put_Line('Job Number is: ' || To_Char(x));
 End;
Re: dbms job not created [message #529881 is a reply to message #529867] Thu, 03 November 2011 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, this is what is stated in "Warning" section in the link I posted.

Regards
Michel
Re: dbms job not created [message #529891 is a reply to message #529881] Thu, 03 November 2011 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
And it's stated in the usage notes in the docs I pointed to in the first place.
Re: dbms job not created [message #529893 is a reply to message #529891] Thu, 03 November 2011 09:20 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As well, I mentioned the wiki page only because it is a short introduction easy to read, the documentation is a better input.

Regards
Michel
Previous Topic: Multiple plans for a particular SQL
Next Topic: pgs allocated issue
Goto Forum:
  


Current Time: Fri Nov 29 04:40:10 CST 2024