Allow the Null Fields

I have a little question concerned one script to execute on ORACLE.

How to modify a column to accept null fields by putting them between " "? the column that I want to modify is the column " CONTACT ".

DROP  TABLE ITIN38.EXT_S_APPEL;
 CREATE TABLE ITIN38.EXT_S_APPEL 
   (    ID NUMBER NOT NULL ENABLE, 
    DATE_APPEL VARCHAR2(29 BYTE) COLLATE USING_NLS_COMP NOT NULL ENABLE, 
    SENS_APPEL_CODE NUMBER(*,0) NOT NULL ENABLE, 
    TYPE_EVENEMENT_CODE NUMBER(*,0) NOT NULL ENABLE, 
    NUMERO VARCHAR2(20 BYTE) COLLATE USING_NLS_COMP, 
    CONTACT VARCHAR2(100 BYTE) DEFAULT ON NULL '' COLLATE USING_NLS_COMP, 
    LIGNE_ID NUMBER(*,0), 
    COMMENTAIRE CLOB COLLATE USING_NLS_COMP, 
    DATE_MAJ VARCHAR2(29 BYTE) COLLATE USING_NLS_COMP NOT NULL ENABLE, 
    ETAT_APPEL_CODE NUMBER(*,0) NOT NULL ENABLE, 
    EVENEMENT_RATT_ID NUMBER
   )  DEFAULT COLLATION USING_NLS_COMP 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY TRANSPORTS38_PCTC
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY newline
    SKIP 1
    CHARACTERSET UTF8
    BADFILE 's_appel.bad'
    LOGFILE 's_appel.log'
    FIELDS TERMINATED BY ';'
    OPTIONALLY ENCLOSED BY '"'
    (
        ID CHAR(100),
    DATE_APPEL CHAR(100),
    SENS_APPEL_CODE CHAR(100),
    TYPE_EVENEMENT_CODE CHAR(100),
    NUMERO CHAR(100),
    CONTACT CHAR(100),
    LIGNE_ID CHAR(100),
    COMMENTAIRE CHAR(10000),
    DATE_MAJ CHAR(100),
    ETAT_APPEL_CODE CHAR(100),
    EVENEMENT_RATT_ID CHAR(100)
    )
        )
      LOCATION
       ( 's_appel.csv'
       )
    )
   REJECT LIMIT 1000 ;

Can you please suggest me a solution for this script at the column level? I tried with this one but doesn't work :/

CONTACT VARCHAR2(100 BYTE) DEFAULT ON NULL '' COLLATE USING_NLS_COMP,
1

1 Answer

Oracle doesn't make any distinction between empty string and NULL. If you want to allow NULL, you need to make the column nullable and maybe tweak your app code to do not handle empty strings differently:

CONTACT VARCHAR2(100 BYTE) COLLATE USING_NLS_COMP

Demo

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Elena Rostova

Elena Rostova

Lead Health, Wellness & Medical Journalist

Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.

Share this article
Twitter Facebook Pinterest