SQL plus loop in .sql script

Question

I'm new in writing SQL plus scripts and i have an issue. I want to write install script for my PL/SQL utility which ask user some questions (via ACCEPT command) and then list what will be done (via PROMPT) and ask for confirmation (again via ACCEPT). This part is OK, but than I have problem, I need either run some PL/SQL scripts if he accept or ask him all questions again if he reject. Is in SQL plus any option how to do an interactive loop which decide based on variable content whether to proceed and run PL/SQL script or loop back to begining of loop?

in pseudo code I want something like this:

 while ('&confirmation' != 'y'){
    /*ask some questions*/
    ACCEPT info_1 CHAR PROMPT 'text_1';
    ACCEPT info_2 CHAR PROMPT 'text_2';
    ...
    /*write some summary*/
    PROMPT 'text_2';
    /*ask for confirmation */
    ACCEPT confirmation CHAR PROMPT 'are these information correct? (y/n)';
 }
 @skript

Thanks

Bjarte Brandt · Accepted Answer · 2017-08-03 12:42:45Z

2

input.sql

accept username char prompt "username: "
accept password char prompt "password: "

prompt
prompt &username
prompt &password
prompt
prompt

accept confirm char prompt "correct? (y|n): "

column col noprint new_value runscript
select
  decode(lower('&confirm'),
    'y', 'task.sql',
    'n', 'input.sql') col
from dual;

prompt &runscript
@&runscript

task.sql

prompt run my tasks with &username and &password in place

test

me@XE> @input
username: a
password: b

a
b

correct? (y|n): y
old   2:   decode(lower('&confirm'),
new   2:   decode(lower('y'),

task.sql
run my tasks with a and b in place
me@XE> @input
username: a
password: b

a
b


correct? (y|n): n
old   2:   decode(lower('&confirm'),
new   2:   decode(lower('n'),

input.sql
username: etc...

answered Aug 3, 2017 at 12:42

Bjarte Brandt

4,5212 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Silverrook Over a year ago

Thanks, this is good, but what if I have more task after "loop" (for example creating log file or do another prompt), if I do it that way, doesn't it mean that last part will be executed more than ones if user don't confirm it on first try?

Collectives™ on Stack Overflow

SQL plus loop in .sql script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related