Plymouth State University uses Oracle heavily due to its Student Information System of choice – SungardHE Banner. As such, I play around in Oracle a lot (sometimes a lot more than I’d like) and I occasionally find functionality that seems more cumbersome than it should.
One such item is selecting the last inserted value on an auto-incrementing column.
Historically, when you are inserting into a table with auto incrementing values (via a sequence) you have always been able to grab the last value with a simple SELECT statement (line 22):
-- setup a table
CREATE TABLE bork (id INTEGER NOT NULL PRIMARY KEY, data VARCHAR2(10) NOT NULL);
-- create the sequence
CREATE SEQUENCE sq_bork INCREMENT BY 1 START WITH 1;
-- create a trigger for auto-incrementing the sequence'
CREATE OR REPLACE TRIGGER tr_sq_bork
REFERENCING NEW AS NEW
FOR EACH ROW
SELECT sq_bork.NEXTVAL INTO :NEW.id FROM DUAL;
-- insert a record into the table
INSERT INTO bork (name) VALUES ('Matt');
-- retrieve last inserted id
SELECT sq_bork.CURRVAL FROM dual;
As you see there, two statements must be executed to get that new id. The INSERT and the SELECT. Well, as of Oracle 10g you can utilize the RETURNING clause like so:
INSERT INTO bork (name) VALUES ('Matt') RETURNING id INTO i_id;
That statement inserts a record into “bork” and returns the value of “id” into the “i_id” variable. Pretty sexy and all with one DML statement. Here’s what we do at Plymouth to utilize the RETURNING clause with the PHP library ADOdb:
//do your database object initialization here:
//$db = new ADONewConnection...
$sql = "BEGIN INSERT INTO bork (data) VALUES ('Matt') RETURNING id INTO :i_id; END;";
$stmt = $db->PrepareSP($sql);
$db->OutParameter($stmt, $inserted_id, 'i_id');
Yup. 4 lines of PHP but only 1 statement sent to the database! I’d take the extra lines any day over the latency of data retrieval.
I a huge fan of Oracle SQL Developer but I ran into an issue a while back that left me scratching my head and re-installing. The issue? Most key strokes other than letters and numbers failed to function. Yeah…no delete or backspace.
When this issue happened a second time I did a little playing with the app and figured out what to do. If you find yourself in a similar predicament simply do this:
- Open SQL Developer
- Click on the Tools > Preferences menu
- Click on Accelerators on the left
- Click the Load Preset… button
Yup. That’s it. The only lame part about that is all your custom key bindings go away and must be re-done. But that is much nicer than re-installing!
Here’s a query I wrote to find the invalid objects and generate compile statements to attempt to fix whatever is busted.
SELECT CASE object_type
WHEN 'PACKAGE' THEN
'ALTER '||object_type||' '||owner||'.'||object_name||' COMPILE;'
'ALTER PACKAGE '||owner||'.'||object_name||' COMPILE BODY;'
WHERE status = 'INVALID'
AND object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE');
My previous post, titled: ‘Sequence-less/Trigger-less Oracle Auto Increment‘ was shot out of the water by my friend and DBA, Jon Emmons. Glad to see that I can be kept in line.
So I have resigned to use Oracle Sequences as a safer means for auto incrementing. But that doesn’t mean that I like it. Here’s what needs to be done to implement auto_incrementing with Oracle:
First, create a sequence:
CREATE SEQUENCE sweet_incrementing INCREMENT BY 1 START WITH 1;
Next write your statement:
INSERT INTO table (id,name) VALUES (sweet_incrementing.NextVal,’bork’);
Now at a second glance, this statement looks much cleaner than my first attempt at subverting the system. However, the fact that you need to create a whole new sequence for each table you wish to have an auto incrementer is pretty stupid when compared to MySQL’s plan of attack. In MySQL you can simply mark a column as a auto_increment during table creation and you’re good to go!
Oracle sucks. Well, not totally, but it fails in a lot of places where MySQL is sexy. One little gem of an irritation is the lack of an auto_increment attribute associated with fields. Instead you have to make use of Oracle Sequences/Triggers which adds a whole layer of complexity on the creation and insertion into a simple table.
I’m not quite sure on this statement’s efficiency, but here’s my solution:
INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);
EDIT: It seems that this isn’t such a good idea, after help from my friendly neighborhood DBA.