Hi all,
I am using ApEx 4.0.2 and I have a problem with dates. What follows is not the full problem or the full definition of what I am trying to acheive but it demonstrates my issue:
I have two databases (ICIS and REF) and I need to pull data from the ICIS Database (which does not have ApEx installed) into the REF database (which is where we build our ApEx applications). I have a database link from REF to ICIS.
If I sign on to the REF Database via SQL Developer and run any of the following four commands:
1. select sysdate from dual where sysdate > '01-JAN-2000';
2. select sysdate from dual@ref2icis where sysdate > '01-JAN-2000';
3. select sysdate from dual where sysdate > TO_DATE('01-JAN-2000','DD-MON-YYYY');
4. select sysdate from dual@ref2icis where sysdate > TO_DATE('01-JAN-2000','DD-MON-YYYY');
I always get the result '17-AUG-11'.
However, if I run the same four commands via the ApEx SQL Command Window I get the following results:
1. ORA-01858: a non-numeric character was found where a numeric was expected
2. ORA-01858: a non-numeric character was found where a numeric was expected ORA-02063: preceding line from REF2ICIS
3. 08/17/2011
4. 08/17/2011
I note the format of the succesfully displayed date is MM/DD/YYYY - the standard US format, which is not the same as our database formats which are all 'DD-MON-RR'. I think this format difference is causing the errors in tests 1 and 2.
So, my question is:
Is ApEx interfering with the Date Format somehow? If so, can I stop it doing so or can I change the format to match our default format?
Thanks for your help,
Martin