Home » Developer & Programmer » Reports & Discoverer » Problem with Date parameter, passing from OTM to Oracle reports (Oracle Reports 10g, Windows)
icon5.gif  Problem with Date parameter, passing from OTM to Oracle reports [message #573235] Mon, 24 December 2012 10:45 Go to next message
hamb
Messages: 5
Registered: December 2012
Junior Member
I am trying to pass a date value from OTM to Oracle reports 10g.


Parameter defined in OTM & in Oracle Reportd :- parameter name "P_H_FROM_Date" (date datatype in otm and oracle reports without any inputmask)
Parameter defined in Oracle Reports :- P_L_Date (datatype date without any inputmask)


SQL Query Date statement:
and to_date(sh.start_time,'YYYY-MM-DD') >= to_date(:P_L_DATE,'YYYY-MM-DD')

Under set_lexical_parameter function (following is the date check):
In Property Inspector :
P_DATE_FORMAT : character (Initial Value: YY-MM-DD)
sh.start_time = data type Date in database



if value_entered(:P_H_FROM_DATE) then
:P_L_DATE := 'AND sh.start_time '||get_filter_condition( :P_H_FROM_DATE, :P_DATE_FORMAT);
else
:P_L_DATE := 'and 1=1';
end if;




*Error*
REP-546: Value does not match mask 'DD-MON-RR'.
ORA-01858: a non-numeric character was found where a numeric was expected

REP-0091: Invalid value for parameter 'P_H_FROM_DATE'.

Please help me, I dont know how to resolve this issue and I need to resolve it ASSP.

Thanks.




Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573241 is a reply to message #573235] Mon, 24 December 2012 15:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, maybe you should uniform date format masks you use. In these several lines you posted, I found:
YYYY-MM-DD
YY-MM-DD
DD-MON-RR
(plus unknown :P_DATE_FORMAT). I guess that, once you know what you want, Oracle will be happier as well.

Because, Oracle expected DD-MON-RR and you provided something that doesn't match.
Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573374 is a reply to message #573241] Thu, 27 December 2012 08:44 Go to previous messageGo to next message
hamb
Messages: 5
Registered: December 2012
Junior Member
I am not using the :P_DATE_FORMAT in my report . I have set the :P_H_FROM_DATE value like this in AfterPFrom Trigger;

:P_H_FROM_DATE := to_char(:P_H_FROM_DATE,'YYYY-MM-DD');


In set_lexical_parameter function;

if value_entered(:P_H_FROM_DATE) then
:P_L_DATE := 'AND sh.start_time '||get_filter_condition( :P_H_FROM_DATE);


But i am still getting this error;

Error

REP-546: Value does not match mask 'YYYY-MM-DD'.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

REP-0091: Invalid value for parameter 'P_H_FROM_DATE'.




Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573381 is a reply to message #573374] Thu, 27 December 2012 10:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, nothing much changed. This time, Oracle expected YYYY-MM-DD, but you provided something different.
Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573383 is a reply to message #573381] Thu, 27 December 2012 11:54 Go to previous messageGo to next message
hamb
Messages: 5
Registered: December 2012
Junior Member
My problem has been solved. I have changed the date_format to DD-MON-YYYY and add another user parameter with date_format like;

'AND to_char(sh.start_time,''DD-MON-YY'')'||:P_L_DATE_VAR;
Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573384 is a reply to message #573383] Thu, 27 December 2012 12:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm glad that you solved the problem. However, I somehow doubt that code you posted evaluates to something that really works. As if " = " (or similar) is missing.

Please, before you post your next message that contains some code, have a look at [url=http://www.orafaq.com/forum/t/174502/72104/]How to use
 tags and make your code easier to read?
. It will teach you how to use [code] tags; because, your code will be easier to read if it is enclosed into the [code] tags, not if you paint it into different colors.
Re: Problem with Date parameter, passing from OTM to Oracle reports [message #573385 is a reply to message #573384] Thu, 27 December 2012 12:05 Go to previous message
hamb
Messages: 5
Registered: December 2012
Junior Member
I will use these tags next time.

Thanks for your help.
Previous Topic: importing the .doc to the oracle 10g
Next Topic: Javaw - psoasyn error
Goto Forum:
  


Current Time: Thu Mar 28 15:37:19 CDT 2024