Dynamic Pl/sql Query, How To Ignore Null Parameters?

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 var

Solution 1:

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

Solution 2:

You can code it all into a single select

SELECT*fromTABLEWHERE something = something_else
AND ((param1 ISNOTNULLAND some_param = param1) OR1)

Solution 3:

Create a test table:

createtable testtab
(
name_first varchar2(50),
name_last varchar2(50),
name_middle varchar2(50)
);

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

Create your procedure:

CREATEOR REPLACE procedure ECDATA.get_testtab_rows
(i_name_first in varchar2 defaultnull,
i_name_last in varchar2 defaultnull,
i_name_middle in varchar2 defaultnull,
o_cursor out sys_refcursor
) as

    v_result_cur    sys_refcursor;

beginopen v_result_cur forselect*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;