Dynamic PL/SQL query, how to ignore null parameters?

Question

I have a PL/SQL procedure with multiple parameters. When a webapp calls the procedure, if it's not using a certain parameter, it passes is as null, ie

procedure test (param1 in varchar2, param2 in varchar2, param3 in varchar2, cursor out sys_refcursor)
...
end procedure test;

I want to make an SQL query where I include the given parameters in the WHERE clause only if the parameter is not null. Is there a way to achieve this in an elegant way, other than building the SQL query in a string and then opening the cursor for that string, like this

vSQL := 'SELECT * from TABLE WHERE something = something_else';
if param1 IS NOT NULL
    vSQL := vSQL || 'AND some_param = ' || param1;

I want to point out that if the parameters are ever exposed to users, this is a classic example of code that has a high risk of SQL*Injection. So you should use DBMS_ASSERT to check your final SQL for good behavior. — Jim Hudson
– Jim Hudson, Commented Feb 8, 2011 at 17:24

Community · Accepted Answer · 2017-04-13 12:42:39Z

1

I have answered a similar question on dba.stackexchange.com

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Feb 8, 2011 at 11:13

René Nyffenegger

40.9k34 gold badges180 silver badges321 bronze badges

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

Comments

RichardTheKiwi · Accepted Answer · 2011-02-08 11:18:52Z

1

You can code it all into a single select

SELECT * from TABLE WHERE something = something_else
AND ((param1 IS NOT NULL AND some_param = param1) OR 1)

answered Feb 8, 2011 at 11:18

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

1 Comment

Jberg Over a year ago

This answer is a nice simple solution when calling named queries from within code where PL typically isn't an option. I like this for times when building criteria queries or piecing together where clauses is overkill.

tbone · Accepted Answer · 2011-02-08 14:35:14Z

Create a test table:

create table testtab
(
name_first varchar2(50),
name_last varchar2(50),
name_middle varchar2(50)
);

insert into testtab values ('Joe', 'Jones', 'A');
insert into testtab values ('Joe', 'Smith', 'A');
insert into testtab values ('Steve', 'Jones', 'B');
insert into testtab values ('Axl', 'Rose', 'C');
insert into testtab values ('Phil', 'McCracken', 'D');
commit;

Create your procedure:

CREATE OR REPLACE procedure ECDATA.get_testtab_rows
(i_name_first in varchar2 default null,
i_name_last in varchar2 default null,
i_name_middle in varchar2 default null,
o_cursor out sys_refcursor
) as

    v_result_cur    sys_refcursor;

begin

    open v_result_cur for
        select * from testtab
        where name_first like nvl(i_name_first, '%')
        and name_last like nvl(i_name_last, '%')
        and name_middle like nvl(i_name_middle, '%')
    ;

    o_cursor := v_result_cur;

end;
/

Then call it like this:

declare

    v_cur sys_refcursor;
    testtab_rec testtab%rowtype;

begin

    get_testtab_rows(i_name_last=>'Jones', o_cursor=>v_cur);
    loop

        fetch v_cur into testtab_rec;
        exit when v_cur%notfound;
        dbms_output.put_line(testtab_rec.name_first || ' ' || testtab_rec.name_middle || ' ' || testtab_rec.name_last);

    end loop;

exception
    when others then raise;
end;

Collectives™ on Stack Overflow

Dynamic PL/SQL query, how to ignore null parameters?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related