Solved

Shell script to to produce csv file from an Oracle table

Posted on 2011-03-15
2
4,246 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 250 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
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 …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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