Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4250
  • Last Modified:

If then in sqlplus

Currently, I have the following code in SQL script runs in sqlplus:

DEFINE REGION         = &&1
EXECUTE MY_PROC


I like to add IF THEN condition.  Please remember this is not pl/sql.  How to do?  Thanks.

DEFINE REGION         = &&1

IF REGION ='USA' THEN
EXECUTE MY_PROC
END IF




0
ewang1205
Asked:
ewang1205
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
sathyagiriCommented:
If it was a function you could use some thing like

select decode('&&REGION','USA',MY_FUNC) from dual;

0
 
sathyagiriCommented:
You could probably create a wrapper function that will call your stored proc and then use the above select statement

create or replace my_func returns number
is
begin
my_proc;
return null;
end;
/

Then use
select decode('&REGION','USA',MY_FUNC) from dual;
0
 
actonwangCommented:
It is simple. you don't need to any wrapper function or extra step. sql*plus wil be enough for you to do it.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
actonwangCommented:
the following scriplet will do the logic for you


////////////////////////////////////////
DEFINE REGION=&&1

set feedback off echo off verify off

SPOOL  exec.sql

SELECT 'EXEC MY_PROC'
FROM DUAL
WHERE '&REGION' = 'USA';

SPOOL OFF

@exec.sql
0
 
MohanKNairCommented:
SQL> EGION varchar2(100);

SQL>  :REGION := 'USA'; end;
/

SQL> begin
IF :REGION ='USA' THEN
MY_PROC;
END IF;
/
0
 
ewang1205Author Commented:

actonwang :  I tried the following and returns EXEC MY_PROC as value, it don't EXECUTE MY_PROC.  

SELECT 'EXEC MY_PROC' FROM DUAL WHERE 1=1;


MohanKNair :  I don't want to use BEGIN and END in the SQL
0
 
ewang1205Author Commented:
I found a solution.  I change the MY_PROC to accept in parameter value and use if/then checking the REGION value then run like the following:

EXECUTE MY_PROCE(REGION)
0
 
actonwangCommented:
>>actonwang :  I tried the following and returns EXEC MY_PROC as value, it don't EXECUTE MY_PROC.  

SELECT 'EXEC MY_PROC' FROM DUAL WHERE 1=1;

you'd have to use my whoe script, not just that ONE sql query:

///////////////
DEFINE REGION=&&1

set feedback off echo off verify off

SPOOL  exec.sql

SELECT 'EXEC MY_PROC'
FROM DUAL
WHERE '&REGION' = 'USA';

SPOOL OFF
0
 
MohanKNairCommented:
ewang1205,

sql*plus is not procedural and it is better to code if..then..else clauses within pl/sql.

Mohan
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now