I'm pretty sure you have not covered this question before on AskTom and am fairly confident that there's no easy solution detailed in the manual, so I thought I would take this rare opportunity when a chance to ask you a question arises to put the following forward for your consideration.
By the way, before I start, why is availability in Europe for your new book so poor ? Amazon are quoting 10-11 days over here !
Anyhow, here goes :
- Let's say we have a stored procedure that accepts a couple of input parameters and chucks out a REF CURSOR after processing :
PROCEDURE test (param1 in varchar2, param2 in VARCHAR2, param3 out p_cursor) IS
BEGIN
{SOME CODE GOES HERE}
{EXCEPTIONS GO HERE}
END test;
- Now, let's say I'm developing a web application, this will involve a whole bunch of PL/SQL packages and procedures ...communication between the webapp and the Oracle database can only occur via these stored procedures (in order to maximise performance, security and avoid sloppy programmers blaming the database !)
The question of the day is :
How can I efficiently prevent nulls being submitted as input parametters and effetivley deal with them before Oracle has a chance to throw an ORA-* error !
Thanks a lot. And keep up the good work.
the database wont throw an ORA-xxxx exception due to nulls in general?
But is it that you want
a) to build a custom query based on the supplied inputs (skipping over nulls)
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>
b) to prevent this routine from being invoked with NULLS?
ops$tkyte@ORA9IR2> create or replace package my_pkg
2 as
3 subtype mystring is varchar2(4000) NOT NULL;
4 procedure p( p_input mystring );
5 end;
6 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body my_pkg
2 as
3
4 procedure p( p_input mystring )
5 is
6 begin
7 null;
8 end;
9
10 end;
11 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec my_pkg.p( 'Hello world' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec my_pkg.p( NULL );
BEGIN my_pkg.p( NULL ); END;
*
ERROR at line 1:
ORA-06550: line 1, column 17:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
is an approach....