RE: Views based on function returning table

The thread started off asking about basing a view on a PLSQL function.

If you create your application by only accessing data through views, you can start with the views as select * from table_name. If a year later, you decide you need to implement security that requires IF THEN logic, you can change a view to select from a pipelined table function. This function could programmatically decide to hide certain column values and/or rows. The views alone don't neccessarily give the extra security. It's a good idea to use views to begin with so that it's easier to change the underlying data access without having to change the application code.

There is another side to this which is the ability to create instead-of triggers for the view to handle DML. You may find that you don't need to change the view from being based on a table, but you do need to implement more security on DML operations. When needed, you could create these instead-of triggers without changing the application code. One big advantage of these is that you don't run into mutating table errors. If you have tables with an arced relationship, one insert could write to two tables.

Ray

---

I see people mentioning using views for access security.

Can someone explain to me how giving access to a simple view rather than the actual table can help in security?

 

From: ml-errorsfatcity.com [mailto:ml-errorsfatcity.com] On Behalf Of Raymond De Bruyn
Sent: Thursday, March 06, 2008 10:35 AM
To: Multiple recipients of list ODTUG-WEBDEV-L
Subject: Re: Views based on function returning table

 

Using bulk collect is definitely useful, but doesn't answer the question of whether to use views based on functions returning collections. Most, if not any, code where you loop through a cursor could be sped up by using bulk collect. Basing a view on a function that returns a collection has advantages, as pointed out, but that in itself isn't reason to use it.

I find in coding that it's best to write code as simple as possible while meeting the requirements. It's always best to code for performance, so bulk collect yes. You may wish to add levels of security in the future, so don't give direct access to tables. Instead, access data through views, but the view only needs to be a basic view until there is a requirement that calls for a view based on a function that returns a collection. I beleive you're talking about pipelined table functions that pipe rows.

I've seen too many projects that become overly complex for no good reason other than hype. I might add that in those overly complex projects often there is poorly written SQL. Too much effort is poured into the flavor of the week and not enough into good SQL and performance planning. As the saying goes - if the shoe fits wear it. Translation - if the technique fits your needs use it, otherwise don't buy into shoes that don't fit.

My 2 cents,
Ray

---

From: ; "Gabriel Tanase" <gabtanasegmail.com&gt;
Reply-To: &nbsp;ODTUG-WEBDEV-Lfatcity.com
To: ; Multiple recipients of list ODTUG-WEBDEV-L <ODTUG-WEBDEV-Lfatcity.com>
Subject: &nbsp;Re: Views based on function returning table
Date: &nbsp;Thu, 06 Mar 2008 05:55:27 -0800

 

Hmmm, back to school then. Thanks for the corrections.

 

Regards,

Gabriel

&nbsp;

On 3/6/08, Gints Plivna < gints.plivnagmail.com">gints.plivnagmail.com> wrote:

2008/3/6, Gabriel Tanase < gabtanasegmail.com">gabtanasegmail.com>:
> I am saying this based on my understanding is that nowadays the SQL and

> PL/SQL engines are co-mingled (for want of a better word) and there are no
> costly context switches (process + memory space) anymore when SQL calls
> PL/SQL and PL/SQL passes result sets back.

Context switches do exist. And overhead as well. There are however

some explicit means to overcome that as well as implicit conversion
for very limited constructs.

 

-- For more information on this topic or to become a member, visit our web site at http://www.ODTUG.com Be sure to check out our Seriously Practical (SP) Conferences coming up this year! ODTUG is pleased to announce that Kaleidoscope 2008 will be held at The New Orleans Sheraton from June 15-19. Keep checking www.ODTUG.com for more details.-- Author: Raymond De Bruyn INET: rdebruynsympatico.ca Fat City Hosting, San Diego, California -- http://www.fatcity.com --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGurufatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ODTUG-WEBDEV-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).