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...
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 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