Some useful Oracle bits and bobs
Now I have to migrate this thing I'm doing from SQL Server to Oracle, there are of course all sorts of differences that I need to take care of. Dates/times in particular may be a pain, but I haven't got into them yet.
What I have found out is this.
The way you define and retrieve unique ID fields (cf. IDENTITY column in SQL server) is as follows:
You need to define a SEQUENCE which will be used to get the next available ID number, then you have to store that directly in the table you are updating.
E.g.
To define a sequence:
CREATE SEQUENCE PAUL START WITH 1 INCREMENT BY 1 NOCACHE;
(not sure if the NOCACHE is strictly required but may be it's a useful precaution?)
To get the next sequence value for use in a table:
SELECT PAUL.NEXTVAL FROM DUAL;
(note that this "FROM DUAL" syntax is what you always use in Oracle to run a statement which is not for a particular table. You can use this to test run a statement if you don't have a table, e.g. for checking a function -example SELECT SYSDATE FROM DUAL - returns the date without it being in a particular table).
Then INSERT/UPDATE the value you get into the key column of your table...
ROWID is the quickest way to find Oracle data, e.g.
SELECT ROWID FROM PAUL_TEST;
This gives actual block/offset addresses which directly point to your data!
Not sure where you would actually want to use this though...
