Madhu sudhan Reddy Madhu sudhan Reddy - 5 days ago 5
SQL Question

what is the alternative way to check for null with out using is null operator in oracle

I had written an stored procedure where selecting from one table(some columns have null data) and inserting into other table with out duplicates.

part of my stored procedure code:

LOOP
BEGIN
SELECT ID
INTO ROWCOUNT
FROM TBL_EMPLOYEE
WHERE

NAME = TEMPTABLE.NAME
AND AGE =TEMPTABLE.AGE

EXCEPTION
WHEN no_data_found THEN
ROWCOUNT := 0;
END;
IF ROWCOUNT = 0 THEN
INSERT INTO TARGET TABLE ......


In the above piece of code there is null data for some columns(eg: TEMPTABLE.AGE etc).
when there is null value it is throwing no_data_found exception and it is inserting the data. we tried alternatively by putting nvl function which is taking more time.

LOOP
BEGIN
SELECT ID
INTO ROWCOUNT
FROM TBL_EMPLOYEE
WHERE

nvl(NAME,0000) = nvl(TEMPTABLE.NAME,0000)
AND nvl(AGE,0000) =nvl(TEMPTABLE.AGE,0000)

EXCEPTION
WHEN no_data_found THEN
ROWCOUNT := 0;
END;
IF ROWCOUNT = 0 THEN
INSERT INTO TARGET TABLE ......


Can any one suggest any alternative how to do null check with equals to operator.
I have tried it with LIKE as well but it did not work.

Answer

I think you want to do this:

NAME = VALUE {in case of Some Value}

and

NAME IS NULL {in case the value is null}

ORACLE doesn't handles it by its own. I think this is a logical condition and this should be handled logically. Let me know if I have answered/understood you correctly?

Comments