Re: ACCEPT in SQL*Plus script

Andy Kent wrote:
> I'm trying to run the following shellscript containing SQL*Plus code.
> The ACCEPT statement is completely ignored and at no point does &yn get
> prompted for. The script just flies through as though the ACCEPT wasn't
> there.
>
> I know there are limitations on how you can mix SQL*Plus, SQL and
> PL/SQL code but I can't work out what I need to do to get the script to
> work as intended. Can anyone help please?
>
> It works fine if I just paste the code text into a SQL*Plus session.
>
>
> #!/bin/ksh
> sqlplus oracle/oracle <<EOF
> define yn=n
>
> select * from test.tab1
> where status = 'P'
> and id in
> (select id from test.tab2);
>
> accept yn default 'n' prompt "Update rows? (y/n)"
>
> update test.tab1
> set status = null
> where status = 'P'
> and id in
> (select id from test.tab2)
> and '&yn' = 'y';
>
> EOF
>

sqlplus oracle/oracle <<EOF
...snip!
accept yn default 'n' prompt "Update rows? (y/n)" EOF Your shell doesn't stop until EOF is found... Make it an interactive session.

-- 
Regards,
Frank van Bortel