Solved

Shell script to to produce csv file from an Oracle table

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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