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

Shell Programming and Scripting
1

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:

$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

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

$ORACLE_HOME/bin/sqlplus  ${user}@${sid}/${pass}  <<EOD 
.
.
.
 copy contents of the SQL file here and you can use ${2} where ever appropriate
.
.
.
EOD
3

I tried your suggestion:

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

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

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

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.

  $ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
    ...
    ...
EOD
5

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

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

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

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:

cat -v yourscript
7

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.

8

So, no spaces/tabs before any line, no spaces/tabs after any line, no ^Ms anywhere and no blank lines, correct?

It's unusual for the here-document to not work. I tried a similar script in my Cygwin + bash + Oracle 11g system and it works fine. Could you check a few more things?

(1) The error message:

Does the line number 213 correspond to the following line you showed in your post?

$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD

Do you have more than one EODs in your script? Maybe you are looking at an EOD other than the one mentioned by the error message?

(2) What shell are you using? (Hopefully not too old.)

(3) Everything else remaining the same, replace this line:

$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<EOD

by this:

$ORACLE_HOME/bin/sqlplus $ORA_DBCU/$ORA_DBCP <<-EOD

Doing this allows the leading TABs before the ending EOD to be ignored.

(4) Remove everything in your shell script and simply test the smallest piece of code that balks:

... put the shebang; assign the variables
sqlplus $ORA_DBCU/$ORA_DBCP <<EOD
select sysdate from dual;
EOD
9

I still think the here document works, I see folks running it with -s or -silent switch. ^M should not be an issue but the can always be removed with the dos2unix tool or

cat <file> | tr -d '\r'

. I also don't think there is anything wrong with your alternative, it's not pretty, I would have used sed or awk...but as long as this thing does not consume to many cycles it's just fine. You might want to look into the functionality of sqlplus...you can probably pipe your sql to it as well... I know for mysql I can do a here document and can pipe to it and I can redirect standard input, by example....

cat foo.sql | mysql
mysql < foo.sql
mysql <<EOD
use foo;show tables;
EOD
1 Like
10

Ok, I tested the original mysql idea by blackrageous and it appears that something similar could be done for Oracle's sqlplus as well.
Using the "printf" built-in of Bash or print/printf of Ksh, you could do combine all commands together and pipe them to sqlplus.

In the following testcase, I have an Oracle procedure called "sp_test" in the "test" schema, that simply prints its input parameter value.

$ 
$ # In Bash
$ printf "set serveroutput on\n exec sp_test(p_yr_qtr => 4)" | sqlplus -s test/test
From procedure sp_test: input p_yr_qtr = 4

PL/SQL procedure successfully completed.

$ 
$

This could be incorporated in a shell script like so -

$ 
$ # display the content of the shell script
$ cat -n call_ora_proc.sh
     1    #!/usr/bin/bash
     2    export ORA_DBCU=test
     3    export ORA_DBCP=test
     4    export YR_QTR=4
     5    export spool_file=call_ora_proc.log
     6    printf "clear columns breaks computes
     7    set recsep off wrap off heading off pagesize 0 space 0 newpage 0 serveroutput on
     8    spool ${spool_file}
     9    exec sp_test(${YR_QTR});
    10    spool off
    11    exit" | $ORACLE_HOME/bin/sqlplus -s $ORA_DBCU/$ORA_DBCP 1>/dev/null
$ 
$ # run the shell script
$ . call_ora_proc.sh
$ 
$ # and check the log file created by it
$ cat call_ora_proc.log
From procedure sp_test: input p_yr_qtr = 4                                      

PL/SQL procedure successfully completed.

$ 
$

thereby avoiding a here-document.

1 Like
11

Sorry for the late responses, didnt get any emails until this morning that replies had been posted.

I appreciate the help. Based on blackrageous's comment

I think I will stick with that for now. This script loads data every quarter and is working, that being said it runs once per quarter thereby not taxing the server too bad.

Thanks again for taking the time to help. I am going to play around with your suggestions and try to get them to work for the sake of trying to improve my unix skillset.