This forum is currently locked. You can't register or post questions at this time. (read more)

Insert/Update/Delete View

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Insert/Update/Delete View

Postby ponic » Tue Apr 03, 2012 11:32 am

Hi Tim,

From my front end I need to insert data into two tables, so is it a good or advisable practice to do insertion. update and deletion using views?
If so how can I do this?

Regards

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Insert/Update/Delete View

Postby Tim... » Tue Apr 03, 2012 12:15 pm

Hi.

It depends why you are using the views.

1) If you are planning to use Edition Based Redfinition, then you never directly access a table. Instead, all code access the editioning view.

http://www.oracle-base.com/articles/11g ... _11gR2.php

2) If you are using the view to hide certain columns from the developer, to make their life simpler.

3) If you are trying to define a simple structure (a single view) to the developer, which is actually fed from a complex structure (several tables). You can then use instead-of triggers to allow insert/update/delete of the views, which behind the scenes will update multiple tables.

4) Having the views in a separate schema that the developer has access to, without giving the developer access to the base tables.

All of these options have value in the right context, but just blanketly saying, I will create a view on top of every table for no particular reason is not a valid choice in my mind, especially if the developer can see both the view and the base table, because sometimes they may access the view and sometimes the table. This makes tracking dependencies trickier.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ponic
Senior Member
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Insert/Update/Delete View

Postby ponic » Tue Apr 03, 2012 12:26 pm

Tim,

All of the options you have mentioned is not applicable to me as I am same person who creates tables,views and creates applications. :-)
I guess I would go by creating a procedure, so that would be a simple choice.

Thanks anyway Tim.

Appreciated.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Insert/Update/Delete View

Postby Tim... » Tue Apr 03, 2012 12:44 pm

Hi.

If you are doing everything, I would write your code to directly access the tables, not go via a view.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests