2 Replies Latest reply on Dec 22, 2016 1:34 PM by 3319176

    [4.2EA] set null issues in script output

    3319176

      I'm using SQL Developer startut script option to set the following option :

      SET NULL '(null)'
      

       

      Below is the result when executing several queries in script output (just a few seconds between each) :

       

      SQL> SELECT * FROM LGN_INV WHERE ID_INV = 5151 AND USER1 = 0;
      ID_LGN_INV  ID_INV  ID_E_LGN_INV  ID_T_LGN_INV  ID_EMPL  ID_POOL_INV1  ID_POOL_INV2  ID_POOL_INV3  ID_SOC  CODE_SOC  ID_SUP  NO_SUP              ID_FAM_SUP  CODE_FAM_SUP  ID_CARTON  NO_CARTON  ID_ART  CODE_ART      ID_VARIANTE  CODE_VARIANTE  ID_LOT  NO_LOT  DATE_DLV  NO_COMPLEMENT  QTE_ATT1  QTE_1   USER1  QTE_ATT2  QTE_2   USER2  QTE_ATT3  QTE_3   USER3  QTE_FORCEE  USER_FORCEE  ECART   ORDRE  ID_MOTIF  COMMT_LGN  RM_DEB_LGN_INV  DATE_DEB_LGN_INV  RM_FIN_LGN_INV  DATE_FIN_LGN_INV  RM_VALID_LGN_INV  DATE_VALID_LGN_INV  NUMBER1  NUMBER2  NUMBER3  DATE1   DATE2   DATE3   TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH  DM                             RM  EM    ID_POOL_INV3_PREC  QTE_ATT3_PREC  QTE_3_PREC  USER3_PREC  
      74613       5151    10            1             46409    5808          (null)        (null)        1       RIVOLIER  198717  030000000605077947  2           Ind�fini      0          (null)     18056   HADYNAMIC71   0            (null)         -1      (null)  (null)    (null)         (null)    (null)  0      (null)    (null)  0      (null)    (null)  0      (null)      0            (null)  482    (null)    (null)     (null)          (null)            (null)          (null)            (null)            (null)              (null)   (null)   (null)   (null)  (null)  (null)  (null)  (null)  (null)  07/12/2016 12:09:54,000000000  (null)     07/12/2016 12:17:10,000000000  30  2056  (null)             (null)         (null)      0           
      74614       5151    10            1             46409    5808          (null)        (null)        1       RIVOLIER  198717  030000000605077947  2           Ind�fini      0          (null)     25263   HADYNAMICMAX  0            (null)         -1      (null)  (null)    (null)         (null)    (null)  0      (null)    (null)  0      (null)    (null)  0      (null)      0            (null)  482    (null)    (null)     (null)          (null)            (null)          (null)            (null)            (null)              (null)   (null)   (null)   (null)  (null)  (null)  (null)  (null)  (null)  07/12/2016 12:09:54,000000000  (null)     07/12/2016 12:17:10,000000000  30  2056  (null)             (null)         (null)      0           
      
      
      SQL> SELECT * FROM SUP_CONTIENT_ART WHERE ID_ART = 18056 AND ID_SUP = 198717;
      ID_SCA  ID_CARTON  ID_SUP  ID_ART  ID_LOT  ID_BLOC  ID_RESERVATION  QTE_SCA  QTE_RESERVEE  ID_RECEP  DATE_RECEP                     DATE_FIFO                      DATE_LIMITE  DATE_DLV  ETAT_STOCK  NB_CARTON  ID_VARIANTE  INFO_STOCK  NB_INV  DATE_DERNIER_INV               NUMBER1  NUMBER2  NUMBER3  DATE1  DATE2  DATE3  TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DM                             RM  EM   
      137692  0          198717  18056   -1      2209     0               3        0             0         18/05/2016 00:00:00,000000000  18/05/2016 00:00:00,000000000                         0           0          0                        3       06/12/2016 10:47:31,152212000                                                                          09/06/2016 09:13:41,000000000  06/12/2016 10:47:31,000000000  1   -14  
      219092  0          198717  18056   -1      0        0               1        0             0         06/12/2016 00:00:00,000000000  06/12/2016 00:00:00,000000000                         0           0          0                        0                                                                                                              06/12/2016 10:47:30,000000000  06/12/2016 10:47:30,000000000  1   -14  
      
      
      SQL> SELECT * FROM LGN_INV WHERE ID_INV = 5151 AND ID_ART = 18056;
      ID_LGN_INV  ID_INV  ID_E_LGN_INV  ID_T_LGN_INV  ID_EMPL  ID_POOL_INV1  ID_POOL_INV2  ID_POOL_INV3  ID_SOC  CODE_SOC  ID_SUP  NO_SUP              ID_FAM_SUP  CODE_FAM_SUP  ID_CARTON  NO_CARTON  ID_ART  CODE_ART     ID_VARIANTE  CODE_VARIANTE  ID_LOT  NO_LOT  DATE_DLV  NO_COMPLEMENT  QTE_ATT1  QTE_1  USER1  QTE_ATT2  QTE_2  USER2  QTE_ATT3  QTE_3  USER3  QTE_FORCEE  USER_FORCEE  ECART  ORDRE  ID_MOTIF  COMMT_LGN  RM_DEB_LGN_INV  DATE_DEB_LGN_INV  RM_FIN_LGN_INV  DATE_FIN_LGN_INV  RM_VALID_LGN_INV  DATE_VALID_LGN_INV  NUMBER1  NUMBER2  NUMBER3  DATE1  DATE2  DATE3  TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH  DM                             RM  EM    ID_POOL_INV3_PREC  QTE_ATT3_PREC  QTE_3_PREC  USER3_PREC  
      74621       5151    60            2             46409    5808                                      1       RIVOLIER  198717  030000000605077947  2           Ind�fini      0                     18056   HADYNAMIC71  0            STD            -1                                       0         1      30                      0                       0                  0                   482                                                                                                                                                                                                           07/12/2016 12:11:24,000000000             07/12/2016 12:11:24,000000000  30  2056                                                0           
      74612       5151    60            1             46409    5808                                      1       RIVOLIER  198717  030000000605077947  2           Ind�fini      0                     18056   HADYNAMIC71  0                           -1                                       3         3      30                      0                       0                  0                   482                                                                                                                                                                                                           07/12/2016 12:09:54,000000000             07/12/2016 12:11:24,000000000  30  2056                                                0           
      74613       5151    10            1             46409    5808                                      1       RIVOLIER  198717  030000000605077947  2           Ind�fini      0                     18056   HADYNAMIC71  0                           -1                                                        0                       0                       0                  0                   482                                                                                                                                                                                                           07/12/2016 12:09:54,000000000             07/12/2016 12:17:10,000000000  30  2056                                                0           
      

       

      As you can see, on the first query, set null option was correctly taken into account, but on the next two queries, set null option was like not active and columns containing nulls are diplayed as blanks (like DATE_LIMITE, DATE_DLV, ID_POOL_INV2, ID_POOL_INV3,...)

        • 1. Re: [4.2EA] set null issues in script output
          B.Delmée

          Hello, this seems similar to a NLS-related problem which has been reported before and acknowledged as a bug.

          • 2. Re: [4.2EA] set null issues in script output
            3319176

            I can confirm this is still happening in 4.2EA2 :

             

            SQL> SELECT * FROM POOL WHERE ID_POOL = 152426;
            ID_POOL  ID_SITE  ID_E_POOL  ID_T_POOL  LIBELLE_POOL          ID_LANCEMENT  ID_PROF  ID_PROF_ECLAT  ID_CHARIOT  ID_RADIO  MODE_PREP  MODE_CHARIOT  ID_SECTEUR  INFO_POOL                                                                         DATE_DEB_RAMASSE               RM_DEB_RAMASSE  DATE_FIN_RAMASSE               RM_FIN_RAMASSE  DATE_DEB_VENTILE               RM_DEB_VENTILE  DATE_TERMINE                   RM_TERMINE  NUMBER1  NUMBER2  NUMBER3  DATE1   DATE2   DATE3   TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH                      DM                             RM  EM    
            152426   1        50         1          POOL_21851_15_03_BAS  21851         15       0              0           15        1          0             3           N� Cde : 10666534-1 (1387113 ILARIA) - Client : CORNER COIN NAPOLI - Transporteu  22/12/2016 10:08:58,000000000  11              22/12/2016 10:25:57,000000000  15              22/12/2016 10:25:57,000000000  15              22/12/2016 10:25:57,000000000  15          (null)   (null)   (null)   (null)  (null)  (null)  (null)  (null)  (null)  22/12/2016 06:02:14,000000000  22/12/2016 00:00:00,000000000  22/12/2016 10:25:57,000000000  15  2040  
            
            
            SQL> SELECT * FROM LGN_PRLV WHERE ID_POOL = 152426;
            ID_LGN_PRLV  ID_E_LGN_PRLV  ID_T_LGN_PRLV  ID_OP   ID_POOL  ID_FAM_COL  ID_MAG  ID_SECTEUR  ID_EMPL  ID_ART  ID_VARIANTE_REQ  ID_LOT_REQ  ID_SUP_REQ  CONTRAT_DATE  DATE_DLV_REQ  ID_MOTIF_BLOC  ID_MOTIF_RESA  ID_SCA  ID_SUP  ID_LOT  ID_CARTON  DATE_DLV  ID_BLOC  ID_RESERVATION  QTE_PRLV_DDEE  QTE_PRLV  ORDRE  ID_GROUPE_PRLV  ID_MVT  ID_VARIANTE  TOP_FACTURE  DATE_DEB_PRLV                  RM_DEB_PRLV  DATE_FIN_PRLV                  RM_FIN_PRLV  NUMBER1  NUMBER2  NUMBER3  DATE1  DATE2  DATE3  TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH                      DM                             RM  EM    
            905952       40             1              120935  152426   1           11      3           4362     6018    0                -1          0                                       0              0              920339  373359  -1      0                    0        0               298            298       1      0               0       0            0            22/12/2016 10:09:03,000000000  11           22/12/2016 10:25:56,000000000  15                                                                                   22/12/2016 05:59:27,000000000  22/12/2016 00:00:00,000000000  22/12/2016 10:25:57,000000000  15  -7    
            905953       10             1              120935  152426   1           11      3           4362     6018    0                -1          0                                       0              0              920339  373359  -1      0                    0        0               2              2         0      0               0       0            0            22/12/2016 10:09:01,000000000  11           22/12/2016 10:25:49,000000000  15                                                                                   22/12/2016 05:59:27,000000000  22/12/2016 00:00:00,000000000  22/12/2016 10:29:19,000000000  11  2040  
            
            
            SQL> SELECT * FROM REQ_DECSTOCK WHERE ID_LGN_PRLV = 905953;
            ID_DDE   ID_SITE  ID_E_REQ_DECSTOCK  ID_T_REQ_DECSTOCK  ID_LGN_PRLV  ID_COLIS  ID_OP   ID_MOTIF  COMMT   SESSION_ID  LIBELLE_ERREUR  DATE_DEB_TRAITE                DATE_FIN_TRAITE                NUMBER1  NUMBER2  NUMBER3  DATE1   DATE2   DATE3   TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH                      DM                             RM  EM  
            1292781  1        3                  1                  905953       635469    120935  0         (null)  239         (null)          22/12/2016 10:25:49,000000000  22/12/2016 10:25:49,000000000  (null)   (null)   (null)   (null)  (null)  (null)  (null)  (null)  (null)  22/12/2016 10:25:49,000000000  22/12/2016 00:00:00,000000000  22/12/2016 10:25:49,000000000  15  -7  
            
            
            SQL> SELECT * FROM SUP_CONTIENT_ART WHERE ID_SUP = 373359;
            
            aucune ligne sélectionnée
            SQL> SELECT * FROM COLIS WHERE NO_COLIS = '030000900606970806';
            ID_COLIS  ID_E_COL  ID_T_COL  ID_OP   ID_FAM_COL  ID_EMBAL  ID_LANCEMENT  NO_COLIS            M_COLIS  DP_M_COLIS  DM_M_COLIS  M_COLIS_REEL  V_COLIS  ID_EXPEDITION  NO_ETIQ_COLIS  NO_SUIVI      NO_TRANSPORT  NO_REGROUPEMENT  RDVNAME_COLIS  ID_SUP  ID_EMPL  FILIATION  TYPE_FILIATION  HOMOGENE  COMPLET  ORDRE  CONTENANT_CHARIOT  ID_E_PESAGE  ID_POINT_RDV  REM_CRC  TOP_FACTURE  DATE_DEB_PREP                  RM_DEB_PREP  DATE_FIN_PREP                  RM_FIN_PREP  DATE_CTR  RM_CTR  DATE_EXPEDITION  RM_EXPEDITION  DATE_LIVRAISON  RM_LIVRAISON  NUMBER1  NUMBER2  NUMBER3  DATE1  DATE2  DATE3  TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DATE_ARCH                      DM                             RM  EM   NO_SUIVI_TDI  
            635469    30        1         120935  1           20        21851         030000900606970806  7,35     0           0           8,1           288000   0              0              900606970806                                                448880  4                   0               0         0        0      0                                             0        0            22/12/2016 10:25:49,000000000  15           22/12/2016 10:25:57,000000000  15                                                                                                                                                                   22/12/2016 10:25:49,000000000  22/12/2016 00:00:00,000000000  22/12/2016 10:49:42,000000000  43  -13                
            
            
            SQL> SELECT * FROM SUP_CONTIENT_ART WHERE ID_SUP = 448880;
            ID_SCA  ID_CARTON  ID_SUP  ID_ART  ID_LOT  ID_BLOC  ID_RESERVATION  QTE_SCA  QTE_RESERVEE  ID_RECEP  DATE_RECEP                     DATE_FIFO                      DATE_LIMITE  DATE_DLV  ETAT_STOCK  NB_CARTON  ID_VARIANTE  INFO_STOCK  NB_INV  DATE_DERNIER_INV  NUMBER1  NUMBER2  NUMBER3  DATE1  DATE2  DATE3  TEXTE1  TEXTE2  TEXTE3  DATE_CREATION                  DM                             RM  EM  
            920339  0          448880  6018    -1      0        0               300      0             3212      02/09/2016 00:00:00,000000000  02/09/2016 00:00:00,000000000                         0           0          0                        0                                                                                                 22/12/2016 10:25:49,000000000  22/12/2016 10:25:57,000000000  15  -7  
            
            
            SQL> UPDATE LGN_PRLV SET ID_E_LGN_PRLV = 40 WHERE ID_LGN_PRLV = 905953;
            
            1 ligne mis à jour.
            
            SQL> COMMIT;
            Validation (commit) terminée.
            

             

            Seems that the SET NULL option is applied randomly (in first query, then not in the second, then again in the third...)

            AFAIK, NLS settings doesn't affect NULL formatting ?