Re: Informix beats Oracle

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
.

Relevant Pages

  • Re: Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Access to Pre-defined Packages ...
    (comp.databases.oracle.server)
  • Re: Teaching Oracle PL/SQL class
    ... PL/SQL is used in industry. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Subtopics ...
    (comp.databases.oracle.server)
  • Re: Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Parameterized Cursors ...
    (comp.databases.oracle.server)
  • Re: Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Parameterized Cursors ...
    (comp.databases.oracle.server)
  • Teaching Oracle PL/SQL class
    ... I am teaching an Oracle PL/SQL class. ... Common Uses? ... Tight Integration with SQL / Higher Productivity ... Parameterized Cursors ...
    (comp.databases.oracle.server)
Loading