How to restrict NULL input parameters into oracle stored procedure

Question

I have writtent some Oracle storedprocedures in these there are more then 20 input parameters and from them morethen 10 parameters are required , I want all with some value and do not want to accept null values for that , Is there anything that I can declare in the Procedure defination itself which can restrict null input parameter or Will I have to check for each value and Raise the exception if the required value is null ?

Answer 1

I know this is an old question, but there is another option (described here):

SUBTYPE varchar2_not_null IS VARCHAR2 NOT NULL;

You can define this type (and number_not_null, etc) either in the same package as your stored procedures, or in their own package if you want to use them in lots of places. You can then declare parameters of being these types.

If NULL gets passed as an argument, you'll get a very useful error message:

cannot pass NULL to a NOT NULL constrained formal parameter

Answer 2

In PL/SQL I don't know of a way around checking each one.

If you are calling the stored procedure from an external library, that library might have that functionality. This is probably not likely because frequently NULL input parameters are required.

You could make a helper PL/SQL procedure that, given a value, will raise an exception if it is null to save on redundant code. You could then write a chunk of perl/python/groovy that would slurp up your procedure declaration and crank out these calls to your null check procedure.