Oracle: dump stored procedure

SELECT text FROM sys.all_source 

 WHERE owner = 'schema-owner'

   AND name = 'package-name'

   AND type = 'PACKAGE BODY'

 ORDER BY line;

Oracle: create index for column that allows NULL

CREATE INDEX table1_nullable_col1_idx
       ON table1 (nullable_col1, 1);

The ", 1" makes Oracle add into the index the record whose nullable_col1 field has a NULL value.

Oracle: update one table with joined condition from another table

UPDATE table1 
       SET t1_col1 = 'something'
       WHERE id IN (SELECT t1.id FROM table1 t1, table2 t2
                                 WHERE t1.id = t2.id
                                 AND t2.t2_col1 = 'some condition');

Oracle DB: Sequence

A sequence is a schema object that can generate unique sequential values.

Create a sequence:
CREATE SEQUENCE eseq
  INCREMENT BY 10;  

Check the current value of the sequence:
SELECT eseq.currval
    FROM DUAL;

Increase the value of the sequence and insert the value to a table
INSERT INTO mytable
    VALUES (eseq.nextval, ......);

Manually change the value of the sequence
ALTER SEQUENCE eseq INCREMENT BY 100;

Oracle: using DATE type in the WHERE clause condition

If the WHERE clause contains a condition of DATE type, we can use TO_DATE() to convert a date string into DATE type. For example:

SELECT * FROM MyTable WHERE DateField > TO_DATE('2019-04-23 21:30:00', 'YYYY-MM-DD HH24:MI:SS');

The second parameter of TO_DATE() method is the format model. To find out the complete list of datetime format elements, follow this link: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#CDEHIFJA

Oracle: insert or update

In this example, there is a table Employee, and the table has columns EmployeeId and PhoneNumber.

If for the employee 123, we want to add her number into the table, or update her number when she is already in the table, we can use this command:

MERGE INTO Employee e 

      USING dual ON (e.EmployeeId = 123)
  WHEN MATCHED THEN 

       UPDATE SET e.PhoneNumber = '555-555-5555'
  WHEN NOT MATCHED THEN 

       INSERT (e.EmployeeId, e.PhoneNumber) 

              VALUES (123, '555-555-5555');