Oracle 加载数据的Shell脚本

askTom 2017-02-14

185

问题描述

Hi,
we work on oracle apps and using 12c Data Base in Back end.
We Are using a concurrent program(shell script) to load data into staging table from front End.in data file we have data like below
col 1             Col2          Col3
1025-1'-100-01, mfg1,          1025stacks
1066-1'-252-01, NSK -|| ABEC5, NULL
1071-1'-352-01, SAINT-GOBAIN,  504289229
1071-1'-358-01, "DAR,OID",     2-454 E893-80@ 
the program rejecting the rows contain ' for example  col1 contains ' in 1st 1025-1'-100-01  so it was rejected.we have lots of records to load.

i used escape character \ in data file like this 1025-1\''-100-01. but it did not work, only working with insert statement. 

So is there any alternative to avoid this issue?

复制

专家解答

我假设你使用的是SQL Loader？

如果是这样，那么您所需要的只是 “由 '，' 分隔”，并且在控制文件中 * 不要 * 具有 “可选地由” 之类的内容。

C:\Users\hamcdc>cat c:\temp\t.ctl
load data
INFILE *
replace INTO TABLE t
FIELDS TERMINATED BY ','
(col1 "trim(:col1)", col2 "trim(:col2)", col3 "trim(:col3)")
begindata
1025-1'-100-01, mfg1,          1025stacks
1066-1'-252-01, NSK -|| ABEC5, NULL
1071-1'-352-01, SAINT-GOBAIN,  504289229
1071-1'-358-01, "DAR,OID",     2-454 E893-80@

C:\Users\hamcdc>sqlldr userid=mcdonac control=c:\temp\t.ctl
Password:

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Feb 14 18:22:59 2017

Copyright (c) 1982, 2016, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 4

Table T:
  4 Rows successfully loaded.

Check the log file:
  t.log
for more information about the load.

SQL> select * from t;

COL1                 COL2                 COL3
-------------------- -------------------- ----------------
1025-1'-100-01       mfg1                 1025stacks
1066-1'-252-01       NSK -|| ABEC5        NULL
1071-1'-352-01       SAINT-GOBAIN         504289229
1071-1'-358-01       "DAR                 OID"
复制