Tuesday, November 27, 2007

Here's a thought... ActiveRecord in PL/SQL

With all the talk about putting logic in the DB and hiding SQL behind stored procedures, wouldn't it be really intuitive if Oracle simply provided a default ActiveRecord implementation for every table in the DB? So, instead of returning a vanilla record type, FOO%ROWTYPE would return an Oracle-generated Object type that had accessors/mutators (getters/setters) for all the columns in table FOO and methods to UpdateRecord and DeleteRecord along with a static method to AddRecord. If you want to avoid any collisions with existing syntax, we could simply have a new attribute- something like FOO%OBJECTTYPE.

My experience shows that most reluctance to encapsulate SQL behind PL/SQL programs stems from the fact that people actually have to write (and subsequently maintain!) that wrapper code. People just don't want to do the donkey work (write the same boilerplate code over-and-over agian for each Table). And personally, I don't think a code generator is very good in this case either. The generated code simply breaks down under maintenance (add/modify/drop column from table).

But, what if all that was done by the database?– CREATE the Object Type when a Table is CREATEd and propagate changes whenever the table is ALTERed; plus maintain the accessors (which could very well enforce the integrity constraints declared on the Table)!

It'd be a great way for the Oracle community to show some benevolence towards (lowly! :P) application developers who can't write PL/SQL even if their life depended on it, let alone SQL!

Also, if you left the <Table>%OBJECTTYPE as NOT FINAL, those who can write PL/SQL could extend it to add even more (business) logic in the database! Whopee and horaay!!!


Anonymous said...

I do not know if it's just me or if everybody else encountering issues with your blog. It looks like some of the text within your content are running off the screen. Can someone else please provide feedback and let me know if this is happening to them too? This could be a issue with my internet browser because I've had this happen previously.

Here is my blog ... www.virtualworldsedu.info

Anonymous said...

Рretty nice post. I ϳust stumbled upon your blog and
wanted to say that Ι've truly enjoyed surfing around your blog posts. After all I'll be subsсгibing tо yοur fеed anԁ I hοpe you write again soοn!

Hеre iѕ my blοg ... Lloyd Irvin