Validating null value in plsql

Is there any other way to handle NULL inside function?? And I think THAT query is doing it.(does anyone else see the dangers in their return code method!!!

that is even WORSE than this: codes - how so very 1980's - I really wish you would not do that.but, as to your code, your analysis is wrong, I don't see how you could come to that conclusion given your existing code. It seems like "negative numbers" are return codes, but yet they use negative numbers in their data!

First, and most important, remember that: you can rest assured that the code represented by "..." will never be executed.

You cannot "fix" the no data found exception - it is for you a fatal error. A column's value can be null unless you defined that column with a NOT NULL constraint, as in: Moving on to PL/SQL, a variable's value can be null unless you declared that variable with a NOT NULL constraint or use the CONSTANT keyword to define your variable as a constant. DECLARE my_name VARCHAR2 (100); BEGIN IF my_name IS NULL THEN DBMS_OUTPUT.put_line ('Name not set'); END IF; END; / Name not set DECLARE my_name VARCHAR2 (100) NOT NULL; BEGIN IF my_name IS NULL THEN DBMS_OUTPUT.put_line ('Name not set'); END IF; END; / PLS-00218: a variable declared NOT NULL must have an initialization assignment DECLARE my_name VARCHAR2 (100) NOT NULL := 'Grandpa'; BEGIN IF my_name IS NULL THEN DBMS_OUTPUT.put_line ('Name not set'); ELSE DBMS_OUTPUT.put_line (my_name); END IF; END; / Grandpa DECLARE my_name CONSTANT VARCHAR2 (100); BEGIN IF my_name IS NULL THEN DBMS_OUTPUT.put_line ('Name not set'); END IF; END; / PLS-00322: declaration of a constant 'MY_NAME' must contain an initialization assignment DECLARE my_name CONSTANT VARCHAR2 (100) := 'Grandpa'; BEGIN IF my_name IS NULL THEN DBMS_OUTPUT.put_line ('Name not set'); ELSE DBMS_OUTPUT.put_line (my_name); END IF; END; / Grandpa Oracle Database currently treats a character value with a length of zero as null.However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.Note: Oracle Database currently treats a character value with a length of zero as null. For example, consider a query that averages the five values 1000, null, null, null, and 2000.However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. Such a query ignores the nulls and calculates the average to be (1000 2000)/2 = 1500. Please refer to DECODE for syntax and additional information.

Leave a Reply