Oracle VS SQL debug

Can you spot the difference?

MS SQL

    SELECT PATNT_REFNO, PATAL_REFNO
    FROM TMP_PATSEARCH_FPATID

Oracle

    DBMS_OUTPUT.PUT_LINE('PATNT_REFNO'
            || CHR(9) || 'PATAL_REFNO');
    FOR c1 IN ( SELECT PATNT_REFNO, PATAL_REFNO
                FROM TMP_PATSEARCH_FPATID )
    LOOP
        DBMS_OUTPUT.PUT_LINE( TO_CHAR(c1.PATNT_REFNO)
                 || CHR(9) || TO_CHAR(c1.PATAL_REFNO) );
    END LOOP;


Why Oracle Sux

DECLARE
v_First VARCHAR2(20) := NULL;
v_Second VARCHAR2(20) := '';
v_Third VARCHAR2(20) := 'Something';
v_Blank VARCHAR2(2) := '';
v_Junk VARCHAR2(2) := '&~';
BEGIN
-- you need to run the following once, on it's own to make DBMS_OUTPUT.PUT_LINE work
-- what a shame there is no PRINT statement like a real database
-- SET SERVEROUTPUT ON;

-- proving NVL does not work with '' as the value
IF NVL(v_First,'')  = '' THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = "" Works'); END IF;
IF NVL(v_Second,'') = '' THEN DBMS_OUTPUT.PUT_LINE ( '"" = "" Works'); END IF;
IF NVL(v_Third,'') <> '' THEN DBMS_OUTPUT.PUT_LINE ( 'Something != "" Works'); END IF;
-- proving NVL does not work with a variable containing ''
IF NVL(v_First,v_Blank)  = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Blank Works'); END IF;
IF NVL(v_Second,v_Blank) = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( '"" = Blank Works'); END IF;
IF NVL(v_Third,v_Blank) <> v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Blank Works'); END IF;
-- proving that Oracle is junk, as other <> '' values do the trick
IF NVL(v_First,v_Junk)  = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Junk Works'); END IF;
IF NVL(v_Second,v_Junk) = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( '"" = Junk Works'); END IF;
IF NVL(v_Third,v_Junk) <> v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Junk Works'); END IF;
END;
/
 
RESULT:
NULL = Junk Works
"" = Junk Works
Something != Junk Works

 

Wot - me biased?

SQL Server - Oracle FAQ: "SQL Server"

Possibly the most xenophobic piece of community written drivel I have ever seen.