SQL Report from UNIX


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers SQL Report from UNIX
# 1  
Old 10-09-2014
SQL Report from UNIX

I am trying to write a shell script which dynamically reads all the .sql (oracle sql files) from a particular directory and generates a .csv file in the target directory.

I have started first with the below sample script to see if it is working but I am not able to get it executed, please help to check and tell me where I am doing it wrong

Code:
#! /usr/bin/sh

OUT=/report/path/report.txt

#oracle variables
ORACLE_HOME=/path/oracle/client
export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
TNS_ADMIN=/path/tnsnames
export TNS_ADMIN
SCRIPT_NAME=”$1” 
# I am trying to execute one .sql file first to see if the script is working

$SQLPLUS -s "user/pass@database" << END_SQL > /dev/null

SET ECHO OFF
SET TERMOUT OFF

SET PAGESIZE 50000

SET LINESIZE 32767
SET TRIMSPOOL ON

SPOOL $OUT

@ /tmp/query.sql

# I even tried giving the sql statement here , I get  the error like spool command unknown

SPOOL OFF
END_SQL

Thanks and Regards,
Mora
# 2  
Old 10-09-2014
Hi,

I am not an SQL person, however I would think that you'll have to use something like expect to manage that as the shell will not understand your SQL commands.

Regards

Dave
This User Gave Thanks to gull04 For This Post:
# 3  
Old 10-09-2014
SQL*Plus accepts here documents, no need for expect.
What is the exact error message you see?
Remove the redirection to /dev/null and put set -x before your call to sqlplus to see what is happening.
This User Gave Thanks to cero For This Post:
# 4  
Old 10-09-2014
Hey,
Let me know if you can use batch scripting I have a script with me which exactly suits your requirement some how this kind of dynamic scripting is tough to implement in unix.
Regards,
Shruthi
# 5  
Old 10-09-2014
I see no exit for sqlplus... and I also am no more much in oracle but at the time I used start commmand, so if I adapt your code to the way I would have done, it would give:
In /tmp/query.sql:
Code:
WHENEVER SQLERROR EXIT
SPOOL /report/path/report.txt

SET ECHO OFF
SET TERMOUT OFF
SET PAGESIZE 50000
SET LINESIZE 32767
SET TRIMSPOOL ON
/*
SPOOL $OUT       # That I am not sure  since you are no more in a shell script but in a .sql
So  see above  but I let you try and you tell us... I have no more access to oracle...      */

The SQL code you had here.....

SPOOL OFF


Code:
#! /usr/bin/sh

OUT=/report/path/report.txt

#oracle variables
ORACLE_HOME=/path/oracle/client
export ORACLE_HOME
SQLPLUS=$ORACLE_HOME/bin/sqlplus
export SQLPLUS
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
TNS_ADMIN=/path/tnsnames
export TNS_ADMIN
SCRIPT_NAME=”$1” 
# I am trying to execute one .sql file first to see if the script is working

$SQLPLUS -s "user/pass@database" << END_SQL      # oracle will be looking for a .sql  so:
start  /tmp/query         
exit;
END_SQL


Last edited by vbe; 10-09-2014 at 10:59 AM.. Reason: Corrected spool
This User Gave Thanks to vbe For This Post:
# 6  
Old 10-09-2014
The script should work fine the way mora posted it. The at sign (@) is short for the start command and the exit command is not needed if sqlplus gets fed by a here document (or a pipe).
I suspect that a variable is set wrong - most likely $ORACLE_HOME.
This User Gave Thanks to cero For This Post:
# 7  
Old 10-10-2014
Thanks for the replies, now the script is working fine, please help to tell me how can I parametrize the script name and how can I dynamically pass all the sqls from a folder one by one to the main script.

Regards,
Mora
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

2. UNIX for Dummies Questions & Answers

SQL PLUS report formatting

Hi I am fetcthing the data from the oracle database using SQLPLUS. Here is my script #!/bin/ksh echo `sqlplus -s <<EOF set feedback off set linesize 5000 set pages 0 set space 0 set echo off set trimspool on set colsep '|' SELECT col1 , col2... (4 Replies)
Discussion started by: max_hammer
4 Replies

3. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

4. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

5. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

6. UNIX for Dummies Questions & Answers

SQL in Unix

Hi, I would like to access SQL plus from my unix, and learn to run some SQL scripts in Unix. How do i begin. Is there any known website that offers a quick tutorial or tips? Thanks, unxhopeful (4 Replies)
Discussion started by: unxhopeful
4 Replies

7. Shell Programming and Scripting

sql through unix.

when we are spooling query o/p to certain txt file,in that file how we can get headers in the query.(through unix shell scripting). for exmple q1="slect * from XXXXXX;"; sqlplus XXX/XXXX@XXXXX spool XXXX.txt $q1 spool off in the text file i want the headers of the query..... ... (1 Reply)
Discussion started by: bhagya.puccha
1 Replies

8. Shell Programming and Scripting

Using SQL in Unix

Please tell me how i can use SQL in my unix scripts. Please give examples. I just want to start writing some scripts for my use. Looking for ur cooperation. thanks (2 Replies)
Discussion started by: Mohit623
2 Replies

9. Shell Programming and Scripting

Script needed to FTP a file from sql report to unix server

Hi All, I have a Sqlplus report which will create a file. I need a FTP Script that will be executed inside the Sqlplus Report to FTP the report output file to unix server. Thanks, Previn (0 Replies)
Discussion started by: vprevin
0 Replies

10. Shell Programming and Scripting

sql plus report under unix environment

Hi i am creating a report with file format csv using sql plus commands under unix environment. I am sending this report using mailx command to excel spread sheet. when i opened the report my columns are merged. I need to manually pull each column to see whole column results. Is there any way in... (0 Replies)
Discussion started by: raosurya
0 Replies
Login or Register to Ask a Question