Validating null value in plsql
First, and most important, remember that: you can rest assured that the code represented by "..." will never be executed.Note: NULL in the above code is a literal value with a non-value of null.Oracle also considers two nulls to be equal if they appear in compound keys.That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.If a NULL value could be passed to my_proc for the value_in parameter, that inequality will never evaluate to true.That might be fine, but you need to give consideration very deliberately to these questions: "Could a null value be passed in here? " Then take the necessary steps to bulletproof your code from nulls (see last section in this blog post for details). Nulls work the same in SQL and PL/SQL and, as is the case with so much else in PL/SQL, you should apply the documented descriptions from SQL to PL/SQL unless otherwise noted, so here's the doc on Nulls in SQL.PUT_LINE( NVL(p_Product Cod,'abc') ); 7 IF p_Product Cod IS NULL 8 THEN 9 dbms_output.put_line( 'hey, this was NULL' ); 10 select 1 into vi_product Key from dual where 1=0; 11 ELSE 12 dbms_output.put_line( 'hey, this was NOT NULL exec dbms_output.put_line( f_getproduct Key( 123, null ) ); abc hey, this was NULL BEGIN dbms_output.put_line( f_getproduct Key( 123, null ) ); END; * ERROR at line 1: ORA-01403: no data found ORA-06512: at "OPS$TKYTE. I did little change to my code and it works but I don't know exactly why???F_GETPRODUCTKEY", line 9 ORA-06512: at line 1 Hi Tom, Thanks for the answer. My Code is FUNCTION F_GET_PRODUCT_KEY (pi_COMPANY_NUM IN DIM_PRODUCT.
PUT_LINE( NVL(p_Product Cod,'abc') ); 7 IF p_Product Cod IS NULL 8 THEN 9 dbms_output.put_line( 'hey, this was NULL' ); 10 ELSE 11 dbms_output.put_line( 'hey, this was NOT NULL PLSQL is NOT executing the else, you are getting no data found from your IF/THEN if you simply comment out your WHEN NO_DATA_FOUND, allowing the code to fail naturally and normally, you'll get a neat print out of the actual line that causes the FAILURE.
The Ask TOM team will be busy at Open World this month.
If you are at the conference, drop into the Groundbreaker area and say Hello.
BEGIN IF '' IS NULL THEN DBMS_OUTPUT.put_line ('Null'); END IF; IF '' IS NOT NULL THEN DBMS_OUTPUT.put_line ('Not'); END IF; IF NULL IS NULL THEN DBMS_OUTPUT.put_line ('Null'); END IF; IF NULL IS NOT NULL THEN DBMS_OUTPUT.put_line ('Not'); END IF; END; But we encourage you to not write code that assumes that this will always be the case.
Hey, having said that, the Oracle Database team has a very strong history of not changing behavior in new releases that causes problems in the 1,000,000s of lines of existing SQL and PL/SQL code out there "in the wild." But I suggest that you do not.