• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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!
0
Caruso_eu
Asked:
Caruso_eu
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now