oracle plsql parameter default null

Question

i have to use parameter in a column and column is not nullable. but parameter will be by default null. how do i control the null value. because when i am not giving value, it is considering value as null and my script is getting failed. would appreciate your help example -

create procedure test1 (p_1 number default null) as begin select col1, col2 from table1 where col1=p_1 -- default NULL;
end; /

enter code here

Well, what value do you want to put in the column if one isn't supplied in the call? You have to have something to use instead of null. Or do you want to select all values if the argument is null? (You also have to select into something...) — Alex Poole
hi alex, if i dont put any value, statement wll just ignore and execute without where conditions. basically i have to use 2 parameter, 1 i will put value and another one is default null. so it will be like ,,,,, whrere col1=p_1 and col2 = p_2. and p_2 i will put value and p_1 will be default null — Ronnie710

Alex Poole Alex Poole · Accepted Answer · 2016-10-24T20:23:40

You can use an or to make the query filter conditional:

select col1, col2
bulk collect into some_collectin
from table1
where p_1 is null or col1 = p_1

If p_1 is null, either because no argument was passed or the argument was null, then the first part matches - for all rows. If it is not null then the first part does not match for any rows, so the equality check applies and filters for only the rows that do match the argument.

If you have other conditions then remember to use parentheses to make sure the overall logic is right:

where (p_1 is null or col1 = p_1)
and ...

oracle plsql parameter default null

1 Answers