?
Solved

Shell script to to produce csv file from an Oracle table

Posted on 2011-03-15
2
Medium Priority
?
4,956 Views
Last Modified: 2012-05-11


I would like to write a unix script to produce a csv file from an Oracle table with the following requirements:
The name of the file should be in particular format Envirnment_CurrentDate.
The file should connect to the table
The first line should be the column of the table.
The data of the table should then populate the file
The last row should be 10 # character to indicate end of file.


I wouuld like to use it in a data warehouse environment to create a csv file from an oracle table.
I am new to UNIX scripting can someone indicate a valueable source to start learning it.
0
Comment
Question by:diteps06
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
swadhinray earned 1000 total points
ID: 35144893
Not sure about how to add the Envirnment_CurrentDate in shell but you can generate the a CSV from your query.

Here is an example how I used to get the csv file from shell script :




 
#!/bin/ksh
ORACLE_HOME=/u01/app/oracle/product/11.1.0
ORACLE_SID=DBATOOL
PATH=/usr/bin:/usr/local/bin::/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/local/ssl:/usr/local/etc:/usr/openwin/bin/:/opt/EMCpower/bin:/etc/emc/bin:/etc:/u01/app/oracle/product/11.1.0/bin
export ORACLE_HOME ORACLE_SID PATH
cd /export/home/oracle
sqlplus /nolog << EOF
CONNECT USER/PASSWORD
SPOOL /export/home/test1.CSV;
set feedback off
set trimspool on
set linesize 400
set pagesize 500
set heading on
SELECT columns 
FROM
TABLE NAME
WHERE CONDITION ; 
spool off;
EXIT;
EOF

Open in new window



Hope other experts will provide how to implement your logic . I have just provided example for generating a CSV file from shell .

You create a script called like test.ksh and give read and write privileges to it and then run the script by :

$ ./ test.ksh
0
 
LVL 1

Author Comment

by:diteps06
ID: 35174344
Thanks for the reply.
I think figure out some of the statements but not all .
ORACLE_HOME should be the oracle installation directory
ORACLE_SID the name of the oracle instance
Could u please explain the commands preceding the SQL query.

Thanks in advance
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question