call another script based on input parameters 2

[cuetzpalin]

Hi All,

I have a script that has an input for region code. I'm expecting the user to enter NC or SC as the values. Based on those input values I want to be able to call a certain script.

Here's a code snippet:
--------------------------------------------
PROMPT
PROMPT ENTER REGION CODE [FORMAT: NC or SC]
PROMPT
ACCEPT xREGION PROMPT 'Region code: '
PROMPT
SET TERMOUT OFF
DEFINE varREGION = '&xREGION'

SELECT '&varREGION' REGION FROM DUAL;

SELECT CASE WHEN '&varREGION' = 'NC' THEN <CALL SCRIPT>
WHEN '&varREGION' = 'SC' THEN <CALL SCRIPT>
END
FROM DUAL;

-------------------------------------------

Is it possible to do that? I tried placing the path of the script in after the THEN keyword (i.e. @\\path\code.sql)
and it doesn't work.

Please advise!

Thanks!

Ed

 

[SantaMufasa]

Cuetz,

I illustrate one method to achieve your results, below. I created three scripts that could execute as a result of your data entry:[ul][li]CuetzNCScript.sql[/li][li]CuetzSCScript.sql[/li][li]CuetzInvalidScript.sql[/li][/ul]

SQL> get CuetzNCScript
  1* select 'This is Cuetz`s NC Script' from dual;
SQL> get CuetzSCScript
  1* select 'This is Cuetz`s SC Script' from dual;
SQL> get CuetzInvalidScript
  1* select 'Error: You entered neither "NC" nor "SC".' from dual;

I created the following SQL*Plus script and named the script, "tt_523.sql":

set echo off
set feedback off
set verify off
accept my_choice prompt "ENTER REGION CODE [FORMAT: NC or SC]: "
col script_name new_value script_name
set termout off
select decode(upper('&my_choice')
             ,'SC','CuetzSCScript.sql'
             ,'NC','CuetzNCScript.sql'
             ,'CuetzInvalidScript.sql'
             ) script_name from dual;
set termout on
@&script_name

The script prompts the user to enter the two acceptable values, "NC" or "SC".


The SQL*Plus code that uses the "new_value" command directs Oracle/SQL*Plus to populate the SQL*Plus named literal, "script_name", with the results of my "SELECT" statement, which outputs under the alias "script_name".

Lastly, SQL*Plus executes (via "@&script_name") the appropriate script.

So, here is what happens when I invoke "tt_523.sql":

SQL> @tt_523
ENTER REGION CODE [FORMAT: NC or SC]: nc
This is Cuetz`s NC Script
SQL> @tt_523
ENTER REGION CODE [FORMAT: NC or SC]: sc
This is Cuetz`s SC Script
SQL> @tt_523
ENTER REGION CODE [FORMAT: NC or SC]: abc
Error: You entered neither "NC" nor "SC".

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cuetzpalin]

That's slick! Thanks Mufasa!

 

[SantaMufasa]

That's slick!

Cool! The little kid in me loves doing sick things. <grin> (And thanks for the

.)



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Even after too many years to count using SqlPlus and Oracle I am always amazed with what you can do - and that you, Santa, seem to know them all...Another star, that was as sweet a piece of code as I have seen...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear,

You are very generous. Thanks! (To an Old Man facing colon-cancer surgery on Wednesday, it boosts my spirits to have someone whom I respect professionally as much as I do you, Turkbear, say something so kind.)



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
As I said in our other common forum, best wishes and we are all pulling for your speedy recovery...





To Paraphrase:"The Help you get is proportional to the Help you give.."