Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: February 14, 2017 - 8:09 am UTC

Last updated: July 06, 2020 - 5:42 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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?


and Connor said...

I assume you are using SQL Loader ?

If so, then all you need is "delimited by ','" and do *not* have anything like "optionally enclosed by" in your control file.

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"


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Shell Script

A reader, March 28, 2017 - 6:17 am UTC

would you explain what benefits we get if we use Shell script to call sql loader.
Connor McDonald
March 28, 2017 - 12:20 pm UTC

It wont impact SQL Loader, but you probably want to have something once SQL loader runs that

- checks to see if the load worked
- looks for oracle errors
- compares rows input with rows loaded

etc etc...

You could do it manually...but you'd probably ultimately want to automate that - and a shell script is good for that.

Documentation

santhoshreddy podduturi, May 05, 2017 - 4:28 am UTC

Is there any oracle documentation or any other resources explaining about shell script to load data? if there please give me.
Connor McDonald
May 09, 2017 - 2:23 am UTC

Search for "sqlldr" on this site, there will be plenty of examples

Fix the Bug

A reader, May 09, 2017 - 8:53 am UTC

Hi conor,
when i searched with sqlldr text the GO buttn getting Disabled,worked fine with doublequotes "sqlldr" please fix this bug.
i found so many questions regarding loading Data But not exactly what i am looking for.

I need to Help me

A reader, July 04, 2020 - 5:49 pm UTC

I'm run my work in server, i need of script shell linux for export data (table ) from oracle sql developer to server form file.txt for exemple.
Thanks

Help me

moha, July 04, 2020 - 5:54 pm UTC

Hi, thanks for your best script. i run my work in server, i need of script shell linux for import data (Table) from oracle sql developer to server form file.txt for exemple
Connor McDonald
July 06, 2020 - 5:42 am UTC

Not sure what you mean, but perhaps something like

#!/bin/ksh
export ORACLE_HOME=...
export ORACLE_SID=...
export PATH=$ORACLE_HOME/bin:$PATH

print "
connect scott/tiger
set markup csv on
set pages 0
set lines 1000
set trimspool on
select * from mytable;" | sqlplus -s /nolog 1>/tmp/file.out 2>&1