Oracle 10g: Using The Returning Clause With ADOdb

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
BEFORE INSERT
ON bork
REFERENCING NEW AS NEW
FOR EACH ROW 
BEGIN
SELECT sq_bork.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

-- 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:

< ?php
//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');
$db->Execute($stmt);
?>

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.

Summit 2007 Presentation Proposal

Like Zach, its time for me to submit my Summit Presentation Proposals. My plans for submitting presentations for Student Query v2.0 and Course Query v2.0 were smashed when competing priorities at work prevented me from pushing forward with those projects. They remain in the same state as last year. Regardless, I still have one presentation proposal this year, which I’ll put below (along with my updated bio).

My Title: Self-Service Banner Administrator and Senior Web Developer

My Bio:
Matthew Batchelder is from Plymouth State University in northern New Hampshire. His titles include: Self-Service Banner Administration, Senior Web Developer, and Business Analyst. Matthew’s projects revolve heavily around Identity Management, custom Student/Prospect tools, and web-based reporting. Summit 2007 will be Matthew’s second year presenting at SungardHE’s conference – at his first, Summit 2006, he presented on “Student Reporting Through SSB.”

Building for Prospective Students
Prospective Students are a key component of every campus. They become our students, but we have to interest them first! Delivering personalized content to the Prospective Student and a solid e-mail marketing strategy in the form of a Prospective Student Portal was the direction taken by Plymouth State University. This session will walk through the steps PSU has taken to plan, develop, integrate, and implement this Prospective Student Portal.