Oracle procedure passes different result when calling in oracle form and the same in sql developer

Question

I tried calling a procedure with 3 input parameters and 3 out parameters in both Oracle forms .pll file as well as in sql developer. Even I expected the same output parameter values for both cases, it gives different results. I confirmed printing a message on .pll and they are exactly same as I used in sql developer. Can anybody explain what the cause for such discrepancy is. Thanks.

In .pll

p_enquiry_date       pp_acc.end_date%type := to_date((nvl(name_in('PPUN.DSP_RUN_END_DATE'),
                                                         to_char(sysdate, 'DD-MON-YYYY'))),'DD-MON-YYYY');

message('enq: '||p_enquiry_date);pause;

It prints 'enq: 11-JAN-23'

In Sql Developer,

p_enquiry_date       pp_acc.end_date%type := to_date('11-JAN-2023','DD-MON-YYYY');

Answer 1

From my point of view, everything is OK. It is not about date value itself, but the way it is presented to you.

Both Forms and SQL Developer show these date values using format mask valid at that moment.

In SQL*Plus (easier to demonstrate), we set it by

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
17.10.2023

If I change my mind, I can

SQL> alter session set nls_date_format = 'dd-MON-yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------
17-OCT-2023

Or, to display time component as well:

SQL> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
10/17/2023 07:02:13

SQL>

sysdate is the same in all cases, it is just about the way you see it.

The same goes with Forms; its date format is set to dd-MON-yy or dd-MON-rr so you see it as 11-OCT-23, but it is as valid as any other format.

If you have to do some date arithmetic with that variable, no problem - you can, and all values will be OK as you're dealing with date datatype.

You could modify the way you see that value - the simplest way is to apply to_char with desired format mask, e.g.

SQL> select to_char(sysdate, 'yyyy-MON-dd') from dual;

TO_CHAR(SYSDATE,'YYY
--------------------
2023-OCT-17

but this is now a string (and so is e.g. 'ABC' or 'Littlefoot' etc.) so - for date arithmetic operations - you'd now have to either convert it back to date (using to_date, again with appropriate format mask that matches that string) or - worse - rely on Oracle's implicit datatype conversion capabilities, and that's simple wrong. Imagine you see this value: 02/04/08. What is it?

• 2nd of April 2008
• 4th of August 2002
• 8th of February 2004

Could be any of these values - that's why format model matters.

Once again: as long as you work with date datatype, you're fine. Don't go to the dark side and work with characters (representing date values).

Answer 2

Given the setup:

CREATE TABLE pp_acc(end_date DATE);

CREATE PROCEDURE message(v_msg VARCHAR2)
  IS BEGIN DBMS_OUTPUT.PUT_LINE(v_msg); END;
/

CREATE PROCEDURE pause
  IS BEGIN NULL; END;
/

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Then using:

DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN DATE
    IS BEGIN RETURN SYSDATE; END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := to_date(
                       (
                         nvl(
                           name_in('PPUN.DSP_RUN_END_DATE'),
                           to_char(sysdate, 'DD-MON-YYYY')
                         )
                       ),
                       'DD-MON-YYYY'
                     );
  message('enq: '||p_enquiry_date);
  pause;
END;
/

Fails with the exception:

ORA-01861: literal does not match format string
ORA-06512: at line 9

Because my NLS_DATE_FORMAT is set to YYYY-MM-DD HH24:MI:SS and the NAME_IN function will perform an implicit type conversion from date-to-string and output the first parameter in the format YYYY-MM-DD HH24:MI:SS when TO_DATE expects the format DD-MON-YYYY and you are mixing implicit and explicit formatting of dates (i.e. NAME_IN converts date-to-string implicitly providing the format and then you convert back to a date using an explicit format model).

If we change to explicitly use the correct format:

DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
    IS BEGIN RETURN TO_CHAR(SYSDATE, 'DD-MON-YYYY'); END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := to_date(
                       (
                         nvl(
                           name_in('PPUN.DSP_RUN_END_DATE'),
                           to_char(sysdate, 'DD-MON-YYYY')
                         )
                       ),
                       'DD-MON-YYYY'
                     );
  message('enq: '||p_enquiry_date);
  pause;
END;
/

Then the output is:

enq: 2023-10-17 00:00:00

The statement work but the output is probably not in the format you are expecting. This is because 'enq: '||p_enquiry_date is performing an implicit date-to-string conversion and is effectively evaluated as:

'enq' || TO_CHAR(
  p_enquiry_date,
  (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
)

You could change the NLS_DATE_FORMAT session parameter to DD-MON-YYYY but don't (any user can change their NLS session parameters at any time so you should not rely on implicit casts).

Instead, use consistent data-types throughout:

If NAME_IN is a built-in Oracle forms function that returns a VARCHAR2 data type then either:

1. Use VARCHAR2 throughout. This is an exceptional case where a built-in function breaks the rule about not using implicit type-conversions so we can apply some common sense and, in this case, it is easier to rely on implicit date formatting throughout rather than mixing implicit and explicit date formats (general rules are good, but sometimes they get broken and you need to know which bits of them to uphold, in this case its "not mixing implicit and explicit date formats" rather than the more general "don't use implicit type conversions"):

   DECLARE
     p_enquiry_date VARCHAR2(50);
   
     FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
       IS BEGIN RETURN SYSDATE; END;
   BEGIN
     DBMS_OUTPUT.ENABLE();
   
     p_enquiry_date  := nvl(name_in('PPUN.DSP_RUN_END_DATE'), to_char(sysdate));
     message('enq: '||p_enquiry_date);
     pause;
   END;
   /
   

   Which outputs formatted as the NLS_DATE_FORMAT (so if yours is set to DD-MON-YYYY then it will output in that format):

   enq: 2023-10-17 09:15:34
   

   or else;

2. Use DATEs and convert NAME_IN back to a date and apply the appropriate formatting when passing to message:

   DECLARE
     p_enquiry_date pp_acc.end_date%type;
   
     FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
       IS BEGIN RETURN SYSDATE; END;
   BEGIN
     DBMS_OUTPUT.ENABLE();
   
     p_enquiry_date  := nvl(TO_DATE(name_in('PPUN.DSP_RUN_END_DATE')), sysdate);
     message('enq: '||TO_CHAR(p_enquiry_date, 'DD-MON-YYYY'));
     pause;
   END;
   /
   

   Which outputs:

   enq: 17-OCT-2023
   

   Alternatively, but not recommended:

3. Run:

   ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
   

   Before running your PL/SQL block.

fiddle