Home » SQL & PL/SQL » SQL & PL/SQL » calling a if block based on parameter (Oracle 10g)
calling a if block based on parameter [message #574505] Fri, 11 January 2013 06:50 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

I need to check the condition based what string I am sending.
Suppose I am calling with
1.'a,b,c' then it should check all the if blocks.
2.'a,b' then it should check only 'a' and 'b' if blocks.
Is it possible to do so?

    
DECLARE
  a VARCHAR2(5):=0;
  b VARCHAR2(5):=0;
  c VARCHAR2(5):=0;
PROCEDURE p1(
    p_str VARCHAR2)
IS
BEGIN
  IF a=0 THEN
    dbms_output.put_line('in A');
  END IF;
  IF b=0 THEN
    dbms_output.put_line('in b');
  END IF;
  IF c=0 THEN
    dbms_output.put_line('in c');
  END IF;
END;
BEGIN
  --which conditions need to be checked.
  p1('a,b,c');
  p1('a,b');
END;



Regards,
Nathan
Re: calling a if block based on parameter [message #574506 is a reply to message #574505] Fri, 11 January 2013 07:16 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

You are excepting this one...
create or replace PROCEDURE p1( p_str VARCHAR2)
IS

a VARCHAR2(5):=0;
  b VARCHAR2(5):=0;
  c VARCHAR2(5):=0; 
BEGIN
  IF instr ('a,b,c','a')>0 THEN
    dbms_output.put_line('in A');
  END IF;
  IF instr ('a,b,c','b')>0 THEN
    dbms_output.put_line('in b');
  END IF;
  IF instr ('a,b,c','c')>0 THEN
    dbms_output.put_line('in c');
  END IF;
END;  
Re: calling a if block based on parameter [message #574543 is a reply to message #574506] Sat, 12 January 2013 00:24 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi Murali,

This is very good solution You have provided.Appreciate your help.Thanks.

Regards,
Nathan
Re: calling a if block based on parameter [message #574548 is a reply to message #574543] Sat, 12 January 2013 07:37 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

How I can fetch the value as 'asha' from variable v2 without assigning it.
declare
v1 varchar2(10):='asha';
v2 varchar2(10);
v3 varchar2(10);
begin
v2:='v1';
select v2 into v3 from dual ;
dbms_output.put_line(v3);
end;


Regards,
Nathan
Re: calling a if block based on parameter [message #574549 is a reply to message #574548] Sat, 12 January 2013 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're at the gates of hell.

Regards
Michel
Re: calling a if block based on parameter [message #574551 is a reply to message #574549] Sat, 12 January 2013 08:12 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi Michel,

I know this is just ridiculous but,Please consider this test case.

create table test1
(v1 varchar2(10));

insert into test1 values('asha');

declare
v1 varchar2(10):='asha';
v2 varchar2(10);
v3 varchar2(10);
str varchar2(50);
c1 sys_refcursor;
begin
v2:='v1';
str:='select '||v2|| ' from test1';
dbms_output.put_line(str);
open c1 for str;
fetch c1 into v3;
dbms_output.put_line(v3);
close c1;
end;



Similarly In this way I want to do for a variable also.Is it possible.

Regards,
Nathan
Re: calling a if block based on parameter [message #574552 is a reply to message #574551] Sat, 12 January 2013 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

what problem are you trying to solve?
Re: calling a if block based on parameter [message #574554 is a reply to message #574551] Sat, 12 January 2013 10:20 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said this is the gates of hell and I will not lead someone to this place.
Whatever you are trying to achieve having this need you do it the wrong way or with the wrong language.

Regards
Michel
Previous Topic: Import excel file to table
Next Topic: How to find tables which does not have specific columns
Goto Forum:
  


Current Time: Mon Oct 20 19:03:16 CDT 2014

Total time taken to generate the page: 0.13216 seconds