How to get all non null rows using a parameter?

Question

This SQL Server Post was quite helpful. I'm using PL/SQL on Oracle 11g. I want cursor to accept both v_parm1 and v_parm2.

Here's what I've done.

DECLARE 
v_parm1 varchar2(50) := 'TALC';
v_parm2 varchar2(50) := '*';

cursor cursor_with_parms(in_parm_list varchar2)
is 
with mohs_scale as
(select '1' as moh_val , 'TALC' as mineral from dual union all
 select '2' as moh_val , 'GYPS' as mineral from dual union all
 select '3' as moh_val , 'CALC' as mineral from dual union all
 select '4' as moh_val , 'FLUO' as mineral from dual) 
 select moh_val,
        mineral 
 from   mohs_scale
 where
   case  in_parm_list when '*' then  mineral is not null 
   else   mineral = in_parm_list
end;      

BEGIN
for rock in cursor_with_parms(v_parm1) loop
dbms_output.put_line(rock.moh_val || ' ' || rock.mineral);
end loop; 
END;

If I use v_parm1 then return just 1-TALC. If I use v_parm2 then return all 4 rows.

Question: How can I write cursor to accept either v_parm1 or v_parm2?

Patrick Marchand · Accepted Answer · 2013-08-02 20:11:21Z

1

What about a WHERE clause like this:

 WHERE
       (in_parm_list = '*')
    OR (mineral = in_parm_list)

answered Aug 2, 2013 at 20:11

Patrick Marchand

3,45523 silver badges13 bronze badges

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

Comments

Harshit · Accepted Answer · 2013-08-01 22:51:07Z

0

Create a Ref cursor if you want to change the query dynamically.

answered Aug 1, 2013 at 22:51

Harshit

5601 gold badge5 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to get all non null rows using a parameter?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related