Getting DATE value from oracle forms using USER EXIT

Question

I'm trying to user EXEC TOOLS GET to retrieve a date field from ORACLE FORMS into a char variable like this :

EXEC TOOLS GET filter.generation_depuis INTO :qstrGenerationDepuis;

When I try to display the variable qstrGenerationDepuis I get this value :

27-JANV.-2 

Instead of :

27/01/2020

Knowing that qstrGenerationDepuis variable is declared as string of 10 characters.

How should I retrieve the field in the user-exit to get the right date format ?

The extension of the user-exit file is .pcs

Thanks a lot in advance.

Answer 1

How about two-step procedure?

First get the value:

EXEC TOOLS GET filter.generation_depuis INTO :qstrGenerationDepuis;

which sets the item to 27-JANV.-2

Then convert it to desired format, but first you have to know which format you got. This is French, isn't it? Looks like 27th of January of ... which year? What is -2? It can be any year between 2020 and 2029 because variable is 10 characters long:

SQL> select to_char(date '2020-01-27', 'dd-MON-yy', 'nls_date_language = french') datum from dual;

DATUM
-----------
27-JANV.-20
         ^ tenth character is "2"; "0" is lost
12345678901

If we presume that it is the current year, then you might try something like this:

• convert a string 27-JANV.-2 into a date by adding current year's last digit (that's what substr does)

• apply to_char to a new date value, 27-JANV.-20, with desired format mask

  SQL> select to_char(to_date('27-JANV.-2' || substr(to_char(sysdate, 'yyyy'), -1),
    2                         'dd-mon-rr',
    3                         'nls_date_language = french'),
    4                 'dd/mm/yyyy') result
    5  from dual;
  
  RESULT
  ----------
  27/01/2020
  

Not that it is impossible (obviously), but - you'd rather

• return desired format mask from filter.generation_depuis
• or, if that's not possible, enlarge item/variable length to at least 11 characters because you'll lose the last digit

Note that you probably don't have to use nls_date_language parameter; I do, as my database runs in Croatian and our format is quite different than yours.