Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Loading Oracle data to unix in flat file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Loading Oracle data to unix in flat file
# 1  
Old 01-20-2009
Loading Oracle data to unix in flat file
Hi
I would like to know how to load oracle data to unix flat file using a shell script.

Can we use sqlldr to import data from oracle, if so can anyone help me with it.
Last edited by raghav288; 01-20-2009 at 04:54 AM..
# 2  
Old 01-20-2009
Loading Oracle data to unix in flat file
Use sqlplus to select your data and output it to a file:

Code:
LOGON={yourlogonstring}
OUTPUT={youroutpufilename}
SCHEMA={yourschema name}
SRCTBL={yoursourcetablename}

sqlplus -s $LOGON <<EOF >$OUTPUT
 column {columname1}       format {formatspec}  heading .
 column {columname2}       format {formatspec}  heading .
 ...other columns...
 set linesize {line size needed}
 select {columname1}
       ,{columname2}
       ,...other columns...
 from $SCHEMA.$SRCTBL;
EOF

Hope this gets you started

JG
# 3  
Old 01-23-2009
Quote:
Originally Posted by jgrogan
Use sqlplus to select your data and output it to a file:

Code:
LOGON={yourlogonstring}
OUTPUT={youroutpufilename}
SCHEMA={yourschema name}
SRCTBL={yoursourcetablename}

sqlplus -s $LOGON <<EOF >$OUTPUT
 column {columname1}       format {formatspec}  heading .
 column {columname2}       format {formatspec}  heading .
 ...other columns...
 set linesize {line size needed}
 select {columname1}
       ,{columname2}
       ,...other columns...
 from $SCHEMA.$SRCTBL;
EOF

Hope this gets you started

JG


Thank you very much Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Data is available or not in a flat file generated by Oracle

Hello, please help me an the below issue. i need to check whether data is available or not in a flat file generated by oracle (sometimes sql didn't any records) to overcome this. without opening flat file. Thanks....... (1 Reply)
Discussion started by: mahesh1987
1 Replies

2. Shell Programming and Scripting

File Content Loading to Oracle Error

I habe a UNIX Variablw with content as below: WHOLE_REC_TXT="$record" where $record contains contents of file Sample contents of file: topic_id|1624|AIDS-HIV|| topic_id|1625|Allergies|| topic_id|1626|Alzheimer s|| topic_id|1627|Knee Pain|| topic_id|1628|Autism||... (2 Replies)
Discussion started by: Arun Mishra
2 Replies

3. Shell Programming and Scripting

Remove white spaces from flat file generated from Oracle table...

I have to export data from table into flat file with | delimited. In the ksh file, I am adding below to do this activity. $DBSTRING contains the sqlplus command and $SQL_STRING contains the SQL query. File is created properly with the data as per SQL command. I am getting white spaces in the... (1 Reply)
Discussion started by: mgpatil31
1 Replies

4. Shell Programming and Scripting

Looping a flat file and loading data

Hi, I am very new to UNIX environment. And I am stuck in this problem. In my Unix server, I have a file called "tablelist.txt" which gives me a list of the tables in my oracle database like shown below. bmp.table1 bmp.table2 bmp.table3 : : bmp.table20 All these tables have 'batch_id'... (1 Reply)
Discussion started by: Reese coutinho
1 Replies

5. Shell Programming and Scripting

Loading data in oracle using shell scripts

Hi , I have a scenario, i have a directory where i receive around 14-15 files at a interval of 20-40 min not fixed, i want to write a unix scripts which invoke sqlldr command to load files into oracle automatically as soon as the file hit the directory. Any help will be appreciated. ... (4 Replies)
Discussion started by: guddu_12
4 Replies

6. Shell Programming and Scripting

Extracting data into flat file thru unix

Hi, I need to extract a oracle staging table to a flat file thru unix batch process.We are expecting more than 4million records in the table.I know I can do it using "UTL_FILE" .But,since "UTL_FILE" takes a lot of time I am looking for better options.Can any body suggest some better options? ... (3 Replies)
Discussion started by: Beena
3 Replies

7. Shell Programming and Scripting

Load data from a flat file to oracle.

I have a flat file with records like Header 123 James Williams Finance2000 124 Pete Pete HR 1500 125 PatrickHeather Engg 3000 Footer The structure is: Eno:4 characters Name:8 characters Surname : 9 characters Dept:7 characters Sal:4characters These are sample... (1 Reply)
Discussion started by: Shivdatta
1 Replies

8. Shell Programming and Scripting

unix script for loading a data from a file into database

Hi All, I am trying to load a data from a files in a particular directory to a database.. cd $SCC isql metdb >> ${LOGDATA}/LOAD.LOG < ! load from "${LDATA}/${FORM}.ld" insert into $LOADTABLE ! But it's showing the error "syntax error at line 46 : `<<' unmatched" Can u plz help me... (5 Replies)
Discussion started by: grajesh_955
5 Replies

9. Shell Programming and Scripting

Shell Script for Data loading in Oracle

Hi All I am new to unix. I need a shell script to load a datafile in to oracle. I already have a control file, and data file. all I need is if i execute the shell it must load the data using the ctl file to table. Control file : PAY0001.ctl Datafile : mon_grs_det.dat log file :... (3 Replies)
Discussion started by: raghuraja_r
3 Replies

10. Shell Programming and Scripting

unix script to export data from csv file to oracle database

Hello people, Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these... (3 Replies)
Discussion started by: vinayagan
3 Replies
Login or Register to Ask a Question