How to ask a user if he wants to commit/rollback changes with batch/sql file

Question

I'm currently making an SQL script that makes a number of changes in the database and I'd like the user to confirm the results for each step.

code:

SET ECHO ON
SET AUTOCOMMIT OFF
SET EXITCOMMIT OFF
-- make changes
update plan_table
set statement_id = '3'
where statement_id = '2';
-- check if correct
select statement_id from plan_table;
-- ask confirmation
ACCEPT response CHAR DEFAULT 'n' PROMPT 'Result ok? - y/n: ';
  if lower(&response) = 'y' then (
    update plan_table
    set statement_id = '4'
    where statement_id = '3';
    -- check if correct
    select statement_id from plan_table;
    -- ask confirmation
    ACCEPT response CHAR DEFAULT 'n' PROMPT 'Result ok? - y/n: ';
     if lower(&response) = 'y' then commit;
     else rollback;
     end if;
  )
  else rollback;
  end if;
/
exit;

The changes work, but I always get an error when I try to get verification (the Accept response parts):

SQL>     -- ask confirmation
SQL>     ACCEPT response CHAR DEFAULT 'n' PROMPT 'Result ok? - y/n: ';
Result ok? - y/n: SQL>    if lower(&response) = 'y' then commit;
SP2-0734: unknown command beginning "if lower(&..." - rest of line ignored.
SQL>      else rollback;
SP2-0734: unknown command beginning "else rollb..." - rest of line ignored.
SQL>      end if;
SP2-0042: unknown command "end if" - rest of line ignored.
SQL>   )
SP2-0042: unknown command ")" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>   else rollback;
SP2-0734: unknown command beginning "else rollb..." - rest of line ignored.
SQL>   end if;
SP2-0042: unknown command "end if" - rest of line ignored.
SQL> /

Can anyone tell me what I'm doing wrong?

error when using case:

Enter value for userreply: old   7: case &userReply when 'y' then exit commit
new   7: case y when 'y' then exit commit
ACCEPT userReply PROMPT 'Result ok? - y/n: '
       *
ERROR at line 6:
ORA-06550: line 6, column 8:
PLS-00103: Encountered the symbol "USERREPLY" when expecting one of the
following:
:= . ( @ % ;

Thanks

This is very hard to achieve in SQL Plus - see http://stackoverflow.com/questions/1870670/how-to-loop-accepting-user-input-with-pl-sql?rq=1 for some ideas. — Tony Andrews, Dec 20 '13 at 13:43
@a_horse_with_no_name I tried replacing if with a case statement, but I'm still getting the error. Also, doesn't SQL plus use PL/SQL (it's an oracle product)? — Andreas, Dec 24 '13 at 10:57
can you post the error you are getting after using the case statement instead of IF ?? — psaraj12, Dec 26 '13 at 08:04
PL/SQL is exclusively used for stored procedures (or functions). Regular SQL statements are just that: SQL — , Dec 27 '13 at 12:26

score 4 · Accepted Answer · answered Dec 24 '13 at 12:01

OK, as a fun exercise I have implemented your requirements in SQL scripts. I do not recommend this approach, it is over complicated and frankly a bit silly! The best way would be to write an application in the development environment of your choice - could be APEX, Forms, ASP, Pro*C, ...

I had to create a number of scripts:

1) do_update.sql:

-- make changes
update plan_table
set statement_id = '&1.'
where statement_id = '&2.';

-- check if correct
select statement_id from plan_table;

-- ask confirmation
ACCEPT response CHAR DEFAULT 'n' PROMPT 'Result ok? - y/n: ';

-- Based on confirmation set prefix to 'do' or 'dont'
set term off verify off

column prefix new_value prefix

select case upper('&response.')
       when 'Y' then 'do'
       else 'dont'
       end as prefix
from dual;

set term on

2) dont_update.sql:

-- don't make changes

3) do_commit.sql:

commit;

4) dont_commit.sql:

rollback;

5) main.sql:

@@do_update 3 2
@@&prefix._update 4 3
@@&prefix._commit

Now in SQL Plus just run main.sql:

SQL> @main

1 row updated.


STATEMENT_ID
------------------------------
1
3
3

Result ok? - y/n: y

2 rows updated.


STATEMENT_ID
------------------------------
1
4
4

Result ok? - y/n: y

Commit complete.

Of course, this is just based on your simple example. For a more realistic case the number of scripts would be much larger. I would not do this!

score 1 · Answer 2 · answered Dec 27 '13 at 05:58

1

First of all ,you are writing in the pl/sql syntax. There are no conditional clauses in sql .Try to correct the syntax and start writing in the pl/sql syntax. As far as I see it, the logic is all clear .

answered Dec 27 '13 at 05:58

saurabhk

140
1
4
14

How to ask a user if he wants to commit/rollback changes with batch/sql file

2 Answers2