10 Replies Latest reply on Jul 5, 2018 6:54 AM by kempiak

[BUG] IR filtering on numerical columns gives ORA-06502

kempiak Jun 9, 2018 4:19 PM

Hi the APEX masters,

I struggle for a while to solve the problem with IR numeric column filtering on APEX 18.1.

I have interactive report where one of the columns has numerical values. As I would like to display numbers accordingly to the Polish formatting standards (comma as the decimal separator) I set the Primary Language to Polish (pl) and Application Language Derived from Application Primary Language. This gives me very good rendering results but now the tricky part comes. When I click on the header of the column standard filter is being displayed allowing me to choose one of the distinct values found on the column. When I choose one I receive the message "ORA-06502: PL/SQL: błąd liczby lub wartości: character to number conversion error" and the filter is not applied. Looks like I miss something but I am not able to figure it out.

As the work around of this problem in the security attributes -> Initialization PL/SQL Code I have put the code which alters the session numeric characters NLS_NUMERIC_CHARACTERS = ''. ''. This solves the problem with the filtering but numbers are rendered with period as the decimal separator and this is not what I want. Just to mention I am not allowed to convert my column using to_char. Column in the report has to be based on the original numerical column coming from the database.

I have created demo application on 18.1 - https://apex.oracle.com/pls/apex/f?p=93804:2.

Thanks a lot for your help,

Maciek

1. Re: IR filtering numeric column gives ORA-06502

andreml Jun 8, 2018 4:36 PM (in response to kempiak)

Hi Maciek,
Yes indeed, seems to be an issue / bug.
Should be patched soon...

I tested this with an IR on emp table an following app-settings:

After changing to:
it works as expected.
Regards
Andre
1 person found this helpful
Like Show 0 Likes(0)

Actions
2. Re: IR filtering numeric column gives ORA-06502

andreml Jun 9, 2018 9:30 AM (in response to kempiak)

Hi,
Dear APEX-Team, please take a closer look at this asap.
I just tested this ones more in the brand new Dev Machine with APEX 18.1 installed.
(Application 101 - Sample Reporting, IR on page 1, please enter a value (eg. cost) with decimal places beforehand)
I would consider this as a showstopper for all IR in Apps with non-American "Application Primary Language".
In case I'm wrong please let us know, how to avoid this issue otherwise.
Best Regards
Andre

PS:
Maciek, would you mind to include the phrase BUG into your titel? - just to spot more attention on this topic.
1 person found this helpful
Like Show 0 Likes(0)

Actions
3. Re: [BUG] IR filtering on numerical columns gives ORA-06502

kempiak Jun 9, 2018 4:26 PM (in response to kempiak)

Dear colleagues,

Just to mention we have very similar issue on the environment of our client APEX ver. 5.0.4.00.12. Would it be possible to release the patch also for that version, in case there is no workaround?
Waiting for your answer.

@andreml Thank you for your additional tests.

Greetings,
Maciek
Like Show 0 Likes(0)

Actions
4. Re: [BUG] IR filtering on numerical columns gives ORA-06502

Pavel_p Jun 12, 2018 11:07 AM (in response to kempiak)

Hi Maciek,
this looks like a serious bug in 18.1 IR and hopefully someone from the dev team Patrick Wolf-Oracle, Carsten Czarski-Oracle, Christian Neumueller-Oracle will take a look at it.
Regards,
Pavel
Like Show 0 Likes(0)

Actions
5. Re: [BUG] IR filtering on numerical columns gives ORA-06502

Carsten Czarski-Oracle Jun 12, 2018 1:45 PM (in response to Pavel_p)

Hi Maciek,
hi Pavel,

thank you for reporting this. I can reproduce the issue and have filed bug number 28171466 for this issue. We will be working on preparing a fix for this.

Unfortunately I don't have another workaround for you except the one which was already mentioned above (setting the NLS_DECIMAL_CHARACTERS value to ".").

Best regards

-Carsten
Like Show 1 Likes(1)

Actions
6. Re: [BUG] IR filtering on numerical columns gives ORA-06502

Pavel_p Jun 12, 2018 1:54 PM (in response to Carsten Czarski-Oracle)

Hello Carsten,
thank you very much for taking care of it. I think that the German community (along with Polish, Czech...and many others) will be highly grateful.
Best regards,
Pavel
Like Show 0 Likes(0)

Actions
7. Re: [BUG] IR filtering on numerical columns gives ORA-06502

kempiak Jun 12, 2018 2:37 PM (in response to Carsten Czarski-Oracle)

Hi Carsten,

First of all thank you very much for your reply.

I just wonder if the fix you've mentioned could also cover APEX ver. 5.0.4.00.12? We have this same bug on the mentioned environment and for the moment we cannot upgrade to the newer version.

Greetings,
Maciej
Like Show 0 Likes(0)

Actions
8. Re: [BUG] IR filtering on numerical columns gives ORA-06502

Carsten Czarski-Oracle Jun 21, 2018 11:20 AM (in response to kempiak)

Hi Maciej,

I just checked on a 5.0 instance, and here the issue seems to be not that severe. You cannot create a filter by using the column header, but it simply does not create the filter; there are no error messages. Using the Actions Menu > Filter, it is still possible to create the filter.

The fix for 18.1 is done on development - we hope to make it available as a PSE via MyOracleSupport shortly.

Best regards

-Carsten
Like Show 0 Likes(0)

Actions
9. Re: [BUG] IR filtering on numerical columns gives ORA-06502

Carsten Czarski-Oracle Jun 29, 2018 1:42 PM (in response to Carsten Czarski-Oracle)

Hi Maciej,
hi Pavel,

a fix for this is available now. Log into My Oracle Support and search for patch number 28128115.

Best regards

-Carsten
Like Show 2 Likes(2)

Actions
10. Re: [BUG] IR filtering on numerical columns gives ORA-06502

kempiak Jul 5, 2018 6:54 AM (in response to Carsten Czarski-Oracle)

Hi Carsten,

Thank you very much for your answer and quick fix on 18.1.

Concerning 5.0 (example as before: Application primary language -> "German" and "Application language" -> "Derived from application primary language") indeed there is no error thrown when I apply the filter using the column header, except that it is not working (filter is not applied).

Unfortunately when I go to Actions -> Filter, choose proposed value from the list and click "Apply" I receive short message "Invalid ..." visible on the attached screen.

When I modify the decimal separator to comma it starts to work. It looks like the problem comes from the fact that the proposed lists of values on the "Expression" field and column header are always propagated with a dot as a decimal separator...

We still have many applications running on 5.0.4.00.12 without the possibility of upgrading in the near future. That is why I would be really grateful if this could be fixed on APEX. J

Many thanks.

Greetings,
Maciej
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.