CODEWITHSUNDEEP
bashoracle-databaseshellsqlplus
user3436389
user3436389

Reputation: 71

Error while calling a .sql script from shell script

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

Answers (1)

Alex Poole
Alex Poole

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

Related Questions