A piggy bank of commands, fixes, succinct reviews, some mini articles and technical opinions from a (mostly) Perl developer.

Jump to

Quick reference

Oracle commands for MySQL/PostgreSQL users

Oracle tips for MySQL users:
  • Simple range: SELECT * FROM (SELECT * FROM foo) WHERE ROWNUM <= 100; -- equivalent of MySQL's LIMIT clause, only for start of table
  • Wrong range: SELECT * FROM (SELECT rownum r, f.bar FROM schema.foo f) WHERE r > 100 AND r <= 200; -- Selects a range, but order will be inconsistent, even if you add ORDER BY to inner select
  • Right range:
    SELECT * FROM (
        SELECT q.*, ROWNUM r FROM (
            SELECT * FROM schema.foo ORDER BY id
        ) q
    ) WHERE r >= 100 AND r < 200
    (source)
  • SELECT last_name FROM employees WHERE last_name LIKE '%d_g\_cat%' ESCAPE '\';
    • matches dog_cat, foodig_catbar, etc.
    • _ = any single character
    • % = any characters
    • \_ = literal _ (underscore)
    • ESCAPE '\'; -- set the escape character to \ (backslash)
  • SELECT ...... WHERE REGEXP_LIKE (instance_name, '^Ste(v|ph)en$');
  • ALTER USER foo IDENTIFIED BY "newpassword456!" REPLACE "oldpassword123!"; -- change password (REPLACE is new in 9.2)