Reputation: 71
Folks,
I am trying to call an sql script with 1 parameter from shell script. My scripts are as follows
tab.sql
set lines 500
col file_name for a80
select file_name,bytes/1024/1024,maxbytes/1024/1024
from dba_data_files
where tablespace_name=upper('&TAB_NAME');
/
and my shell script is test.sh is
#!/bin/bash
LOC=`pwd`
echo -n "Enter the name of the Tablespace: "
read -r TAB_NAME
sqlplus "/ as sysdba" <<- EOF
@$LOC/tab.sql $TAB_NAME
EOF
exit
When I execute the script this is what I am getting
sh test.sh
Enter the name of the Tablespace: users
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 16 14:17:45 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> Enter value for tab_name:
SP2-0546: User requested Interrupt or EOF detected.
Enter value for tab_name:
SP2-0546: User requested Interrupt or EOF detected.
SQL> Disconnected
Can anyone tell me what is wrong here? I searched on net regarding this SP2-0546 but didn't resolved my issue. I am using 11.2.0.4 db on OLE-6
Thanks
Upvotes: 0
Views: 2539
Reputation: 191570
You're mixing up named and positional substitution variables, and possibly confusing them with shell variables a bit too. You can refer to the argument passed on the SQL*Plus command line positionally:
select file_name,bytes/1024/1024,maxbytes/1024/1024
from dba_data_files
where tablespace_name=upper('&1');
/
If you wanted to you could define a named substitution variable; possibly overkill here but can be useful for clarity in bigger scripts:
define TAB_NAME=&1
select file_name,bytes/1024/1024,maxbytes/1024/1024
from dba_data_files
where tablespace_name=upper('&TAB_NAME');
/
or to simplify later references a bit:
define TAB_NAME=upper('&1')
select file_name,bytes/1024/1024,maxbytes/1024/1024
from dba_data_files
where tablespace_name=&TAB_NAME;
/
Note that in this version the reference to &TAB_NAME
is not enclosed in single quotes, as they are applied during the substitution.
I can be helpful to set verify on
while testing/debugging, then set verify off
for real use to hide the noise. You might also find it useful to include the -s
flag in the sqlplus
call to hide the banner text. You don't even need a heredoc in this case, you can run the script directly from the command line too:
sqlplus -s "/ as sysdba" @$LOC/tab.sql $TAB_NAME
(Connecting as sys
for routine queries isn't ideal; it would be better to have a normal user with privileges to see the data dictionary tables you need to query, but that's a different topic...)
Upvotes: 1