Apex Interactive Report ORA-1843

Question

I'm trying to display at oracle apex IR query, a biggest date from 3 different tables, using greatest function to choice the biggest. For that, created two varchar2 type functions.

One of them return the date converted to char, of a selected table like this:

    create or replace FUNCTION RETURN_DATES (key in number, parameter in NUMBER)
    RETURN VARCHAR2 IS

    ...
BEGIN
        case parameter

                when 1  then 
                    select distinct MAX(mydate) into seal  from table1 v  WHERE v.num_contract = number_contract;
                   return NVL(TO_CHAR(TO_DATE (seal),'mm/dd/yyyy'),'01/01/1980')
                when 2 then 
                    select... from table2 x WHERE...
...
END;

The second function calls that one using a greatest function between the parameter calls, which refers to case statement:

    create or replace FUNCTION RETURN_BIGGEST_DATE(key in number) return VARCHAR2 IS

    ...
BEGIN
         select greatest (RETURN_DATES(key,1),RETURN_DATES(key,2),RETURN_DATES(key,3)) into greatest_date from dual;    
         return greatest_date;
...
END;

When i call it on sql commands it's works fine, but at an interactive report its fails, returning the ORA-1843, using the quite same query.

Could anyone help?

Jose Gustavo Lima Jose Gustavo Lima · Accepted Answer · 2017-08-01T21:58:16

Simply modifying the RETURN_DATES returning DATE

and the seal variable, of date type, without conversions, works fine at apex IR! I noticed when a date column is relayed on ir query, there are an extra parameter on Column filter, named Date Ranges. It was the two way traffic to start simulations and change the returning type of the that function.

No more changes was required:

create or replace FUNCTION RETURN_DATES (key in number, parameter in NUMBER)
    RETURN DATE IS

...

     select distinct MAX(mydate) into seal  from table1 v  WHERE v.num_contract = number_contract;
                       return NVL(seal),'01/01/1980')

Apex Interactive Report ORA-1843

1 Answers