Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job.submit error
icon5.gif  dbms_job.submit error [message #304480] Wed, 05 March 2008 06:29 Go to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Hi, i am trying to use dbms_job package to run every day at 4 am a process.
but when i try to run the sql, i get an error.
i was searching over the net but its seems what i am doing is ok.
begin
exec dbms_job.submit (:my_job, 'DBMS_STATS.gather_database_stats; END;', 'trunc(SYSDATE)+4/24', 'SYSDATE + 1');
end;
/
SP2-0552: Bind variable "MY_JOB" not declared.


ok i tried using but i get the same error
declare
  my_job number;
begin
  exec dbms_job.submit (:my_job, 'DBMS_STATS.gather_database_stats; END;', 'trunc(SYSDATE)+4/24', 'SYSDATE + 1');
end;
/
SP2-0552: Bind variable "MY_JOB" not declared.


i think that is a stupid thing but... i cant find the answer
thank you in advance

[Updated on: Wed, 05 March 2008 06:40] by Moderator

Report message to a moderator

Re: dbms_job.submit error [message #304485 is a reply to message #304480] Wed, 05 March 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SP2-00552: Bind variable \"%.*s\" not declared.\n
 *Cause:  The specified bind variable was not declared.
 *Action: Run the VARIABLE command to check that the bind variables
          you used in your SQL statement exist. Before running a SQL
          statement with bind variables, you must use the VARIABLE
          command to declare each variable.

In addtion, yuo don't use "exec" in a PL/SQL block. It is a SQL*Plus shortcut for "begin ... end".

Regards
Michel

[Updated on: Wed, 05 March 2008 06:41]

Report message to a moderator

Re: dbms_job.submit error [message #304486 is a reply to message #304480] Wed, 05 March 2008 06:40 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Replace :my_job with my_job
Re: dbms_job.submit error [message #304489 is a reply to message #304485] Wed, 05 March 2008 06:49 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Michel Cadot wrote on Wed, 05 March 2008 06:39
SP2-00552: Bind variable \"%.*s\" not declared.\n
 *Cause:  The specified bind variable was not declared.
 *Action: Run the VARIABLE command to check that the bind variables
          you used in your SQL statement exist. Before running a SQL
          statement with bind variables, you must use the VARIABLE
          command to declare each variable.

In addtion, yuo don't use "exec" in a PL/SQL block. It is a SQL*Plus shortcut for "begin ... end".

Regards
Michel



thank you michael for you answer
i executed VARIABLE command and
SQL> variable
SP2-0568: No bind variables declared.


Quote:

Replace :my_job with my_job



i did it... and that´s the error
SQL> declare
  2     my_job number;
begin
        dbms_job.submit (my_job, 'DBMS_STATS.gather_database_stats; END;', 'trunc(SYSDATE)+4/24', 'SYSDATE + 1');
        commit;
end;
/
  3    4    5    6    7  declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 4
Re: dbms_job.submit error [message #304492 is a reply to message #304480] Wed, 05 March 2008 07:07 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
I think you need a BEGIN in here:

'DBMS_STATS.gather_database_stats; END;'
Re: dbms_job.submit error [message #304494 is a reply to message #304492] Wed, 05 March 2008 07:15 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

coleing wrote on Wed, 05 March 2008 07:07
I think you need a BEGIN in here:

'DBMS_STATS.gather_database_stats; END;'

yes but if i dont use the dbms_job.submit i can schedule that task

Re: dbms_job.submit error [message #304498 is a reply to message #304480] Wed, 05 March 2008 07:17 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

i was trying and i get a step
now i modified the pl but i continue with errors
declare
        my_job number;
begin
        dbms_job.submit (my_job => 66, 'DBMS_STATS.gather_database_stats; END;', 'trunc(SYSDATE)+4/24', 'SYSDATE+1');
        commit;
end;
/
  2    3    4    5    6    7    dbms_job.submit (my_job => 66, 'DBMS_STATS.gather_database_stats; END;', 'trunc(SYSDATE)+4/24', 'SYSDATE+1');
        *
ERROR at line 4:
ORA-06550: line 4, column 2:
PLS-00306: wrong number or types of arguments in call to 'SUBMIT'
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored

thank you in advance again Wink
Re: dbms_job.submit error [message #304499 is a reply to message #304489] Wed, 05 March 2008 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Third parameter is a DATE not a STRING.

Regards
Michel
Re: dbms_job.submit error [message #304500 is a reply to message #304498] Wed, 05 March 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First parameter is OUT not IN one.

Regards
Michel
Re: dbms_job.submit error [message #304504 is a reply to message #304500] Wed, 05 March 2008 07:29 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Michel Cadot wrote on Wed, 05 March 2008 07:18
First parameter is OUT not IN one.

Regards
Michel


the first parameters generate the job_id at dba_jobs?
where it get the number?

what do you think that i might change?
now i am reading this article (at orafaq)
http://www.orafaq.com/node/871
and the schemma to use it is something like this
declare
  my_job number;
begin
  dbms_job.submit(job => my_job, 
    what => 'my_procedure(foo);'
    next_date => sysdate+1,
    interval => 'sysdate+1');
end;
/


any idea?

using the same structure i get
declare
        my_job number;
begin
        dbms_job.submit (job =>my_job , 
                        what =>'DBMS_STATS.gather_database_stats;end;', 
                        next_date => trunc(SYSDATE)+4/24, 
                        interval => 'SYSDATE+1');
        commit;
end;
/
  2    3    4    5    6    7    8    9   10  declare
*
ERROR at line 1:
ORA-06550: line 1, column 131:
PLS-00103: Encountered the symbol "END"
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 4

[Updated on: Wed, 05 March 2008 07:33]

Report message to a moderator

Re: dbms_job.submit error [message #304512 is a reply to message #304504] Wed, 05 March 2008 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Begin" is missing if you use "end;"

Regards
Michel
Re: dbms_job.submit error [message #304516 is a reply to message #304512] Wed, 05 March 2008 08:14 Go to previous messageGo to next message
thorin666
Messages: 144
Registered: March 2007
Senior Member

Michel Cadot wrote on Wed, 05 March 2008 08:00
"Begin" is missing if you use "end;"

Regards
Michel


Sorry michael i make it work
there is how i did it

begin
	dbms_job.submit (:my_job , 'DBMS_STATS.gather_database_stats;', trunc(SYSDATE)+4/24, 'SYSDATE+1');
	commit;
end;
/

One more time... thnx!
Re: dbms_job.submit error [message #304520 is a reply to message #304516] Wed, 05 March 2008 08:35 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to execute it each day at 4AM you have to use 'trunc(SYSDATE)+1+4/24' and not 'SYSDATE+1'.

Regards
Michel
Previous Topic: How call java parameters from PL/SQL procedure
Next Topic: ALTER USER with COMPLEX PASSWORD
Goto Forum:
  


Current Time: Sun Dec 11 06:36:14 CST 2016

Total time taken to generate the page: 0.07306 seconds