You Asked

Hello,
We have a Java process that calls OR stored procedure via JPA.
This procedure was initially declared outside of a package, and everything worked fine, but when we moved it into a package, we encountered date conversion problems related to the incorrectly initialized Oracle NLS_DATE_FORMAT variable.

When our call is made via a Windows SQL client, NLS_DATE_FORMAT has the value 'DD/MM/YYYY'.

However, when the call is made via Java Process, this variable has the value 'DD-MON-RR' => This causes problems in the procedure where date conversions are sometimes used without specifying the format, and Oracle then uses the NLS_DATE_FORMAT variable. This can lead to a TO_DATE(27/02/2026) operation when the session format is 'DD-MON-RR'... and therefore results in the Oracle error -1843 "not a valid month".

When the stored procedure was outside of a package, we forced this format at the beginning of our procedure with the command:
execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT=''dd/mm/yyyy''';

But this doesn't work when the procedure is inside a package because it is apparently compiled and cached before the ALTER SESSION operation is performed...

The code is below:

CREATE OR REPLACE PACKAGE GRASE_WSDATEBUTOIR_PG is
/*
   Traitement effectue par le package :
        Appel procedure de recuperation date butoire pour ws sme avantage
--*/
--
-- C O N S T A N T E S    P U B L I Q U E S
-- ============================================================================
   P_CST_module        CONSTANT STD_desc_PG.P_ST_module       DEFAULT 'GRASE_WSDATEBUTOIR_PG';
   P_CST_fonction_adm  CONSTANT STD_desc_PG.P_ST_fonction_adm DEFAULT 'SERVICE';
   P_CST_version       CONSTANT STD_desc_PG.P_ST_version      DEFAULT '26.2.0.01';
   P_CST_version_date  CONSTANT DATE                          DEFAULT '23/02/2026';
-- ============================================================================

--+Nom         : proc_ws_datebutoir_pr
--+traitement  : dans le cadre du ws sme avantage (creation montant)
--+Parametres  : I_idpersonn    IN  co.graseuvp_pg.P_ST_identifiant_personne,
--         I_dtjour       IN  DATE,
--         I_dttraitement IN  DATE,
--+Description : recupere la date butoire necessaire a  la creation occ rrx1tmtavtg
-- ****************************************************************************
PROCEDURE ws_datebutoir_pr
(
    I_idpersonn    IN  co.graseuvp_pg.P_ST_identifiant_personne,
    I_cdsynretveu  IN  co.graseuvp_pg.P_ST_code_synthese,
    I_termapayer   IN  co.graseuvp_pg.P_ST_code_terme_a_payer,

    O_dtbutoir     OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_gen OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_pts OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_cg  OUT co.graseuvp_pg.P_ST_date_butoire,

    O_code_retour  OUT RRX1TERRPGM.coderetour%TYPE,
    O_module       OUT RRX1TERRPGM.module%TYPE,
    O_errproc      OUT RRX1TERRPGM.errproc%TYPE,
    O_sqltrace     OUT RRX1TERRPGM.sqltrace%TYPE,
    O_sqlcode      OUT RRX1TERRPGM.sqlcode%TYPE,
    O_message      OUT RRX1TERRPGM.message%TYPE
);
-- 

END GRASE_WSDATEBUTOIR_PG;
/


CREATE OR REPLACE PACKAGE BODY GRASE_WSDATEBUTOIR_PG is
--      cartouche de package
--      partie BODY
-- ==================================================================
/*
--
   Nom du package                  : GRASE_WSDATEBUTOIR_PG

--*/

TYPE G_TR_parametres IS RECORD
        (cdappel        STD_desc_PG.P_ST_cdappel,
        idpersonn       RRX1TPERSONN.idpersonn%TYPE,
        date_jour   DATE,
        envir_ori       STD_desc_PG.P_ST_envir_ori,
        appli_ori       STD_desc_PG.P_ST_appli_ori,
        iddemande   RRX1TSERVPAI.iddemande%TYPE,
        dteffrev        DATE,
        dtderevt        DATE,
        ibvalutl    RRX1TREVIS.ibvalutl%TYPE
        );
-- ============================================================================
-- C U R S E U R S  V A R I A B L E S P U B L I C S
-- ============================================================================
 G_W_niv_debug_module   STD_desc_PG.P_ST_niveau_debug DEFAULT NULL;
 G_R_parametres     G_TR_parametres;
 G_R_erreur         STD_desc_PG.P_TR_erreur;

 G_CST_fonction_adm     CONSTANT      STD_desc_PG.P_ST_fonction_adm    DEFAULT 'SERVICE';
 G_CST_trt              CONSTANT      STD_desc_PG.P_ST_trt          DEFAULT 'rolexri';
-- ============================================================================
-- P R O C E D U R E S   E T   F O N C T I O N S   P U B L I Q U E S
-- ============================================================================
--
PROCEDURE trace_PR
( I_sqltrace                 IN     STD_desc_PG.P_ST_sqltrace
, I_message                  IN     STD_desc_PG.P_ST_message
, I_boolean                  IN     BOOLEAN                   DEFAULT NULL  );

--+****************************************************************************
--+Nom         : proc_ws_datebutoir_pr
--+traitement  : dans le cadre du ws sme avantage (creat montant)
--+Parametres  : I_idpersonn    IN  co.graseuvp_pg.P_ST_identifiant_personne,
--      I_dtjour       IN  DATE,
--      I_dttraitement IN  DATE,
--+Description : recupere la date butoire necessaire a  la creation occ rrx1tmtavtg
-- ****************************************************************************
PROCEDURE ws_datebutoir_pr
(
    I_idpersonn    IN  co.graseuvp_pg.P_ST_identifiant_personne,
    I_cdsynretveu  IN  co.graseuvp_pg.P_ST_code_synthese,
    I_termapayer   IN  co.graseuvp_pg.P_ST_code_terme_a_payer,
    O_dtbutoir     OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_gen OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_pts OUT co.graseuvp_pg.P_ST_date_butoire,
    O_dtbutoir_cg  OUT co.graseuvp_pg.P_ST_date_butoire,
    O_code_retour  OUT RRX1TERRPGM.coderetour%TYPE,
    O_module       OUT RRX1TERRPGM.module%TYPE,
    O_errproc      OUT RRX1TERRPGM.errproc%TYPE,
    O_sqltrace     OUT RRX1TERRPGM.sqltrace%TYPE,
    O_sqlcode      OUT RRX1TERRPGM.sqlcode%TYPE,
    O_message      OUT RRX1TERRPGM.message%TYPE
)

IS
    -- constantes
    L_CST_errproc   VARCHAR2(255)                DEFAULT 'proc_ws_datebutoir';
    L_CST_sqltrace  VARCHAR2(255)                DEFAULT 'P000010';
    L_CST_module    CONSTANT STD_desc_PG.P_ST_module := P_CST_module;

    -- variables
    L_R_erreur      admlr3.STD_desc_PG.P_TR_erreur;
    L_R_dtbutoir    co.GPBEP_pubulpc_PG.P_TR_dtbutoir;

    -- exception
    L_E_ulpc        EXCEPTION;

    -- utilitaire : date du jour service (avec fallback sysdate)
    L_date_jour     DATE;
BEGIN

 O_code_retour :=6;

 trace_PR(L_CST_sqltrace,L_CST_errproc);
 trace_PR('V010213','I_idpersonn:'||to_char(I_idpersonn ));
 trace_PR('V010214','I_cdsynretveu:'||to_char(I_cdsynretveu));
 trace_PR('V010215','I_termapayer:'||to_char(I_termapayer));

    -- Initialisation OUT (sécurité)
    O_dtbutoir      := NULL;
    O_dtbutoir_gen  := NULL;
    O_dtbutoir_pts  := NULL;
    O_dtbutoir_cg   := NULL;

    O_code_retour   := admlr3.STD_desc_PG.P_CST_normal;
    O_module        := L_CST_module;
    O_errproc       := L_CST_errproc;
    O_sqltrace      := L_CST_sqltrace;
    O_sqlcode       := 0;
    O_message       := NULL;

 trace_PR('V010215','co.graseuvp_pg.P_R_service.date_jour:'||to_char(co.graseuvp_pg.P_R_service.date_jour));


    -- Date service (si le package P_R_service fournit date_jour)
    L_date_jour := to_date(SYSDATE,'dd/mm/yyyy');

    --traçage
      trace_PR('V010216', 'L_date_jour=' || TO_CHAR(L_date_jour) );


    ----------------------------------------------------------------------
    -- 1) Date butoire des contrôles globalisés
    ----------------------------------------------------------------------
 O_dtbutoir_cg := co.GPBEP_maj_controleglobalise_PG.recup_dtbutoircg_FN(L_R_erreur);
    --traçage
      trace_PR('V010217', 'O_dtbutoir_cg=' || TO_CHAR(O_dtbutoir_cg) );
    dbms_output.put_line('O_dtbutoir_cg=' || TO_CHAR(O_dtbutoir_cg));

    ----------------------------------------------------------------------
    -- 2) Date butoire générale
    ----------------------------------------------------------------------
    co.GPBEP_pubulpc_PG.recup_dtbutoir_PR(L_R_dtbutoir, L_R_erreur);

    IF L_R_erreur.code_retour > admlr3.STD_desc_PG.P_CST_normal THEN
        RAISE L_E_ulpc;
    END IF;

    IF I_termapayer = co.graseuvp_pg.P_CST_termapayer_a_echoir THEN
        O_dtbutoir_gen := L_R_dtbutoir.strasbourg;
    ELSE
        O_dtbutoir_gen := L_R_dtbutoir.prestation;
    END IF;
      --traçage
      trace_PR('V010218', 'O_dtbutoir_gen=' || TO_CHAR(O_dtbutoir_gen) );
      dbms_output.put_line('O_dtbutoir_gen=' || TO_CHAR(O_dtbutoir_gen));

    ----------------------------------------------------------------------
    -- 3) Dernière mensualité exigible
    ----------------------------------------------------------------------
    O_dtbutoir_pts := co.GPBEP_pubulpc_PG.recup_dermexi_FN(I_idpersonn, L_R_erreur);
      --traçage
      trace_PR('V010219', 'O_dtbutoir_pts=' || TO_CHAR(O_dtbutoir_pts) );

    IF L_R_erreur.code_retour > admlr3.STD_desc_PG.P_CST_normal THEN
        RAISE L_E_ulpc;
    END IF;
    dbms_output.put_line('O_dtbutoir_pts=' || TO_CHAR(O_dtbutoir_pts));

    ----------------------------------------------------------------------
    -- 4) Date butoire retenue (fin de mois sur pts, sinon fin de mois date_jour)
    ----------------------------------------------------------------------
    O_dtbutoir :=
        CASE
            WHEN O_dtbutoir_pts IS NOT NULL THEN LAST_DAY(O_dtbutoir_pts)
            ELSE LAST_DAY(L_date_jour)
        END;
      --traçage
      trace_PR('V010220', 'O_dtbutoir=' || TO_CHAR(O_dtbutoir) );
      dbms_output.put_line('O_dtbutoir=' || TO_CHAR(O_dtbutoir));

EXCEPTION
    WHEN L_E_ulpc THEN
        -- Renseignement depuis L_R_erreur
        O_module      := L_R_erreur.module;
        O_errproc     := L_R_erreur.errproc;
        O_sqltrace    := L_R_erreur.sqltrace;
        O_sqlcode     := L_R_erreur.sqlcode;
        O_code_retour := L_R_erreur.code_retour;

        --traçage
        trace_PR('V010221', 'O_code_retour=' || O_code_retour );
        trace_PR('V010223', 'O_module=' || O_module );
        trace_PR('V010224', 'O_errproc=' || O_errproc );
        trace_PR('V010225', 'O_sqlcode=' || O_sqlcode );
        trace_PR('V010226', 'O_sqltrace=' || O_sqltrace );

        -- Sécuriser les dates OUT
        IF O_dtbutoir IS NULL THEN
            O_dtbutoir := LAST_DAY(L_date_jour);
        END IF;
        --traçage
        trace_PR('V010227', 'O_dtbutoir=' || TO_CHAR(O_dtbutoir) );


    WHEN OTHERS THEN
        O_module      := L_CST_module;
        O_errproc     := L_CST_errproc;
        O_sqltrace    := L_CST_sqltrace;
        O_sqlcode     := SQLCODE;
        O_code_retour := admlr3.STD_desc_PG.P_CST_arret;

        --traçage
        trace_PR('V010221', 'O_code_retour=' || O_code_retour );
        trace_PR('V010223', 'O_module=' || O_module );
        trace_PR('V010224', 'O_errproc=' || O_errproc );
        trace_PR('V010225', 'O_sqlcode=' || O_sqlcode );
        trace_PR('V010226', 'O_sqltrace=' || O_sqltrace );

        -- Sécuriser les dates OUT
        IF O_dtbutoir IS NULL THEN
            O_dtbutoir := LAST_DAY(L_date_jour);
        END IF;
        --traçage
        trace_PR('V010227', 'O_dtbutoir=' || TO_CHAR(O_dtbutoir) );

end ws_datebutoir_pr;

END GRASE_WSDATEBUTOIR_PG;
/

Below is the Java code that calls the procedure :

package cnav.retraite.droit.wdt.procstockeesor.sme.jpa.service.core;

@ApplicationScoped
public class DateButoirSEBean {

  private final Logger logger = LoggerFactory.getLogger(DateButoirSEBean.class);

  private static final String NOM_PROCEDURE_STOCKEE = "CO.ws_datebutoir_pr";

  @Inject
  @Named("orObjectStore")
  private ObjectStore objectStore;

  /**
   * Nom de la PROCEDURE : determinedatebutoir_ws <br>
   * Paramètre d'entrée : I_idpersonn IN NUMBER, I_cdsynretveu IN NUMBER , I_termapayer IN VARCHAR2
   * <br>
   * Paramètres de sortie : O_dtbutoir OUT DATE, O_dtbutoir_gen OUT DATE, O_dtbutoir_pts OUT DATE,
   * O_dtbutoir_cg OUT DATE, O_code_retour OUT NUMBER, O_module OUT VARCHAR2, O_errproc OUT
   * VARCHAR2, O_sqltrace OUT VARCHAR2, O_sqlcode OUT NUMBER , O_message OUT VARCHAR2
   *
   * @param idConv
   * @param idPersonn
   * @param codeSynthSituRetVeu
   * @param termAPayer
   * @param mediationInfo
   * @return
   */
  @Interceptors(SRSeMultibaseProviderInterceptor.class)
  @SRSeMultibaseManagedCall(rule = REGLE_MULTIBASE)
  public DtbutoirResultEntity appelProcedureStockeeDetermineDtbutoirPaiementPR(
      String idConv,
      Long idPersonn,
      Integer codeSynthSituRetVeu,
      String termAPayer,
      SRCommonMultibaseMediationInfo mediationInfo) {

    try {
      StoredProcedureQuery query =
          objectStore.getEm().createStoredProcedureQuery(NOM_PROCEDURE_STOCKEE);

      logger.debug("[{}] création de la query : {}", idConv, query);

      // Déclaration des paramètres
      query
          .registerStoredProcedureParameter("I_idpersonn", Long.class, ParameterMode.IN)
          .registerStoredProcedureParameter("I_cdsynretveu", Integer.class, ParameterMode.IN)
          .registerStoredProcedureParameter("I_termapayer", String.class, ParameterMode.IN)
          .registerStoredProcedureParameter("O_dtbutoir", Date.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_dtbutoir_gen", Date.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_dtbutoir_pts", Date.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_dtbutoir_cg", Date.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_code_retour", Integer.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_module", String.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_errproc", String.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_sqltrace", String.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_sqlcode", Integer.class, ParameterMode.OUT)
          .registerStoredProcedureParameter("O_message", String.class, ParameterMode.OUT);

      logger.debug("[{}] enregistrement de paramètres IN/OUT dans la query :  {}", idConv, query);

      // Passage des valeurs
      query.setParameter("I_idpersonn", idPersonn);
      query.setParameter("I_cdsynretveu", codeSynthSituRetVeu);
      ((ProcedureParameter) query.getParameter("I_termapayer")).enablePassingNulls(true);
      query.setParameter("I_termapayer", termAPayer);
      logger.debug(
          "[{}] ajout des paramètres d'appel dans la query [I_idpersonn : {} ;  I_cdsynretveu : {} ;  I_termapayer : {}]",
          idConv,
          query.getParameterValue("I_idpersonn"),
          query.getParameterValue("I_cdsynretveu"),
          query.getParameterValue("I_termapayer"));

      // Exécution
      query.execute();

      // Récupération du paramètre OUT
      Date dateButoir = (Date) query.getOutputParameterValue("O_dtbutoir");
      Date dateButoirGen = (Date) query.getOutputParameterValue("O_dtbutoir_gen");
      Date dateButoirPts = (Date) query.getOutputParameterValue("O_dtbutoir_pts");
      Date dateButoirCg = (Date) query.getOutputParameterValue("O_dtbutoir_cg");
      Integer codeRetour = (Integer) query.getOutputParameterValue("O_code_retour");
      String module = (String) query.getOutputParameterValue("O_module");
      String errProc = (String) query.getOutputParameterValue("O_errproc");
      String sqlTrace = (String) query.getOutputParameterValue("O_sqltrace");
      Integer sqlCode = (Integer) query.getOutputParameterValue("O_sqlcode");
      String message = (String) query.getOutputParameterValue("O_message");
      logger.debug("CodeRetour : {}", codeRetour);

      // Log du résultat
      logger.info(
          "[{}] Résultat de l'appel de la procedure '"
              + NOM_PROCEDURE_STOCKEE
              + "' -> [dateButoir: '{}', dateButoirGen: '{}', dateButoirPts: '{}', "
              + "dateButoirCg: '{}', codeRetour: '{}']",
          idConv,
          dateButoir,
          dateButoirGen,
          dateButoirPts,
          dateButoirCg,
          codeRetour);

      if (codeRetour == null || codeRetour != 0) {
        logger.debug(
            "[{}] Erreur lors de l'appel de la procedure '"
                + NOM_PROCEDURE_STOCKEE
                + "' -> [codeRetour: '{}', module: '{}', errProc: '{}', sqlTrace: '{}', "
                + "sqlCode: '{}', message: '{}']",
            idConv,
            codeRetour,
            module,
            errProc,
            sqlTrace,
            sqlCode,
            message);

        throw new RuntimeException(
            String.format(
                "[%s] Erreur lors de l'appel de la procédure '"
                    + NOM_PROCEDURE_STOCKEE
                    + "' : %d - %s",
                idConv,
                sqlCode,
                message));
      }

      return new DtbutoirResultEntity(
          idPersonn, dateButoir, dateButoirGen, dateButoirPts, dateButoirCg);

    } catch (Throwable e) {
      logger.error(" [{}] Erreur : {}  - ", idConv, e.getMessage(), e);
      throw new DateButoirException(e.getMessage(), e);
    }
  }
}

Thank you in advance for your help.
Sincerely,
Nordine

and Chris said...

It makes no difference whether a procedure is stand-alone or in a package. Data type conversions will use the current NLS settings. You can change them within a procedure (see demo at the bottom), but it's much better to explicitly specify the format masks in the conversion functions.

That said, I believe the problem lies here:

L_date_jour := to_date(SYSDATE,'dd/mm/yyyy');

SYSDATE is already a DATE!

So this really becomes:

L_date_jour := to_date ( to_char ( SYSDATE ), 'dd/mm/yyyy' );

Don't do this! It leads to the conversion errors you see.

If your goal here is "get the start of the day", then use TRUNC or other datetime functions to do this, e.g.:

L_date_jour := trunc ( SYSDATE );

Here's an example to show that packaged procedures respect the current NLS settings by changing the NLS_DATE_FORMAT within the procedure:

create or replace package pkg as 
  procedure p;
end;
/
create or replace package body pkg as 
  procedure p is 
  begin
    execute immediate q'[alter session set nls_date_format = 'dd-mon-yyyy']';
    dbms_output.put_line ( sysdate );
    execute immediate q'[alter session set nls_date_format = 'yyyy/mm/dd']';
    dbms_output.put_line ( sysdate );
  end;
end;
/

exec pkg.p();
04-mar-2026
2026/03/04

Rating

(1 rating)

Comments

request for further clarification

A reader, March 04, 2026 - 3:34 pm UTC

Thank you for your quick analysis. I've noted the problem you reported regarding the use of the `to_date` function with a date. However, there's something I don't understand: why does the procedure execute without error when called from a Windows SQL client, regardless of whether it's encapsulated in a package or not? Conversely, if called from a Java program, it only fails when the procedure is encapsulated in a package. Sorry to insist on this point—do you have any further information you could provide?

Sincerely,
Nordine

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

request for further clarification

More to Explore

PL/SQL demos

PL/SQL docs