Thanks for the question, santhoshreddy.
Asked: January 09, 2017 - 5:30 am UTC
Last updated: January 10, 2017 - 3:11 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi,
i loading some into table by using sql loader. to uderstand my issue clearly See the Below Example
INSERT INTO t1 VALUES (2,'san'thu');
above statement throws error ORA-00917 missing comma. so to insert san'thu below is the correct statement
INSERT INTO t1 Values (2,'san''thu');
it insert data san'thu into table t1.
So i have data to load into table by using sql loader
But the Data file Contain lot of data like san'thu.
sql loader is unable to insert data like san'thu, i think because of ' . so i would like to know how to insert data into table contains ' with sql loader.
and Connor said...
You might need to load the data into a staging table first and reformat that. THere is an example here.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2976181076629 The staging table could just be an external table, where you load the entire line into a single column and then parse it out.
Do you have any scope to alter the incoming source file. For example, you could have a pre-processor option for an external table which (say) converts all quotes to some other character, and then use a REPLACE(col,'$','''') as part of an external table SQL load.