Home » Developer & Programmer » Forms » COPY function not passing variable to datablock (Oracle Report 11.1.2.2)
COPY function not passing variable to datablock [message #657757] Mon, 21 November 2016 19:26 Go to next message
a.harinath@gmail.com
Messages: 6
Registered: September 2016
Junior Member
Hi All,

I have a strange issue in using the COPY function to pass the date parameter to a datablock.

Attached code is present in the PRE-QUERY trigger in a datablock POS_SEARCH_RESULTS. The form takes the store_no, from_date, to_date, fixed, occasion_id as input parameter. The parameters are passed to another datablock POS_SEARCH_RESULTS which need to query a database table gb_pos_exceptions.

Whenever I pass the from_date in the form, for some reason it never fetch any records and getting "Query Caused No Records To Be Retrieved" error. If I comment the code with respect to the from_date and to_date in attached, then the other parameters are working fine.

Please suggest if there are alternate ways to copy the parameter values to a data block. I tried set_block_property with default_where, still no records error.

Thanks & Regards,
Harinath
  • Attachment: FormError.txt
    (Size: 2.66KB, Downloaded 1671 times)
Re: COPY function not passing variable to datablock [message #657758 is a reply to message #657757] Mon, 21 November 2016 19:30 Go to previous messageGo to next message
a.harinath@gmail.com
Messages: 6
Registered: September 2016
Junior Member
I forgot to mention, I tried to use :system.last_query to check if the appropriate query gets constructed prior passing on the datablock POS_SEARCH_RESULTS, and the query did return results when I executed in TOAD.
Re: COPY function not passing variable to datablock [message #657761 is a reply to message #657758] Tue, 22 November 2016 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Uh, yes ... Forms and dates. Yikes!

As far as I can remember, it requires awkward manipulations that include TO_CHAR and TO_DATE functions (which you DO have in your code) - though, you missed to provide format mask. Try to do that. For example:
' >= to_date(to_char(:POS_SEARCH_CRITERIA.from_date, ''dd.mm.yyyy''), ''dd.mm.yyyy'')';
Re: COPY function not passing variable to datablock [message #657790 is a reply to message #657761] Tue, 22 November 2016 16:43 Go to previous message
a.harinath@gmail.com
Messages: 6
Registered: September 2016
Junior Member
I did try your approach, but not succeful either.
Whats peculiar is this same form work in a different environment with Oracle 11g as database and Oracle Report server 11.1.2.2 running on AIX.
But issue is only when I try to port the same Oracle form onto Oracle 12C database and the report server mounted on Oracle Linux Server 6.

Is there any other option to bind the from_date and to_date to a datablock process_date (database table column)? I suspect issue could be in the COPY function appending 2 input parameters to 1 datablock column.

Thanks
Previous Topic: WHEN-VALIDATE-ITEM for a non-navigable item
Next Topic: SET_ITEM_INSTANCE_PROPERTY
Goto Forum:
  


Current Time: Thu Mar 28 11:12:47 CDT 2024