[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5793
  • Last Modified:

Shell script to to produce csv file from an Oracle table



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
diteps06
Asked:
diteps06
1 Solution
 
swadhinrayCommented:
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
 
diteps06Author Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now