Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Issue in SQL Loader scripts

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Issue in SQL Loader scripts
# 1  
Old 11-12-2012
Issue in SQL Loader scripts
Hi,

I'm planning to load the data from FLAT files into tables.

Source file: more input.txt

Code:
LRNO|Bale|Horsepower|NumberOfBarges|BollardPull|NumberOfCars|GasCapacity|GrainCapacity|IndicatedHorsepower|LiquidCapacity|
NumberOfPassengers|NumberRefrigeratedContainers|NumberOfTEU|NumberOfTrucks|NumberOfVehicles|NumberOfRailWagons
5002170|15884|11001||0||0|17647|11001|0|4|0|40|||
5004099|19022|18151||0||0|19385|18151|0|0|0|0|||
5004245|19022|18151||0||0|19385|18151|0|0|0|0|||

But when i'm opening same file through VI editor it showing new line feed character as well.

Code:
LRNO|Bale|Horsepower|NumberOfBarges|BollardPull|NumberOfCars|GasCapacity|GrainCapacity|IndicatedHorsepower|LiquidCapacity|
NumberOfPassengers|NumberRefrigeratedContainers|NumberOfTEU|NumberOfTrucks|NumberOfVehicles|NumberOfRailWagons^M
5002170|15884|11001||0||0|17647|11001|0|4|0|40|||^M
5004099|19022|18151||0||0|19385|18151|0|0|0|0|||^M
5004245|19022|18151||0||0|19385|18151|0|0|0|0|||^M

Hence I'm getting error as
Code:


Code:



Record 2: Rejected - Error on table DFN_IHS_CAPACITIES, column NUMBEROFRAILWAGONS.
ORA-01722: invalid number






Even, SQL loader script as also follwos.
 
load data
infile '../in/ input.txt'
truncate
into table dfn_IHS_Capacities
fields terminated by '|' --optionally enclosed by '"'
trailing nullcols
(
LRNO                            "ltrim(rtrim(:LRNO))",
Bale                            "ltrim(rtrim(:Bale))",
Horsepower                      "ltrim(rtrim(:Horsepower))",
NumberOfBarges                  "ltrim(rtrim(:NumberOfBarges))",
BollardPull                     "ltrim(rtrim(:BollardPull))",
NumberOfCars                    "ltrim(rtrim(:NumberOfCars))",
GasCapacity                     "ltrim(rtrim(:GasCapacity))",
GrainCapacity                   "ltrim(rtrim(:GrainCapacity))",
IndicatedHorsepower             "ltrim(rtrim(:IndicatedHorsepower))",
LiquidCapacity                  "ltrim(rtrim(:LiquidCapacity))",
NumberOfPassengers              "ltrim(rtrim(:NumberOfPassengers))",
NumberRefrigeratedContainers    "ltrim(rtrim(:NumberRefrigeratedContainers))",
NumberOfTEU                     "ltrim(rtrim(:NumberOfTEU))",
NumberOfTrucks                  "ltrim(rtrim(:NumberOfTrucks))",
NumberOfVehicles                "ltrim(rtrim(:NumberOfVehicles))",
NumberOfRailWagons              "ltrim(rtrim(:NumberOfRailWagons))",
SQLLoaddate                     SYSDATE)

Could you please advise me how to remove that new feed line character ?

Please let me know if any information needed from My side.

Moderator's Comments:
Mod Comment Please use code tags next time for your code and data.
Last edited by radoulov; 11-12-2012 at 04:59 AM..
# 2  
Old 11-12-2012
To get rid of the DOS carriage return:
Code:
perl -pi -e 's/\r\n/\n/g' input.txt

# 3  
Old 11-12-2012
or convert the file dos2unix..it will remove the control+m characters.....
# 4  
Old 11-12-2012
Is there any possible handle the things through SQL loader scripts ?
# 5  
Old 11-12-2012
yes,it is possible..
# 6  
Old 11-12-2012
BMk,Can you please let me know whatZ command can add in this control file ?

Code:
load data
infile '../in/ input.txt'
truncate
into table dfn_IHS_Capacities
fields terminated by '|' --optionally enclosed by '"'
trailing nullcols
(
LRNO                            "ltrim(rtrim(:LRNO))",
Bale                            "ltrim(rtrim(:Bale))",
Horsepower                      "ltrim(rtrim(:Horsepower))",
NumberOfBarges                  "ltrim(rtrim(:NumberOfBarges))",
BollardPull                     "ltrim(rtrim(:BollardPull))",
NumberOfCars                    "ltrim(rtrim(:NumberOfCars))",
GasCapacity                     "ltrim(rtrim(:GasCapacity))",
GrainCapacity                   "ltrim(rtrim(:GrainCapacity))",
IndicatedHorsepower             "ltrim(rtrim(:IndicatedHorsepower))",
LiquidCapacity                  "ltrim(rtrim(:LiquidCapacity))",
NumberOfPassengers              "ltrim(rtrim(:NumberOfPassengers))",
NumberRefrigeratedContainers    "ltrim(rtrim(:NumberRefrigeratedContainers))",
NumberOfTEU                     "ltrim(rtrim(:NumberOfTEU))",
NumberOfTrucks                  "ltrim(rtrim(:NumberOfTrucks))",
NumberOfVehicles                "ltrim(rtrim(:NumberOfVehicles))",
NumberOfRailWagons              "ltrim(rtrim(:NumberOfRailWagons))",
SQLLoaddate                     SYSDATE)

# 7  
Old 11-12-2012
Go to this site,Try and let me know
OraFAQ Forum: Server Utilities » sql loader data isse
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Help Command in SQL LOADER

Which is the UNIX command to get the SQL Loader Help??:confused: (1 Reply)
Discussion started by: venkatesh sunil
1 Replies

2. Shell Programming and Scripting

use of sql loader in the script

All, Below is the code written when sql loader loads the data: $ORACLE_EXEC_PATH/sqlldr userid=$user_passwd control=$CNTR_FILES/invoice.ctl log=$LOG_FILES/$INVOICE_FILE.log bad=$ER ROR_FILES/$INVOICE_FILE.bad data=$DATA_FILES/$INVOICE_FILE silent=FEEDBACK direct=false errors=50000 rc=$?... (1 Reply)
Discussion started by: Oracle_User
1 Replies

3. UNIX for Dummies Questions & Answers

SQL*Loader

HI Experts, We have a ksh file named ldr_empdelta.ksh and it is having the SQL*LOADER script as follows. print "LOAD DATA" > $WEDB_GEN/ldrscan.ctl print "INFILE '"$LED_SCAN"/delta/led_del.dat'" >> $WEDB_GEN/ldrparms.ctl print "TRUNCATE" >> $WEDB_GEN/ldrscan.ctl print "PRESERVE BLANKS" >>... (1 Reply)
Discussion started by: ajaykumarkona
1 Replies

4. Shell Programming and Scripting

Problem in sql loader

Hi Frnds, I wanto load the data thats in data file with comma(,) delimited. it throw this errors SQL*Loader-951: Error calling once/load initialization ORA-00604: error occurred at recursive SQL level 1 ORA-00054: resource busy and acquire with NOWAIT specified Plz look at my file... (3 Replies)
Discussion started by: Gopal_Engg
3 Replies

5. Shell Programming and Scripting

sql loader script

hi all i am new to shell scripting. i have 35 flat files delimited files that i have to load in to a table. i was asked to create a shell script that loads the data into the table... could you please help me if you have a script. Also .. how to create logs files...does my sql... (3 Replies)
Discussion started by: rajesh_tns
3 Replies

6. Shell Programming and Scripting

SQL*Loader in shellscript

Hi, what is the ideal format of the file to load it into oracle table by using SQL*LOADER. The file contains six columns and the oracle table contains 15 columns. Is there any criteria that columns should be equal in no.? Can anyone help me on this, i need to use this in my shell script? ... (2 Replies)
Discussion started by: venkatesht
2 Replies

7. UNIX for Dummies Questions & Answers

Problem with SQL loader

Hi, I am trying to load the records in a .txt file into the data base using SQL loader. I gave: $ sqlldr user/pass@DB control=filename.ctl log =mylog.log It throwed the following error: SQL*Loader-297: Invalid syntax or bind variable in SQL string for column STATUS. ORA-01756: quoted... (2 Replies)
Discussion started by: risshanth
2 Replies

8. Shell Programming and Scripting

A SQL Loader Script

Hi Guys, I am looking to develop a SQL Loader script that would bascially allow the user to have a txt file (or such) as an input file containing .sql scripts procedures, triggers, bascially anything against a database that could then be run automatically. Let me break it down a bit more,... (2 Replies)
Discussion started by: LiquidChild
2 Replies

9. Shell Programming and Scripting

SQL scripts not running, possible timeout issue?

I am a novice Unix scripter and need a little advice/help on a script I've written that's causing some problems. We are using Solaris 9 on a Sun box and the script is invoked with the korn shell. I have a two-part question: I wrote a shell script that calls and executes 3 separate sql scripts,... (3 Replies)
Discussion started by: E2004
3 Replies

10. Shell Programming and Scripting

SQL loader error

Hi friends, i am getting the following error when i am using sql loader. SQL*Loader: Release 8.1.7.4.0 - Production on Tue Apr 19 13:00:07 2005 (c) Copyright 2000 Oracle Corporation. All rights reserved. SQL*Loader-704: Internal error: ulconnect: OCIServerAttach ORA-12535:... (4 Replies)
Discussion started by: sveera
4 Replies
Login or Register to Ask a Question