OraFAQ Forum: Reports & Discoverer » Problem with Date parameter, passing from OTM to Oracle reports

Home » Developer & Programmer » Reports & Discoverer » Problem with Date parameter, passing from OTM to Oracle reports (Oracle Reports 10g, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with Date parameter, passing from OTM to Oracle reports [message #573235]

Mon, 24 December 2012 10:45

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.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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'.

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

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;

Report message to a moderator

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

Littlefoot
Messages: 21823
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.

Report message to a moderator

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

hamb
Messages: 5
Registered: December 2012

Junior Member

I will use these tags next time.

Thanks for your help.

Report message to a moderator

Previous Topic:	importing the .doc to the oracle 10g
Next Topic:	Javaw - psoasyn error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 05:23:39 CST 2024