Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Issue in SQL Loader scripts
Top Forums Shell Programming and Scripting Issue in SQL Loader scripts Post 302729969 by shyamu544 on Monday 12th of November 2012 03:43:56 AM
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..
 

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

10. 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
All times are GMT -4. The time now is 09:24 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy