Re: if else logic in SQLPlus scripts
From: Richard Spee <rhpspee_at_wxs.nl>
Date: Tue, 24 Sep 2002 00:51:40 +0200
Message-ID: <amo6c4$qcp$1_at_reader12.wxs.nl>
> I know I saw a treatment of this someplace on the web, but I cannot find it
> now that I look for it.
>
> I would like to do some simple if/then/else logic from within my SQL plus
> scripts.
>
> Does anybody have any examples of this?
>
> Thanks.
>
> --steve
>
>
> Received on Tue Sep 24 2002 - 00:51:40 CEST
Date: Tue, 24 Sep 2002 00:51:40 +0200
Message-ID: <amo6c4$qcp$1_at_reader12.wxs.nl>
This is an example in which pieces of
code are executed depending
on the input of a user. It combines pl/sql with
spool-files and sqlplus commands.
As you will notice there are some serious limitations to this solution.
You will problaby get the following
error
SP2-0309: SQL*Plus
command procedures may only be nested to a depth of 20
Did not have time to look into it.
Perhaps other people have a solution for
this.
This is the script. Put it in a file and execute
it in SQL*Plus
set serveroutput on size 1000000
--
set define off
set termout off
set echo off
set verify off
set feedback off
--
create or replace procedure wr
(var_line in varchar2
)
is
begin
dbms_output.put_line(var_line);
end;
/
create or replace package globals
as
random_value number;
user_value number;
next_script varchar2(50);
try_again char(1);
end;
/
--
-- The file ready.sql does not have to be generated, can be an os-file
--
spool ready.sql
exec wr('PROMPT Thank you for playing');
spool off
--
-- The file tolow.sql does not have to be generated, can be an os-file
--
spool tolow.sql
exec wr('PROMPT');
exec wr('accept user_value PROMPT ''Your value is to low, try again: ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file tohigh.sql does not have to be generated, can be an os-file
--
spool tohigh.sql
exec wr('PROMPT');
exec wr('accept user_value PROMPT ''Your value is to high, try again: ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file ok.sql does not have to be generated, can be an os-file
--
spool ok.sql
exec wr('PROMPT');
exec wr('accept try_again PROMPT ''Correct. Want to try again(Y/N): ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.try_again := ''&try_again'';');
exec wr(' if globals.try_again = ''N''');
exec wr(' then');
exec wr(' globals.next_script := ''ready'';');
exec wr(' else');
exec wr(' globals.next_script := ''main'';');
exec wr(' end if;');
exec wr('end;');
exec wr('/');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file main.sql does not have to be generated, can be an os-file
--
spool main.sql
exec wr('declare');
exec wr('var_value number;');
exec wr('begin');
exec wr('select trunc(dbms_random.value(1,10))');
exec wr('into var_value');
exec wr('from dual;');
exec wr('globals.random_value := var_value;');
exec wr('end;');
exec wr('/');
exec wr('accept user_value prompt ''A number between 1 and 10: ''');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file compare.sql does not have to be generated, can be an os-file
--
spool compare.sql
exec wr('begin');
exec wr(' if globals.random_value = globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''ok'';');
exec wr(' elsif globals.random_value > globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''tolow'';');
exec wr(' elsif globals.random_value < globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''tohigh'';');
exec wr(' end if; ');
exec wr('end;');
exec wr('/');
spool off
--
-- The file gen_next.sql does not have to be generated, can be an os-file
-- The files next1 and next2 must be generated
--
spool gen_next.sql
exec wr('spool next1.sql');
exec wr('exec wr(''spool next2.sql'')');
exec wr('exec wr(''exec wr(''''start ''''||globals.next_script);'');');
exec wr('exec wr(''spool off'');');
exec wr('exec wr(''start next2'');');
exec wr('spool off');
spool off
--
set define off
set termout off
set echo off
set verify off
set feedback off
--
create or replace procedure wr
(var_line in varchar2
)
is
begin
dbms_output.put_line(var_line);
end;
/
create or replace package globals
as
random_value number;
user_value number;
next_script varchar2(50);
try_again char(1);
end;
/
--
-- The file ready.sql does not have to be generated, can be an os-file
--
spool ready.sql
exec wr('PROMPT Thank you for playing');
spool off
--
-- The file tolow.sql does not have to be generated, can be an os-file
--
spool tolow.sql
exec wr('PROMPT');
exec wr('accept user_value PROMPT ''Your value is to low, try again: ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file tohigh.sql does not have to be generated, can be an os-file
--
spool tohigh.sql
exec wr('PROMPT');
exec wr('accept user_value PROMPT ''Your value is to high, try again: ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file ok.sql does not have to be generated, can be an os-file
--
spool ok.sql
exec wr('PROMPT');
exec wr('accept try_again PROMPT ''Correct. Want to try again(Y/N): ''');
exec wr('PROMPT');
exec wr('begin');
exec wr(' globals.try_again := ''&try_again'';');
exec wr(' if globals.try_again = ''N''');
exec wr(' then');
exec wr(' globals.next_script := ''ready'';');
exec wr(' else');
exec wr(' globals.next_script := ''main'';');
exec wr(' end if;');
exec wr('end;');
exec wr('/');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file main.sql does not have to be generated, can be an os-file
--
spool main.sql
exec wr('declare');
exec wr('var_value number;');
exec wr('begin');
exec wr('select trunc(dbms_random.value(1,10))');
exec wr('into var_value');
exec wr('from dual;');
exec wr('globals.random_value := var_value;');
exec wr('end;');
exec wr('/');
exec wr('accept user_value prompt ''A number between 1 and 10: ''');
exec wr('begin');
exec wr(' globals.user_value := &user_value;');
exec wr('end;');
exec wr('/');
exec wr('start compare');
exec wr('start gen_next');
exec wr('start next1');
exec wr('start next2');
spool off
--
-- The file compare.sql does not have to be generated, can be an os-file
--
spool compare.sql
exec wr('begin');
exec wr(' if globals.random_value = globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''ok'';');
exec wr(' elsif globals.random_value > globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''tolow'';');
exec wr(' elsif globals.random_value < globals.user_value');
exec wr(' then');
exec wr(' globals.next_script := ''tohigh'';');
exec wr(' end if; ');
exec wr('end;');
exec wr('/');
spool off
--
-- The file gen_next.sql does not have to be generated, can be an os-file
-- The files next1 and next2 must be generated
--
spool gen_next.sql
exec wr('spool next1.sql');
exec wr('exec wr(''spool next2.sql'')');
exec wr('exec wr(''exec wr(''''start ''''||globals.next_script);'');');
exec wr('exec wr(''spool off'');');
exec wr('exec wr(''start next2'');');
exec wr('spool off');
spool off
set define on
set termout on
set echo off
set feedback off
set termout on
set echo off
set feedback off
start main
--set feedback on
--set echo on
--set verify on
--set echo on
--set verify on
"Steve Mitchell" <stevem_at_hdcsi.com> wrote in
message news:OjLi9.823$d55.60410098_at_newssvr21.news.prodigy.com...
> > I know I saw a treatment of this someplace on the web, but I cannot find it
> now that I look for it.
>
> I would like to do some simple if/then/else logic from within my SQL plus
> scripts.
>
> Does anybody have any examples of this?
>
> Thanks.
>
> --steve
>
>
> Received on Tue Sep 24 2002 - 00:51:40 CEST