Multiple arguments passed to sqlplus in shell script is not getting accepted

Question

I am trying to execute a shell script where variables with values in shell script are getting passed to sqlplus. But it is failing with below error and EOF is creating problem too.

yr_bkp1=$(date +"%Y")

dt_bkp1=$(date +"%d" --date="yesterday")

mn_bkp1=$(date +"%b")

mo_bkp1=$(echo "$mn_bkp1" | tr '[:lower:]' '[:upper:]')

check_fold_size_bkp1=`du -h /archive/node1/bkp/HRMIS_NODE1_PRODFULL_$yr_bkp1$mo_bkp1$dt_bkp1`

size_bkp1=$(echo $check | head -n1 | awk '{print $1;}')

loc_bkp1=$(echo $check | head -n1 | awk '{print $2;}')

cd /archive/node1/bkp/HRMIS_NODE1_PRODFULL_$yr_bkp1$mo_bkp1$dt_bkp1

clnt_cnt1=$(ls -ltr 

/archive/node1/bkp/HRMIS_NODE1_PRODFULL_$yr_bkp1$mo_bkp1$dt_bkp1/*.gz | wc -l)

export ORACLE_HOME=/apps/oracle/oracle_ee/product/11.2.0/dbhome

/oracle_ee/sqlplus DBA_SCHEMA/sting23ret@CENTREPO @/rmanbkp/exp_bkp_hpay_essdb/test.sql $loc_bkp1 $clnt_cnt1 <<EOF
EOF

When I am trying to execute it is picking only one argument value I am getting below error

2015

15

JAN

13G /archive/node1/bkp/HRMIS_NODE1_PRODFULL_2015JAN15

296

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 16 19:23:01 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

old   1: insert into test (TEST1,TEST2) values ('&1','')

new   1: insert into test (TEST1,TEST2) values ('296','')

1 row created.

Commit complete.
Enter value for 2: old   1: insert into test (TEST1,TEST2) values ('','&2')
new   1: insert into test (TEST1,TEST2) values ('','EOF ')

1 row created.

Commit complete.

Commit complete.

Disconnected from oracle......

When I copy separately the line

/apps/oracle/oracle_ee/product/11.2.0/dbhome/bin/sqlplus LOGIN/PASSWORD@DATABASE @/rmanbkp/scripts/exp_bkp_hpay_essdb_info/exp_bkp_hpay_essdb_ins.sql $loc_bkp1 $clnt_cnt1 <<EOF

it is fetching argument value and inserting to the table. What could be the reason. What change I need to do in the code. Please let me know.

Welcome to stackoverflow.Please try to reduce the code snippets if possible. — Nagama Inamdar
– Nagama Inamdar, Commented Jan 16, 2015 at 14:08
Change your password ASAP as you pasted it along with your code, then put a test in the script to validate that the arguments really contain data before passing on to sqlplus. It will help to narrow down what is happening and verify it is what you expect. — Gary_W
– Gary_W, Commented Jan 16, 2015 at 14:27
I suspect 296 is the value of $clnt_cnt1 so that means that $loc_bkp1 is empty. In any way, you should check that each parameter given is not empty, before using it in a call. Not doing so, results in very weird behaviour, like which you MAY have right now. There's no easy way to tell an SQL file that he must have 1, 2 or whatever parameters (like it is easy in bash/sh/..) — tvCa
– tvCa, Commented Jan 16, 2015 at 14:57
I'm also assuming you actually have some commands between <<EOF and EOF, because otherwise you can just leave that out of the code. You're calling SQLPLUS with a script, with parameters .. that's enough code to run. You can call SQLPLUS without EOF .. — tvCa
– tvCa, Commented Jan 16, 2015 at 15:00

Alex Poole · Accepted Answer · 2015-01-16 15:12:59Z

1

$loc_bkp1 is going to be empty because you have inconsistent naming when you try to set it. You get $check_fold_size_bkp1 from this:

check_fold_size_bkp1=`du -h /archive/node1/bkp/HRMIS_NODE1_PRODFULL_$yr_bkp1$mo_bkp1$dt_bkp1`

But then when you get the next two variables you refer to $check, not $check_fold_size_bkp1. So it looks like you want:

size_bkp1=$(echo $check_fold_size_bkp1 | head -n1 | awk '{print $1;}')
loc_bkp1=$(echo $check_fold_size_bkp1 | head -n1 | awk '{print $2;}')

You might also consider enclosing the arguments in double quotes, in case one ends up blank anyway; and I'd also suggest you use the -s and -l flags to hide the SQL*Plus banner and exit if you are unable to connect to the database:

/oracle_ee/sqlplus -s -l DBA_SCHEMA/... @.../test.sql "$loc_bkp1" "$clnt_cnt1"

The EOF 'heredoc' seems completely redundant in your example code.

answered Jan 16, 2015 at 15:12

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3441224 Over a year ago

Thanks a lot. I missed out those. It working fine now.

user3441224 Over a year ago

Again I am getting the same error. I tried to add more arguments to the sqlplus. Is there any limitaion to that. Below please find the code. and sql script execution output.

user3441224 Over a year ago

/apps/oracle/oracle_ee/product/11.2.0/dbhome/bin/sqlplus -s -l DBA_SCHEMA/************@CENTREPO @/rmanbkp/scripts/exp_bkp_hpay_essdb_info/exp_bkp_hpay_essdb_ins.sql "$size_bkp1" " $size_bkp2" "$loc_bkp1" "$loc_bkp2" "$clnt_cnt1" "$clnt_cnt2" <<EOF EOF

user3441224 Over a year ago

Enter value for size_bkp1: SP2-0546: User requested Interrupt or EOF detected. Commit complete. Commit complete. Please let me know whether full code need to be copied.

Alex Poole Over a year ago

@user3441224 - without seeing the SQL, hard to tell; but you're passing positional parameters so you should be referring to &1, '&3', etc., not &size_bkp1, unless you're explicitly defining that in the script? (You seem to have extra spaces within your double-quoted parameters too, which might cause other problems later).

|

Collectives™ on Stack Overflow

Multiple arguments passed to sqlplus in shell script is not getting accepted

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related