Not a vallid month

Hello,

i have a strange oracle problem. I got  2 procedures one is for passing date parameter to another.

Now if i ran procedure in SQL developer everything works as it should ... now i have tried to put it to a job and ran it every day periodically and it fails with "is not a valid month" error.

I am on a 10.0.2 database.

A smal portion of my code is here:

Header of procedure 1 :

create or replace
PROCEDURE   p_run_by_param
IS
begin
DECLARE

	date_of varchar2(50); 
	date_to varchar2(50); 

begin


  date_of := to_char(trunc(SYSDATE,'mm'),'dd.mm.yyyy');
  date_to := to_char(SYSDATE,'dd.mm.yyyy');
  
    p_real(date_of,date_to);
 
 end;

Open in new window


And procedure 2 :

create or replace
PROCEDURE p_real
(date_of date,date_to date)
is
BEGIN
    
   select
   *
   from table
   where date between to_char(date_of,'dd.mm.yyyy') and to_char(date_to,'dd.mm.yyyy');
  
 end;

Open in new window


Thank you!
Caruso_euAsked:
Who is Participating?
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Try:

select systimestamp at time zone 'Europe/Vienna' from dual;

Result does not look like a date to me... I'd remove the at time zone part.
0
 
lwadwellCommented:
I have to ask why, when p_real accepts two parameters of datatype date, that varchar2 strings are passed?  Why not just call with the date values?  e.g.
 
    p_real(trunc(SYSDATE,'mm'),trunc(sysdate));

When you pass the varchar2 into a date datatype ... an implicit conversion to date is occuring ... and because of the format most likely, it is causing the error.
0
 
lwadwellCommented:
also
    date between to_char(date_of,'dd.mm.yyyy') and to_char(date_to,'dd.mm.yyyy');
seems extremely odd too.  What is the datatype of the 'date' column involved?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
for any date data types, please use only DATE data type so that you will not get into these type of issues invariably though you can play with the dates by storing them in varchar2.
0
 
Caruso_euAuthor Commented:
Even if i pass everything as date for example:

create or replace
PROCEDURE   p_run_by_param
IS
begin
DECLARE

	date_of date; 
	date_to date; 

begin


  date_of := trunc(SYSDATE,'mm');
  date_to := SYSDATE;
  
    p_real(date_of,date_to);
 
 end;

Open in new window


and

create or replace
PROCEDURE p_real
(date_of date,date_to date)
is
BEGIN
    
   select
   *
   from table
   where date between date_of and date_to;
  
 end;

Open in new window


it still fails with same error.

But again : It WORKS is SQL developer ... fails only when i create job.
0
 
lwadwellCommented:
Are you sure there is no other date conversion, implied or explicit, occurring elsewhere in the code?  That sort of error is during a to_date() type of operation.  
The difference between SQL Developer and a job ... will most likely by SESSION based environment defaults (particularly NLS_DATE_FORMAT) - I am guessing that SQL Developer is setting one on login which is different to the system default used by the job.
This is why implicit conversions are bad, very bad.
0
 
Caruso_euAuthor Commented:
hmm its quite strange really.   I even fail wih this: (note i have commented call to procedure)

create or replace
PROCEDURE   p_run_by_param
IS
begin
DECLARE

	date_of date; 
	date_to date; 

begin


  date_of := trunc(SYSDATE,'mm');
  date_to := SYSDATE;
  
   -- p_real(date_of,date_to);
 
 end;

Open in new window


have also tried this and same error.  What would be correct form of this procedure?

create or replace
PROCEDURE   p_run_by_param
IS
begin
DECLARE

	date_of date; 
	date_to date; 

begin


  date_of := '10.08.2012';
  date_to := 12.09.2012;
  
   -- p_real(date_of,date_to);
 
 end;

Open in new window

0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Procedure 2 will not compile because you cannot select something in a procedure without assigning the result somewhere. So you would have to declare a variable and select the result into that, something like this:

create or replace PROCEDURE p_real (date_of date,date_to date) as
date_count number;
begin
select count (*) into date_count from table where date between date_of and date_to;
end;

Open in new window

0
 
lwadwellCommented:
What is the exact error message and how are you running the job?
0
 
Caruso_euAuthor Commented:
I create job inside of enterprize manager:

BEGIN
sys.dbms_scheduler.create_job( 
job_name => '"SYSTEM"."p_real"',
job_type => 'PLSQL_BLOCK',
job_action => 'scott.p_real;',
repeat_interval => 'FREQ=DAILY;BYHOUR=10;BYMINUTE=10',
start_date => systimestamp at time zone 'Europe/Vienna',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'test',
auto_drop => FALSE,
enabled => TRUE);
END;

Open in new window


and exact error msg is:

ORA-01843: not a valid month

Open in new window

0
 
lwadwellCommented:
That job schedule is calling p_real without parameters, not p_run_by_param
0
 
Caruso_euAuthor Commented:
@gerwinjansen:

select systimestamp at time zone 'Europe/Vienna' from dual;

Result does not look like a date to me... I'd remove the at time zone part.

This was the solution!  Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.