Validating null value in plsql sex and the city manhattan dating game
My code is like; I've a hard coded value in table for Product_Cod=-2, so I should never get No_Data_Found exception if Product Cod is NULL.
I checked, for Company Cod and it is NOT NULL when I call function. I find that NULL values can be handled by ISNULL but some how it doesn't work in my case. I think this query: SELECT PRODUCT_KEY in vi_Product Key from DIM_PRODUCT WHERE Product_Cod=-2;is just as capable of raising no_data_found as the other one.
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.
If a NULL value could be passed to my_proc for the value_in parameter, that inequality will never evaluate to true.
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.
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.
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.
If you ask me - that function should have NO exception block.