Very easy if you don't also need gold mines.
(1) Create a custom goldmine that can be placed in water (i.e. an oil field / derrick)
(2) Create custom workers which are ships that act as oil tankers.
(3) Create a shipyard building which acts as a drop off point for oil.
If that's too simple, a second solution is to code the behavior required for oil gathering. How does this work?
(1) Assign each oil field a region to detect when a tanker enters it.
(2) When a tanker enters an oil field, add oil to the tanker (visually if you want).
(3) Order the tanker to return to a viable shipyard / town hall.
(4) When the tanker is in X range of that target town hall, add the oil to the player, performing any visuals necessary, e.g. +10 (in black floating text or something).
(5) Order the tanker to move to the closest oil region controlled by the player
(6) Go back to (1)
Note you'll also need a custom spell that allows the tanker to target a valid oil field. That will start the entire loop until it's ordered otherwise.