RE: Views based on function returning table

-----Original Message-----
From: ml-errorsfatcity.com [mailto:ml-errorsfatcity.com]On Behalf Of Hudson, James - DNR
Sent: Thursday, March 06, 2008 1:25 PM
To: Multiple recipients of list ODTUG-WEBDEV-L
Subject: RE: Views based on function returning table

The simplest case is where ;you have columns in the data that some users shouldn't be able to see.

 

create table emp (last_name varchar2(30), first_name varchar2(30), social_security_no varchar2(9))

/

create view emp_without_ssn_v as select last_name, first_name from emp

/

grant select on emp_without_ssn_v to limited_user_role

/

 

But you could also limit access by rows with a where clause. Either way, the view would limit the rows and columns visible to a particular user or developer.

 

By doing it on the database side, it means that will be the case for any application accessing the data and won't have to be programmed into every user interface.

 

-- jim

James F. Hudson
Wisconsin Department of Natural Resources
Madison, WI
(608) 267-0840

 

---

From: ml-errorsfatcity.com [mailto:ml-errorsfatcity.com] On Behalf Of Mark Kramm
Sent: Thursday, March 06, 2008 11:15 AM
To: Multiple recipients of list ODTUG-WEBDEV-L
Subject: RE: Views based on function returning table

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 <gmail.com">gints.plivnagmail.com> wrote:

2008/3/6, Gabriel Tanase <gmail.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).