SEO

>From: "David Buchholz" <David.Buchholz@tsysacquiring.com>

> > This group's Oracle knowledge is current to version 8i. <g>
>
>Yeah, Daniel, and your Informix knowledge seems current to version 5.1.
>
>Big <g>
>
>The only difference is that we don't troll the Oracle newsgroups.
>
>-Dave

Well actually no.

My experience is with Oracle 9. Oh yeah, they really have things worked out
when it comes to geospatial.

QuadTree indexes were a big joke, but in Oracle 9, there was a nasty bug
that caused R-Tree indexes to be too slow. (Yeah there was a delay
introduced to slow down some of the processing so that it could synch
properly.) Supposedly fixed in Oracle 10 or 11.

There's some quirks in the cx_Oracle python module, but that's not really
Oracle's fault.

I'd bitch about Informix's python module, and their RoR adapter but IBM
*HASN'T* released their open beta relase on the RoR yet!

Yet I digress.

Sorry Danny boy, but for a troll, you've kind of worn out your welcome.

__________________________________________________ _______________
Hotmail to go? Get your Hotmail, news, sports and much more!
http://mobile.msn.com

Ian Michael Gumby wrote:
>
>
>
>> From: "David Buchholz" <David.Buchholz@tsysacquiring.com>
>
>> > This group's Oracle knowledge is current to version 8i. <g>
>>
>> Yeah, Daniel, and your Informix knowledge seems current to version 5.1.
>>
>> Big <g>
>>
>> The only difference is that we don't troll the Oracle newsgroups.
>>
>> -Dave
>
> Well actually no.
>
> My experience is with Oracle 9.

Released 6 years ago in 2001 and currently desupported. That's keeping
current?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

DA Morgan wrote:

>> My experience is with Oracle 9.
>
> Released 6 years ago in 2001 and currently desupported. That's keeping
> current?

Desupported already?! I thought it was about to reach end of support (with 11
comming up)... Plea$e $upgrade$... keep Gartner happy! :>

By the way... You may clarify me a doubt... (I really should ask this in an
Oracle newsgroup...):

My fellow Oracle DBA tells me something about PL/SQL need to have explicitly
grants (user/table) while simple SQL doesn't... I know I'm not making much
sense, but in his words: "It's a different engine... SQL and PL/SQL"
If I recall correctly if you create a procedure with a user that has the
necessarily privileges over the tables, a third user cannot execute the
procedure if he doesn't have the underlying table privileges... Does it ring a
bell? I'll try to clarify this with him...

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Fernando Nunes wrote:
> My fellow Oracle DBA tells me something about PL/SQL need to have
> explicitly grants (user/table) while simple SQL doesn't... I know I'm
> not making much sense, but in his words: "It's a different engine... SQL
> and PL/SQL"
> If I recall correctly if you create a procedure with a user that has the
> necessarily privileges over the tables, a third user cannot execute the
> procedure if he doesn't have the underlying table privileges... Does it
> ring a bell? I'll try to clarify this with him...
Fernando I think you are confusing two issues here.
In Oracle dynamic SQL run by a user can use role-membership to execute.
Any DDL objects (like e.g. views) cannot rely on a permission based on a
role. The definer requires explicit permission to the used objects.
Given that roles are managed in the database this is indeed interesting.

The fact that routines run under "definer"s rights by default is part of
encapsulation. One common usage of routines is to avoid having to give
users access to base objects. To the best of my knowledge this is SQL
standard. There should (and I believe is) a means to use "invoker"s
rights which basically turns a routine into a macro.

Anyway there are countless differences between Oracle SQL and PL/SQL in
packages (and they drive me nuts on a daily basis).
e.g: routine overloading and defaulting is only supported within packages.
VARCHAR2 has different limits inside and outside of PL/SQL (32k vs. 4000),
You can declare subtypes (like distinct types) in PL/SQL, but not outside.
In fact there are many objects that can only be used within a package
and/or routine.
Since PL/SQL typically contain SQL (such as expressions) inside of it
it's pretty confusing what can and cannot be done at any given time.

One of the things I have learned over the years is to treat PL/SQL and
SQL completely separate.

I believe this may be one reason why Oracle users perceive the lack of
package support in other products as such a big minus. It concentrates a
lot of the capabilities.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Fernando Nunes wrote:
> DA Morgan wrote:
>
> Desupported already?! I thought it was about to reach end of support
> (with 11 comming up)... Plea$e $upgrade$... keep Gartner happy! :>

http://www.oracle.com/support/librar...-datasheet.pdf
My mistake ... desupport begins in 9 days.

> If I recall correctly if you create a procedure with a user that has the
> necessarily privileges over the tables, a third user cannot execute the
> procedure if he doesn't have the underlying table privileges... Does it
> ring a bell? I'll try to clarify this with him...

Your question is quite clear and your DBAs are partially correct. The
issue is the security model for compiled objects and it can be
configured to work either way on a procedure by procedure basis. With
DEFINER RIGHTS the user does not need permission to the underlying
tables/view. With CURRENT_USER rights permission must be granted. HTH.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Serge Rielau wrote:
> Fernando Nunes wrote:
>> My fellow Oracle DBA tells me something about PL/SQL need to have
>> explicitly grants (user/table) while simple SQL doesn't... I know I'm
>> not making much sense, but in his words: "It's a different engine...
>> SQL and PL/SQL"
>> If I recall correctly if you create a procedure with a user that has
>> the necessarily privileges over the tables, a third user cannot
>> execute the procedure if he doesn't have the underlying table
>> privileges... Does it ring a bell? I'll try to clarify this with him...

> Fernando I think you are confusing two issues here.
> In Oracle dynamic SQL run by a user can use role-membership to execute.

Correct.

> Any DDL objects (like e.g. views) cannot rely on a permission based on a
> role. The definer requires explicit permission to the used objects.

Correct.

> Given that roles are managed in the database this is indeed interesting.
>
> The fact that routines run under "definer"s rights by default is part of
> encapsulation. One common usage of routines is to avoid having to give
> users access to base objects. To the best of my knowledge this is SQL
> standard. There should (and I believe is) a means to use "invoker"s
> rights which basically turns a routine into a macro.

Though any object can also be created/replaced with CURRENT_USER rights.

Part of the reason for this is that objects, with the same name, may
exist in both the user's and the definer's schemas. This makes it easy
to determine which of these objects is used by the code.

> Anyway there are countless differences between Oracle SQL and PL/SQL in
> packages (and they drive me nuts on a daily basis).

Or would if you had Oracle installed eh. <g>

> e.g: routine overloading and defaulting is only supported within packages.

And user defined types and operators.

> VARCHAR2 has different limits inside and outside of PL/SQL (32k vs. 4000),
> You can declare subtypes (like distinct types) in PL/SQL, but not outside.
> In fact there are many objects that can only be used within a package
> and/or routine.
> Since PL/SQL typically contain SQL (such as expressions) inside of it
> it's pretty confusing what can and cannot be done at any given time.

Unless someone works with it regularly in which case it is mindlessly
simple.

> One of the things I have learned over the years is to treat PL/SQL and
> SQL completely separate.

Or would if you had Oracle installed eh. <g>

> I believe this may be one reason why Oracle users perceive the lack of
> package support in other products as such a big minus. It concentrates a
> lot of the capabilities.
>
> Cheers
> Serge

Partially correct. The package initialization section is important,
global persistent variable and type definitions are another. There
are many reasons why packages are a huge improvement over stand-alone
procedures/functions. Among them ease of maintenance and versioning:
One object vs. many.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

DA Morgan wrote:
>> Anyway there are countless differences between Oracle SQL and PL/SQL
>> in packages (and they drive me nuts on a daily basis).
> Or would if you had Oracle installed eh. <g>
Let the record show that I do not have Oracle installed.
There was an oh so short episode of misguidance.
I have been shown back to the true path long ago.

Hence, you (or anyone else) are most welcome to test DB2 Viper 2 beta
and point out my fallings caused by said lack of blessed state.

e.g. is it true that BITAND really returns wrong results for NUMBERs
bigger than max bigint?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:

> Fernando I think you are confusing two issues here.

Yes! I checked with him and he told me it was role related... And DDL I
think... Never mind.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Serge Rielau wrote:
> DA Morgan wrote:
>>> Anyway there are countless differences between Oracle SQL and PL/SQL
>>> in packages (and they drive me nuts on a daily basis).
>> Or would if you had Oracle installed eh. <g>
> Let the record show that I do not have Oracle installed.

I knew that which is why I wanted to offer you an opportunity to clarify
anyone's misunderstanding.

> e.g. is it true that BITAND really returns wrong results for NUMBERs
> bigger than max bigint?

A search of the appropriate website shows no evidence that this is
correct in any version.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
> Serge Rielau wrote:
>> DA Morgan wrote:
>>>> Anyway there are countless differences between Oracle SQL and PL/SQL
>>>> in packages (and they drive me nuts on a daily basis).
>>> Or would if you had Oracle installed eh. <g>
>> Let the record show that I do not have Oracle installed.
>
> I knew that which is why I wanted to offer you an opportunity to clarify
> anyone's misunderstanding.
>
>> e.g. is it true that BITAND really returns wrong results for NUMBERs
>> bigger than max bigint?
>
> A search of the appropriate website shows no evidence that this is
> correct in any version.
Eh.. don't tell me YOU don't have have Oracle installed.
I'm the one who has to look at websites, you can just try stuff ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab