Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Actual SQL instead of using a file from within a shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Actual SQL instead of using a file from within a shell script
# 1  
Old 07-31-2013
Actual SQL instead of using a file from within a shell script
I am very noobish to UNIX, our guy is on vacation so I am trying to take up some slack while he is away.

Typically when we use sql from within a shell script, we do so from a file containing the sql.

Example:
Code:
$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP @${cron_dir}/${report_file}.sql

However I am now needing to run a stored procedure and a few deletes that I have to pass a parameter from ${2}

So the question is how can I get things to run from the shell script itself instead of wrapping things up in a file?

Thanks in advance
# 2  
Old 07-31-2013
I am not clear on what you are asking. It sounds like you have a second argument passed in ${2} and you want to use it's value inside the *.sql? I think sqlplus will respect a here document, so you could do
Code:
$ORACLE_HOME/bin/sqlplus  ${user}@${sid}/${pass}  <<EOD 
.
.
.
 copy contents of the SQL file here and you can use ${2} where ever appropriate
.
.
.
EOD

Last edited by blackrageous; 07-31-2013 at 07:07 PM..
# 3  
Old 07-31-2013
I tried your suggestion:

Code:
export YR_QTR=${2}
  $ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
  
  clear columns
  clear breaks
  clear computes
  set recsep off
  set wrap off
  set heading off
  set pagesize 0
  set space 0
  set newpage 0
    
  spool ${spool_file}
    
  set serveroutput on;
    
  exec test.sp_test(${YR_QTR});
    
  spool off;
    
exit;
  
  EOD

I got this error

Code:
syntax error at line 213 : `<<' unmatched

Ideas?

---------- Post updated at 08:00 PM ---------- Previous update was at 06:44 PM ----------

I figured out an alternative, I built the .sql file on the fly and remove it when I am done

Code:
export YR_QTR=${2}
  
  echo "clear columns" >> ${file_qtr}
  echo "clear breaks" >> ${file_qtr}
  echo "clear computes" >> ${file_qtr}
  echo "set recsep off" >> ${file_qtr}
  echo "set wrap off" >> ${file_qtr}
  echo "set heading off" >> ${file_qtr}
  echo "set pagesize 0" >> ${file_qtr}
  echo "set space 0" >> ${file_qtr}
  echo "set newpage 0" >> ${file_qtr}
    
  echo "spool ${spool_file}" >> ${file_qtr}
    
  echo "set serveroutput on;" >> ${file_qtr}
    
  echo "exec test.test(${YR_QTR});" >> ${file_qtr}
    
  echo "spool off;" >> ${file_qtr}
    
  echo "exit;" >> ${file_qtr}
  
  $ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP @${file_qtr}
  rm "${file_qtr}"

# 4  
Old 08-01-2013
That seems a lot of extra effort. The problem with the << error is that the closing EOD must be at the beginning of the line, not indented, as you have it.

Code:
  $ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
    ...
    ...
EOD

# 5  
Old 08-01-2013
Quote:
Originally Posted by Scott
That seems a lot of extra effort. The problem with the << error is that the closing EOD must be at the beginning of the line, not indented, as you have it.

Code:
  $ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
    ...
    ...
EOD

Tried it again with your suggestion. Everything left justified, same error:

Code:
export YR_QTR=${2}
$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
  
clear columns
clear breaks
clear computes
set recsep off
set wrap off
set heading off
set pagesize 0
set space 0
set newpage 0
  
spool ${spool_file}
  
set serveroutput on;
  
exec test.sp_test(${YR_QTR});
  
spool off;
    
exit;
  
EOD

also tried it without skipping lines

Code:
export YR_QTR=${2}
$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
clear columns
clear breaks
clear computes
set recsep off
set wrap off
set heading off
set pagesize 0
set space 0
set newpage 0
spool ${spool_file}
set serveroutput on;
exec test.sp_test(${YR_QTR});
spool off;
exit;
EOD

still not working... any other thoughts? I really like the idea of keeping it contained within the coding instead of creating a seperate file.
# 6  
Old 08-01-2013
Make sure, too, that there's no trailing spaces. Otherwise, maybe your file has ^M, or some other hidden characters in it, so please show the output of:
Code:
cat -v yourscript

# 7  
Old 08-01-2013
There are no spaces after any of the lines, in the code section posted it was a direct cut and paste and you cant highlight anything after the last character on any line.

As far as the ^M... that is not the case either. I learned that the hard way last year. I am not a big fan of VI so I modify the files in a windows environment then ftp over, once transferred I remove the ^M via vi before running the file.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script to call sql file

hi , the below script contains sql query and after executed it sends the output of the query (output.txt) to an email body with conditional subject line based on the output of all_counts_match.txt. i want to make this script generic so that it can accept the sql file as parameter and can... (5 Replies)
Discussion started by: itzkashi
5 Replies

2. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file s1.sh ... (4 Replies)
Discussion started by: reddy298599
4 Replies

3. Shell Programming and Scripting

Shell script that will compare two config files and produce 2 outputs 1)actual config file 2)report

Hi I am new to shell scripting. There is a requirement to write a shell script to meet follwing needs.Prompt reply shall be highly appreciated. script that will compare two config files and produce 2 outputs - actual config file and a report indicating changes made. OS :Susi linux ver 10.3. ... (4 Replies)
Discussion started by: muraliinfy04
4 Replies

4. Shell Programming and Scripting

Calling sql file from shell script with parameters.

Hi, I am calling a sql file script.sql from shell script and passing few parameters also as shown below: sqlplus -S id/password @script.sql $param1 $param2 Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as... (7 Replies)
Discussion started by: anil029
7 Replies

5. Shell Programming and Scripting

calling a sql file in my shell script

Hi, I want to call a sql file in my shell script. see the below code:- if ] then ( isql -U${S_USER} -S${S_SERV} -w100 -b -h0 <<ENDSQL | sed -e "s/Password://" ${S_PWD} set nocount on go use ${S_DB} go // need to call a file name... (16 Replies)
Discussion started by: dazdseg
16 Replies

6. 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

7. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

8. UNIX for Advanced & Expert Users

Calling sql file from shell script

Hi I have a shell script that call a sql file. The sql file will create a spool file. My requirement is, when ever i get an OS error like file not found. I have to log it in a log file. Could some who worked in a like scenario help me by giving the code sample. Many Thanks.. (1 Reply)
Discussion started by: chintapalli001
1 Replies

9. Shell Programming and Scripting

calling sql file from shell script

Hello everybody I need help calling sql file from shell script. Can anyone help me creating a small shell script which calls an sql file . The .sql file should contain some select statements like select emp_no from emp_table; select emp_id from emp_table; And the results should be... (6 Replies)
Discussion started by: dummy_needhelp
6 Replies

10. Shell Programming and Scripting

How to execute a .sql file with shell script

hi everybody... can anyone help me in executing the .sql file with shell scripting.... thanx in advance (2 Replies)
Discussion started by: abuanas
2 Replies
Login or Register to Ask a Question