Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

File read from UNIX to Oracle

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting File read from UNIX to Oracle
# 1  
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
# 2  
Old 08-07-2015
Did you solve this or was the thread tagged "solved" accidentally? If not, it would be interesting to know what the solution was, don't you think?

bakunin
# 3  
Old 08-07-2015
It was not accidentally tagged.

Solution is :

By using the positional character numbers. i have loaded the records into staging table.
This User Gave Thanks to arun888 For This Post:
bakunin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

10. 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
Login or Register to Ask a Question