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
You need to code this a different way:

#!/bin/ksh

sqlplus oracle/oracle @myscript.sql

Now put all of your SQL*Plus code (that text between the EOF markers) into myscript.sql:

 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';

NOW your SQL*PLus script won't 'blow by' your ACCEPT, as you've made the session interactive. Using 'here documents', as in your original script, causes SQL*Plus to read all input directly from the script, stopping at *nothing* until the matching marker is reached (in this case your EOF tag)and results in a non-interactive execution of the code. This is excellent for things like database startup and shutdown scripts, and any SQL*Plus action requiring *no* user input whatsoever. Other methods described, such as having the shell ask for your input prior to executing your embedded block of code, also would work, and an example has been already posted. It matters not how you choose to implement this; the long and the short of it is you can't ask a 'here document' to be interactive.

David Fitzjarrell Received on Thu Feb 03 2005 - 07:43:59 CST