Things in SQL Oracle Developer I LIKE (and wish I had in MS SSMS)

1) Ability to filter objects (SPROC/functions/tables) with a LIKE (not a contains) and have multiple filters on at the same time

LIKE 'usp_hv%'
OR   'xsp_hv%'
OR   '%maint'


2) Ability to search within CODE using SQL without opening a separate tool

SELECT type, name, line, text
FROM   user_source
WHERE  UPPER(text) LIKE UPPER('%Text to search for%');

Outer join shortcut? DO NOT USE

Every now and again I find this awful SQL notation. (this is a note so I don't have to google it again)
Terse code is fine as long as you know what all the squiggly bits mean!

outer join shortcut? - dBforums:

*= is a LEFT JOIN
=* is a RIGHT JOIN.
It is a T_SQL extension and was valid up thru SQL 2000 It is no longer available in SQL 2005.
It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

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.

Oracle debug - output a CRLF delimited block of text

v_crlf CHAR(2):=CHR(13) || CHR(10);

v_RemainingText:=v_Orig_Text || v_crlf ;
WHILE length(v_RemainingText) > 1 LOOP
    v_crpos:=instr(v_RemainingText,v_crlf);
    v_ThisText:=substr(v_RemainingText,1,v_crpos-1);
    dbms_output.put_line(v_ThisText);
    v_RemainingText:=substr( v_RemainingText
                           , v_crpos+2
                           , length(v_RemainingText)-(v_crpos+1)
                           );
END LOOP;