Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Shell script to to produce csv file from an Oracle table

Posted on 2011-03-15
2
Medium Priority
?
5,415 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

636 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