Problems executing SQL Plus sessions

Shell Programming and Scripting
1

If I execute this script:

$ORACLE_HOME/bin/sqlplus -s <<EOF > oracle.log
$DB_id/$DB_pswd@$DB_server
start test.sql
EOF
the sql script executes with no errors.

IF I execute the following script:

UsedSpace=`df -k . | awk '{print $5 }'| tail -1 | cut -d"%" -f1`

if [ $UsedSpace -ge 50 ]
then
cd /ford/thishost/u/rct/www.80/crid/crid_new/cronjobs
$ORACLE_HOME/bin/sqlplus -s <<EOF > oracle.log
$DB_id/$DB_pswd@$DB_server
start test.sql
EOF
fi
I receive a syntax error. The syntax error must be the way I am wrapping the if clause because the line starting UsedSpace works fine. What am I doing wrong? Thanks for your help.

2

I have never seen the "Start" usage before. I would replace "start test.sql" with @test.sql (supply a path to the script if need be)

3

I have replace "start" with "@". I am still getting syntax errors. Is there anybody else that can help me?

4

What is the actual syntax error thrown? What is UsedSpace set to after this line is executed

UsedSpace=`df -k . | awk '{print $5 }'| tail -1 | cut -d"%" -f1`

Also (not that this will fix your issue but..) this would be cleaner syntax and it allows variables to be nested.

UsedSpace=$(df -k . | awk '{print $5 }'| tail -1 | cut -d"%" -f1)

5

$ORACLE_HOME/bin/sqlplus $DB_id/$DB_pswd@$DB_server <<EOF
.....
EOF

6

FWIW -
start comes the afi/ufi pre-Sqlplus versions of Oracle. It is exactly the same as @, just from version 2 of Oracle.

7

The value of this variable is 80. I have tried all suggestions, but I am still getting a syntax error.

test.sh: syntax error at line 34: `end of file' unexpected

Line 34 goes beyond the last line in file test.sh

8

the matching/ending EOF marker should have no leading spaces/tabs in front of it.

Pls post what you're trying.

9

When our end-users run reports, we save a copy of the report on our web server. I want to create a shell script that is executed by CRON to occasionally clean out reports and free up disk space.
Here is the report.

#!/bin/sh
UsedSpace=`df -k . | awk '{print $5 }'| tail -1 | cut -d"%" -f1`
##the cut -d"%" strips out the % sign.

if [ $UsedSpace -ge 50 ]
then
# ls -l >> $logfile
# $ORACLE_HOME/bin/sqlplus $DB_id/$DB_pswd@$DB_server <<SQLPLUS
$ORACLE_HOME/bin/sqlplus -s <<SQLPLUS > oracle.log
$DB_id/$DB_pswd@$DB_server
spool test1.log
select to_char(sysdate,'YYYYMMDDHHMMSS') FROM dual;
spool off
SQLPLUS
fi

When I execute this script from the command line, I get the following error: test.sh: syntax error at line 34: `end of file' unexpected

If I remove the If/end if lines, the scrip runs fine. Any thoughts?

10

When our end-users run reports, we save a copy of the report on our web server and to a db table. I want to create a shell script that is executed by CRON to occasionally clean out reports and free up disk space and remove the record from the db.
Here is the report.

#!/bin/sh
UsedSpace=`df -k . | awk '{print $5 }'| tail -1 | cut -d"%" -f1`
##the cut -d"%" strips out the % sign.

if [ $UsedSpace -ge 50 ]
then
# ls -l >> $logfile
# $ORACLE_HOME/bin/sqlplus $DB_id/$DB_pswd@$DB_server <<SQLPLUS
$ORACLE_HOME/bin/sqlplus -s <<SQLPLUS > oracle.log
$DB_id/$DB_pswd@$DB_server
spool test1.log
execute RemoveReports;
spool off
SQLPLUS
fi

When I execute this script from the command line, I get the following error: test.sh: syntax error at line 34: `end of file' unexpected

If I remove the If/end if lines, the scrip runs fine. Any thoughts?

mh53j_fe
View Public Profile
Find all posts by mh53j_fe
Add mh53j_fe to Your Buddy List

� Previous Thread

11

first, Is the RemoveReport a stored procedure or function? If it is you can use the execute command otherwise you would have to use either 'start 'or '@'RemoveReports to indicate the sql file that you are trying to run.

Second.
open your shell in vi an do a set list. If you find any spaces at the end of the lines after the sqlplus invocation remove those spaces and give it a try.

12

I do use the vi editor, and when i turned on set list, Voila!
I took out all the white spaces wrapped around the sql statements and the script ran fine.

Thanks to you and all the others that have replied to my requests today!

Dave

13

I think if you simply add a - (minus) after the << it should ignore any tabs and whitespace it finds.

<<-SQPLUS
...
SQLPLUS