Sponsored Content
Top Forums Shell Programming and Scripting File read from UNIX to Oracle Post 302951549 by arun888 on Friday 7th of August 2015 08:14:51 AM
Old 08-07-2015
File read from UNIX to Oracle

hi all,

I have flat file in unix. By using sql loader i need to import the data from the flat file. I have created a control file, table with the below data structure and pasted the sample input file. I am not sure whether it is a right way to do it. can anyone provide the thought on this.

Code:
$ cat sqlldr-fixed.ctl

load data infile '/unix/home/D080715.MMM'
into table testtable terminated by ""
 (LOCATION_id position(1:4), LOCATION_name position(5:54),address position(55:84),
  city position(85:114), state position(115:116), zip_code postion(117:121),
  zip_extension position(122:125), SEPARTOR2 position(126:126), country_code(127:129),
  SEPARTOR1 position(130:156), internal_contact position(157:186), LOCATION_contact position(187:216),
  contact_phone position(217:236), OCCURED_day position(237:237), mvmtSALES position(238:245)

Code:
Input Structure :
03 LOCATION_ID          CHAR(4),
03 LOCATION_NAME        CHAR(50),
03 ADDRESS           CHAR(30),
03 CITY              CHAR(30),
03 STATE             CHAR(2),
03 ZIP_CODE          CHAR(5),
03 ZIP_EXTENSION     CHAR(4),
03 TEMP2           CHAR(1),
03 COUNTRY_CODE      CHAR(3),
03 TEMP1           CHAR(27),
03 INTERNAL_CONTACT  CHAR(30),
03 LOCATION_CONTACT     CHAR(30),
03 CONTACT_PHONE     CHAR(20),
03 OCCURED_DAY       CHAR(1),
03 MVMTSALES          CHAR(9);

Input file View :
Code:
cat /unix/home/D080715.MMM | tail -5

YFG Yellow Front Grocery                              UPPER MAIN ST                 DAMARISCOTTA                  ME04543     Germany                       Satish Mohan                  Jeff                                              17Sun-6Sat
YLQ Young's Liquors                                   1432 E Mulberry St Unit A     FORT COLLINS                  CO80524                                   Andrea Biedron                                                                  11Mon-7Sun
YOK YOKES                                             3426 S UNIVERSITY             SPOKANE                       WA99206     USA                           Tiffani Caffrey               ED GOEBEL                     6099212292          33Wed-2Tue
YUN YOUNGS MARKET                                     P.O. Box 3167                 Visalia                       CA          USA                           Satish Mohan                  Ken Young                     6597339644          33Wed-2Tue


Last edited by rbatte1; 08-07-2015 at 09:50 AM.. Reason: Split up very long command to make it easier to read
 

10 More Discussions You Might Find Interesting

1. Solaris

exporting a dmp file (oracle)from unix

i want to export a database (full schema) so that I can import the database to a windows 2000 server. The database is sitting on a unix-sun solaris box ver 8.. What are the commands... I am a bit rusty at unix at the moment! Cheers E (4 Replies)
Discussion started by: etravels
4 Replies

2. Shell Programming and Scripting

How to read a dynamically changing file and load into Oracle?

I have a tab delimited file which has 27 character fields. The file needs to be loaded into an Oracle table. But the challenge is that everytime the file comes it may or may not have values in all 27 fields. Column Definition of the 27 fields: TYPE: Char (1) NAME: Char (30) CUSTOM_VAL: Char... (8 Replies)
Discussion started by: madhunk
8 Replies

3. Shell Programming and Scripting

unix file to oracle table

Hi , Can anyone help me regarding loading a unix file data to oracle database table using shell scripts? I wanted to grep only this data from a spool file sql_test.txt 99 00:00:00:01 but if I use grep I am getting format sql_test.txt 99 rows selected. Elapsed:... (2 Replies)
Discussion started by: ran16
2 Replies

4. UNIX for Advanced & Expert Users

How can i pass a file from unix to oracle??

I am having a file which has the following data 123424 2354235 142424 43423 1434 . . . . etc i want to pass these values to oracle at a time by using a file.. can someone suggest me a answer... waiting...... (1 Reply)
Discussion started by: vidyadhar85
1 Replies

5. UNIX for Advanced & Expert Users

How can i read a non text file in unix - ELF-64 executable object file - IA64

The binary file is ELF-64 executable object file - IA64. How i know that the source is Is there any comamnd in unix i can read these kind of files or use a thirty party software? Thanks for your help (8 Replies)
Discussion started by: alexcol
8 Replies

6. Shell Programming and Scripting

Read Oracle Username password SID from single file and pass it to shell

Dear All I am trying to write one shell which will be running through Cron which contain one SQL query. But I want to draw/fetch the Username password and Instance name (required to loging to the database) from one single file to run that SQL query . Also this file contain details of multiple... (2 Replies)
Discussion started by: jhon
2 Replies

7. Shell Programming and Scripting

Can't load external file from unix into Oracle DB

Hi all, I'm traying to run a script on Unix with in it sql +. In this script I want to load a externall file that stand on my unix system into a tmp table of a oracle db, but for some reason it won't work. Find below the script. #!/bin/ksh ORACLE_SID=db1... (7 Replies)
Discussion started by: Tuut-Tuut
7 Replies

8. Shell Programming and Scripting

shellscript to read data from txt file and import to oracle db

Hi all, Help needed urgently. I am currently writing a shellscript to read data/record from a flat file (.txt) file, and import/upload the data to oracle database. The script is working fine, but it takes too long time (for 18000 records, it takes around 90 mins). I guess it takes so long... (1 Reply)
Discussion started by: robot_mas
1 Replies

9. Shell Programming and Scripting

Oracle 11g script read from file in where clause (RHEL 5.7)

Hi everyone, Simple question. I have a comma-delimited file, aux.txt, with the following contents (all in one line): 'value1','value2','value3',...,'valueN' I would like to know if there's a way I can use that file inside a sql script in a where clause, like so: select myfield1 from mytable... (6 Replies)
Discussion started by: gacanepa
6 Replies

10. Shell Programming and Scripting

Read Oracle connection details from a configuration file

Hi, Is it possible to pass oracle connection information from a configuration file and use that to connect to oracle database in my unix shell scripts. Following is the scenario: (1) I would like to save oracle connection string details in a configuration file (ex., dbconfig.txt) (2) from my... (6 Replies)
Discussion started by: sudhakaratp
6 Replies
TR(1)								   User Commands							     TR(1)

NAME
tr - translate or delete characters SYNOPSIS
tr [OPTION]... SET1 [SET2] DESCRIPTION
Translate, squeeze, and/or delete characters from standard input, writing to standard output. -c, -C, --complement use the complement of SET1 -d, --delete delete characters in SET1, do not translate -s, --squeeze-repeats replace each input sequence of a repeated character that is listed in SET1 with a single occurrence of that character -t, --truncate-set1 first truncate SET1 to length of SET2 --help display this help and exit --version output version information and exit SETs are specified as strings of characters. Most represent themselves. Interpreted sequences are: NNN character with octal value NNN (1 to 3 octal digits) \ backslash a audible BEL  backspace f form feed new line return horizontal tab v vertical tab CHAR1-CHAR2 all characters from CHAR1 to CHAR2 in ascending order [CHAR*] in SET2, copies of CHAR until length of SET1 [CHAR*REPEAT] REPEAT copies of CHAR, REPEAT octal if starting with 0 [:alnum:] all letters and digits [:alpha:] all letters [:blank:] all horizontal whitespace [:cntrl:] all control characters [:digit:] all digits [:graph:] all printable characters, not including space [:lower:] all lower case letters [:print:] all printable characters, including space [:punct:] all punctuation characters [:space:] all horizontal or vertical whitespace [:upper:] all upper case letters [:xdigit:] all hexadecimal digits [=CHAR=] all characters which are equivalent to CHAR Translation occurs if -d is not given and both SET1 and SET2 appear. -t may be used only when translating. SET2 is extended to length of SET1 by repeating its last character as necessary. Excess characters of SET2 are ignored. Only [:lower:] and [:upper:] are guaranteed to expand in ascending order; used in SET2 while translating, they may only be used in pairs to specify case conversion. -s uses SET1 if not translating nor deleting; else squeezing uses SET2 and occurs after translation or deletion. AUTHOR
Written by Jim Meyering. REPORTING BUGS
Report tr bugs to bug-coreutils@gnu.org GNU coreutils home page: <http://www.gnu.org/software/coreutils/> General help using GNU software: <http://www.gnu.org/gethelp/> Report tr translation bugs to <http://translationproject.org/team/> COPYRIGHT
Copyright (C) 2011 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. SEE ALSO
The full documentation for tr is maintained as a Texinfo manual. If the info and tr programs are properly installed at your site, the com- mand info coreutils 'tr invocation' should give you access to the complete manual. GNU coreutils 8.12.197-032bb September 2011 TR(1)
All times are GMT -4. The time now is 07:50 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy