SQL PLUS Command 'ACCEPT' is not waiting for user input with sh shell script

Shell Programming and Scripting
1

Dear All,

The sqlplus 'Accept' command is not waiting for user input when I include the command within a shell script.

Note: The 'Accept' command is working fine if I execute it in a SQLPLUS Prompt.

Please fins the below sample script which i tried.

SCRIPT:
--------

#! /bin/sh
 
sqlplus -s uname/pwd@samplDB << EOF
 
set heading off
set feedback off
 
ACCEPT X PROMPT 'ENTER X: '
 
select * from sam_tab where s_name like '%&X%';
 
exit
 
EOF

OUTPUT:
---------

The above script is not waiting for the user to key in the input.

But, when i tried the below SQL Codes in SQLPLUS Editor it works as expected.

----
ACCEPT X PROMPT 'ENTER X: '

select * from sam_tab where s_name like '%&X%';
------

Can anyone help me out of this??

Thank you all for your valuable time and Answers..
:b:
[/SIZE]

2 
#!/bin/ksh
echo "enter a value \c"
read value
printf "'%s'"  $value | read X
sqlplus -s uname/pwd@somedb << EOF
set heading off
set feedback off

 
select ${X} from dual;
 
exit
 
EOF

One way to do this.

3

Or:
first script:

#! /bin/sh

sqlplus -s uname/pwd@samplDB << EOF

start mysqlscript
exit;
EOF

in mysqlscript.sql:

WHENEVER SQLERROR EXIT


set heading off
set feedback off

select * from sam_tab where s_name like '&X';
 
exit
4

Thank you for your responses.

But, I am looking for the script to do the below requirement

sqlplus -s uname/pwd@samplDB << EOF
 
set heading off
set feedback off
 
ACCEPT X PROMPT 'ENTER X: '
 
select * from sam_tab where s_name like '%&X%';
here I want to get the user input again to delete the paricular row.--

ACCEPT is_ok PROMPT 'Shall I delete the Row? [Y/N] : '

-- here i want execute the delete stamt if the user enters "Y" 
if [ &is_ok == "Y" ] Then
delete from sam_tab where s_name like '%x%';
end if;

exit
EOF

please advice me if this is possible. I tried with this ACCEPT Statement.
It is prompting the given comments (like Enter X: ) but, it is not waiting user to enter the inputs.

please advice.. how can i acheive this..

5

Hi Vbe,

Thanks for the solution..

I have tried the script as of your solution.

But, still it is not waiting for the user to enter the input.
please verify my script below and please advice.

t1.sql is as follows:-

WHENEVER SQLERROR EXIT
 
set heading off
set feedback off
select * from sam_db where no=&inp;
exit

and t1.sh is as follows:-

#! /bin/sh
sqlplus -s uname1/pwd1@testDB << EOF
start t1.sql
exit;
EOF

when i execute the script, i got the respond like below:-

Enter value for shall_i_show_you: old 1: 
select * from sam_db where no=&inp
new 1: 
select * from sam_db where no=exit;

can you suggest anything for me..