It's possible to achieve something like this?

Procedure1(param1,param2) IS
....
begin
Procedure2;
end Procedure1;

Procedure2 IS
.....
begin
--Know the parameters values of Procedure1 at this point(with
dbms_debug, dbms_trace, some V$, ....)
end Procedure2;

The point to this is that i have a lot of procedures(like Procedure1)
calling a Procedure(Procedure2).
So if this could be achieved, i only have to use a simple line like
"Procedure2;" to all procedures(and with the advantage of not being
dependent of the number of parameteres, so future changes wouldn't
affect the Procedure2 call).

Thx in advance.

Op 29-1-2010 14:09, LPF schreef:
> It's possible to achieve something like this?
>
> Procedure1(param1,param2) IS
> ...
> begin
> Procedure2;
> end Procedure1;
>
> Procedure2 IS
> ....
> begin
> --Know the parameters values of Procedure1 at this point(with
> dbms_debug, dbms_trace, some V$, ....)
> end Procedure2;
>
> The point to this is that i have a lot of procedures(like Procedure1)
> calling a Procedure(Procedure2).
> So if this could be achieved, i only have to use a simple line like
> "Procedure2;" to all procedures(and with the advantage of not being
> dependent of the number of parameteres, so future changes wouldn't
> affect the Procedure2 call).
>
> Thx in advance.

With this approach, you're on the highway to hell.

Shakespeare

On Jan 29, 8:09=A0am, LPF <eng.lp...@gmail.com> wrote:
> It's possible to achieve something like this?
>
> Procedure1(param1,param2) IS
> ...
> begin
> Procedure2;
> end Procedure1;
>
> Procedure2 IS
> ....
> begin
> --Know the parameters values of Procedure1 at this point(with
> dbms_debug, dbms_trace, some V$, ....)
> end Procedure2;
>
> The point to this is that i have a lot of procedures(like Procedure1)
> calling a Procedure(Procedure2).
> So if this could be achieved, i only have to use a simple line like
> "Procedure2;" to all procedures(and with the advantage of not being
> dependent of the number of parameteres, so future changes wouldn't
> affect the Procedure2 call).
>
> Thx in advance.

There is a way to define what are in effect global variables for a
session.  You create a package specification and define all the
variables there.  As long as the calling procedure has populated the
variables the called procedure can access and use them.  I am not sure
coding like this would put you on the road to hell but it sure would
create hell for the programmers who follow you.  Also any change to
the package specification invalidates all the referencing code on your
system, which is not a good thing.

Procedures have parameter lists for a reason.  Using parameters
appropriately makes for more robust and maintainable code.  Global
variable belong in packages for use by procedures defined within the
package and should be considered for use outside of a single package
only in limited circumstances.

You should really rethink your design.

IMHO -- Mark D Powell --

LPF (eng.lpsff@gmail.com) wrote:
: It's possible to achieve something like this?

: Procedure1(param1,param2) IS
: ...
: begin
: Procedure2;
: end Procedure1;

: Procedure2 IS
: ....
: begin
: --Know the parameters values of Procedure1 at this point(with
: dbms_debug, dbms_trace, some V$, ....)
: end Procedure2;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE might help do this.  And while
checking the documentation for that one you should look for other related
procedures in the same part of the manual.

Tom Kyte (who else) has some examples of it's usage.

On 29.01.2010 15:55, Shakespeare wrote:
> Op 29-1-2010 14:09, LPF schreef:
>> It's possible to achieve something like this?
>>
>> Procedure1(param1,param2) IS
>> ...
>> begin
>> Procedure2;
>> end Procedure1;
>>
>> Procedure2 IS
>> ....
>> begin
>> --Know the parameters values of Procedure1 at this point(with
>> dbms_debug, dbms_trace, some V$, ....)
>> end Procedure2;
>>
>> The point to this is that i have a lot of procedures(like Procedure1)
>> calling a Procedure(Procedure2).
>> So if this could be achieved, i only have to use a simple line like
>> "Procedure2;" to all procedures(and with the advantage of not being
>> dependent of the number of parameteres, so future changes wouldn't
>> affect the Procedure2 call).
>>
>> Thx in advance.
>
> With this approach, you're on the highway to hell.

I agree: this implicit transfer of information is bad to understand and 
debug - and it's error prone.  What if Procedure2 needs a parameter that 
Procedure1 does not receive?  You would never know.  The laziness to 
type a few procedure arguments will come back and haunt you.  I just 
removed access to configuration information through a static in Java and 
I can tell you: this is not fun - even though with modern IDEs you will 
find all affected places pretty easily.

Kind regards

	robert


-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Fri, 29 Jan 2010 05:09:21 -0800 (PST), LPF <eng.lpsff@gmail.com>
wrote:

>It's possible to achieve something like this?
>
>Procedure1(param1,param2) IS
>...
>begin
>Procedure2;
>end Procedure1;

Did you try? 
Apparently you didn't.
Scoping rules dictate param1 and param2 should be available at all
lower levels, assuming you declared procedure2 locally to procedure1.
If you didn't, it won't work.
As with most questions a quick glance in the Fine Manual would have
provided the answer.
And, BTW, it is usually considered bad  programming practice, being a
recipe to make code unreadable.

-------------- 
Sybrand Bakker
Senior Oracle DBA

LPF <eng.lpsff@gmail.com> writes:

> It's possible to achieve something like this?
>
> Procedure1(param1,param2) IS
> ...
> begin
> Procedure2;
> end Procedure1;
>
> Procedure2 IS
> ....
> begin
> --Know the parameters values of Procedure1 at this point(with
> dbms_debug, dbms_trace, some V$, ....)
> end Procedure2;
>
> The point to this is that i have a lot of procedures(like Procedure1)
> calling a Procedure(Procedure2).
> So if this could be achieved, i only have to use a simple line like
> "Procedure2;" to all procedures(and with the advantage of not being
> dependent of the number of parameteres, so future changes wouldn't
> affect the Procedure2 call).
>
> Thx in advance.

You could look at the object oriented features.  Set variables on an
object then call a member function/procedure could accomplish this.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

LPF <eng.lpsff@gmail.com> writes:

> It's possible to achieve something like this?
>
> Procedure1(param1,param2) IS
> ...
> begin
> Procedure2;
> end Procedure1;
>
> Procedure2 IS
> ....
> begin
> --Know the parameters values of Procedure1 at this point(with
> dbms_debug, dbms_trace, some V$, ....)
> end Procedure2;
>
> The point to this is that i have a lot of procedures(like Procedure1)
> calling a Procedure(Procedure2).
> So if this could be achieved, i only have to use a simple line like
> "Procedure2;" to all procedures(and with the advantage of not being
> dependent of the number of parameteres, so future changes wouldn't
> affect the Procedure2 call).
>

DO NOT DO IT!

this is such a bad idea on so many levels, I can't even begin to list
them all. I also doubt it will work technically and even if it did, you
are likely to be in all sorts of mess when the oracle version changes.
essentialy, you may as well code your whole thing as one huge procedure
because doing it how you suggest would be about the same. You would lose
all the benefits of scoping, would rely on side effects, would run itno
all sorts of type issues, would cripple system efficiency and wuld
create a monster that cannot be maintained or easily tested. 

Programmers should be lazy, but not that lazy. Use decent tools and
the changes your talking about are not a problem. In fact, they are a
benefit as you can make changes and easily identify and test what has
been modified and you only need to understand the bit of code your
changing, not the whole system with its hidden side effects.

Tim
-- 
tcross (at) rapttech dot com dot au

Tim X <timx@nospam.dev.null> writes:

> LPF <eng.lpsff@gmail.com> writes:
>
>> It's possible to achieve something like this?
>>
>> Procedure1(param1,param2) IS
>> ...
>> begin
>> Procedure2;
>> end Procedure1;
>>
>> Procedure2 IS
>> ....
>> begin
>> --Know the parameters values of Procedure1 at this point(with
>> dbms_debug, dbms_trace, some V$, ....)
>> end Procedure2;
>>
>> The point to this is that i have a lot of procedures(like Procedure1)
>> calling a Procedure(Procedure2).
>> So if this could be achieved, i only have to use a simple line like
>> "Procedure2;" to all procedures(and with the advantage of not being
>> dependent of the number of parameteres, so future changes wouldn't
>> affect the Procedure2 call).
>>
>
> DO NOT DO IT!
>
> this is such a bad idea on so many levels, I can't even begin to list
> them all. I also doubt it will work technically and even if it did, you
> are likely to be in all sorts of mess when the oracle version changes.
> essentialy, you may as well code your whole thing as one huge procedure
> because doing it how you suggest would be about the same. You would lose
> all the benefits of scoping, would rely on side effects, would run itno
> all sorts of type issues, would cripple system efficiency and wuld
> create a monster that cannot be maintained or easily tested. 
>
> Programmers should be lazy, but not that lazy. Use decent tools and
> the changes your talking about are not a problem. In fact, they are a
> benefit as you can make changes and easily identify and test what has
> been modified and you only need to understand the bit of code your
> changing, not the whole system with its hidden side effects.

Its just the OO world dude.  Instead of calling methods with parameters,
you instantiate objects, set their instance variables and then have the
object operate.  

Oracle's object oriented features are superb, and I'm not talking about
just using object types, I'm talking about using OO.  At the end of the
day, its all still SQL and PLSQL.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

Galen Boyer <galen_boyer@yahoo.com> writes:

> Tim X <timx@nospam.dev.null> writes:
>
>> LPF <eng.lpsff@gmail.com> writes:
>>
>>> It's possible to achieve something like this?
>>>
>>> Procedure1(param1,param2) IS
>>> ...
>>> begin
>>> Procedure2;
>>> end Procedure1;
>>>
>>> Procedure2 IS
>>> ....
>>> begin
>>> --Know the parameters values of Procedure1 at this point(with
>>> dbms_debug, dbms_trace, some V$, ....)
>>> end Procedure2;
>>>
>>> The point to this is that i have a lot of procedures(like Procedure1)
>>> calling a Procedure(Procedure2).
>>> So if this could be achieved, i only have to use a simple line like
>>> "Procedure2;" to all procedures(and with the advantage of not being
>>> dependent of the number of parameteres, so future changes wouldn't
>>> affect the Procedure2 call).
>>>
>>
>> DO NOT DO IT!
>>
>> this is such a bad idea on so many levels, I can't even begin to list
>> them all. I also doubt it will work technically and even if it did, you
>> are likely to be in all sorts of mess when the oracle version changes.
>> essentialy, you may as well code your whole thing as one huge procedure
>> because doing it how you suggest would be about the same. You would lose
>> all the benefits of scoping, would rely on side effects, would run itno
>> all sorts of type issues, would cripple system efficiency and wuld
>> create a monster that cannot be maintained or easily tested. 
>>
>> Programmers should be lazy, but not that lazy. Use decent tools and
>> the changes your talking about are not a problem. In fact, they are a
>> benefit as you can make changes and easily identify and test what has
>> been modified and you only need to understand the bit of code your
>> changing, not the whole system with its hidden side effects.
>
> Its just the OO world dude.  Instead of calling methods with parameters,
> you instantiate objects, set their instance variables and then have the
> object operate.  
>

Well, I guess that is one interpretation of OO! However, that wasn't
what the OP asked about, nor was his example referencing an Oracle
object definition. While I agree you could possibly reduce the number of
calls which pass parameters, OO will not eliminate them. The OPs
reference to dbms_debug, trace etc makes it pretty clear that wasn't
what he was thinking either. 

I don't agree that OO is about procedures without arguments. In
fact, every OO language I can think of has methods/procedures/messages
which support parameters. The original OO language, smalltalk, used a
message passing paradigm. You created an object and then you would send
messages to it. those messages might be very simple and might result in
the object doing something or possibly doing nothing. this is like
calling a method without parameters. However, its vary rare you can do
much with objects without passing them new stuff to operate on - without
that, they are really just a 'one trick pony'. Most of the methods you
will define for an object will take parameters. In smalltalk, where
everything is an object, you have things like

5 + 1

where 5 is an object, '+' is a message (procedure/method) and 1 is an
arguement/parameter. In java, you see 'setters' all over the place. If
you look at the basic Java API, you will see most mehtods take
parameters. Likewise, C++ uses parameters all over the place.  In fact,
in an OO design, you will often have objects like iterators that take
collection objects and  then operate on them - the collection object is
passed as parameter (usually in the constructor). 

> Oracle's object oriented features are superb, and I'm not talking about
> just using object types, I'm talking about using OO.  At the end of the
> day, its all still SQL and PLSQL.

I agree at the end of the day its all SQL and PL/SQL. However, as
someone who has used some really good and some really awful OO
languages, I'm afraid Oracle's attempt is not what I would call a superb
example. If you want to see a really clear, concise and clean OO
implementation, look at smalltalk. If you want to see a really horrible
version, check out C++. Java, lies in the middle somewhere and if you
want to see a really powerful OO paradigm, then check out CLOS (Common
Lisp Object System). 

I found Oracles OO stuff to be somewhere between Java and C++. It has
some inconsistencies in how things are treated, especially with respect
to types. For example, its ability to handle object types depends on
whether the object was defined at the SQL or the PLSQL level and it
can't handle %TYPE parameters in method definitions (I'm referring to
Oracle's object types with methods as opposed to the common object type
consisting of just object variables) 

The main weakness IMO with Oracle's OO stuff is that it is obviously an
after thought - something added to the language to hopefully provide
Oracle sales with another compliance buzzword. 

for me, the only thing it appears to have added is constructors and
inheritance. to a large extent, we already had OO with PL/SQL package
Maybe this is what you were referring to when you referred to 'not just
the object types'? If so, I would agree. PL/SQL packages have provided
all the most useful properties of the OO paradigm, with the exception on
inheritance. You have encapsulation, polymorphism/overloading of
functins/procedures, instance variables, private/public and even a form
of constructor with init blocks. It is even debatable that not having
inheritance in packages is a loss - inheritance is sometimes very
useful, but it is also probably the most misused/abused feature of OO
(with the possible exception of operator overloading in C++!). The only
thing that would be worse would be multiple inheritance!

I've used Oracle's OO types with method and constructors stuff a bit and
have found it useful for certain cases. For example, I used some of the
facilities in a 'proof of concept' test harness that I came across called
'PLUTO'. The work done by the authors was quite interesting, but the
implemeentation had a few problems. I've extended it and have been using
it as the basis for experimenting with a simple test harness for PL/SQL.
The ideas developed by the original authors (whose names escape me just
now - I think the code was on googlecode) are interesting and show quite
imaginative original application of PLSQL. I don't know yet whether it
will amount to anything relly useful, but its an interesting diversion
from less inspiring stuff and it gave me an excuse to play with oracle's
'full' object definitions. 

If the OP wants to apply OO paradigms to his design, then I would say go
for it. Personally, I don't think OO is always the right way to go, but
it certainly has been the popular choice over the last decade and there
is bound to be lots of people who will udnerstand such a paradigm and
provide assistance when/if required. . However, I think it wold be a
mistake to adopt that paradigm just so that you can avoid using
parameters in procedures. Even inside objects, you don't really want all
variables to be instance or class variables - sometimes you want values
to be short term or have limited lexical scope. The reduction of calls
with parameters comes at a cost - you have to design your application
from an OO perspective and have to be confident that adopting that
paradigm really does improve upon what you are doing. A badly designed
OO system is likely to be a lot worse than a reasonably designed
procedural model. I've also found that most OO approaches also add a
considerable amount of coding overhead. java is probably the best
example of this. While this may not be an issue on a really large
project, it can be a problem or difficult to justify in something not so large.

regards,

Tim

-- 
tcross (at) rapttech dot com dot au

>> Tim X <timx@nospam.dev.null> writes:
>>
>>> LPF <eng.lpsff@gmail.com> writes:
>>>
>>>> It's possible to achieve something like this?
>>>>
>>>> Procedure1(param1,param2) IS
>>>> ...
>>>> begin
>>>> Procedure2;
>>>> end Procedure1;
>>>>
>>>> Procedure2 IS
>>>> ....
>>>> begin
>>>> --Know the parameters values of Procedure1 at this point(with
>>>> dbms_debug, dbms_trace, some V$, ....)
>>>> end Procedure2;
>>>>
>>>> The point to this is that i have a lot of procedures(like Procedure1)
>>>> calling a Procedure(Procedure2).
>>>> So if this could be achieved, i only have to use a simple line like
>>>> "Procedure2;" to all procedures(and with the advantage of not being
>>>> dependent of the number of parameteres, so future changes wouldn't
>>>> affect the Procedure2 call).
>>>>
>>>
>>> DO NOT DO IT!
>>>
>>> this is such a bad idea on so many levels, I can't even begin to list
>>> them all. I also doubt it will work technically and even if it did, you
>>> are likely to be in all sorts of mess when the oracle version changes.
>>> essentialy, you may as well code your whole thing as one huge procedure
>>> because doing it how you suggest would be about the same. You would lose
>>> all the benefits of scoping, would rely on side effects, would run itno
>>> all sorts of type issues, would cripple system efficiency and wuld
>>> create a monster that cannot be maintained or easily tested. 
>>>
>>> Programmers should be lazy, but not that lazy. Use decent tools and
>>> the changes your talking about are not a problem. In fact, they are a
>>> benefit as you can make changes and easily identify and test what has
>>> been modified and you only need to understand the bit of code your
>>> changing, not the whole system with its hidden side effects.
>>
>> Its just the OO world dude.  Instead of calling methods with parameters,
>> you instantiate objects, set their instance variables and then have the
>> object operate.  
>>
>
> Well, I guess that is one interpretation of OO! However, that wasn't
> what the OP asked about, nor was his example referencing an Oracle
> object definition. While I agree you could possibly reduce the number of
> calls which pass parameters, OO will not eliminate them. The OPs
> reference to dbms_debug, trace etc makes it pretty clear that wasn't
> what he was thinking either. 
>
> I don't agree that OO is about procedures without arguments. 

I don't believe that either, but parameterized methods is one thing
that is quite less prevalent in OO.  The parameterized methods are
usually either setting up the object for later operation or helper
methods.

> In fact, every OO language I can think of has
> methods/procedures/messages which support parameters. The original OO
> language, smalltalk, used a message passing paradigm. You created an
> object and then you would send messages to it. those messages might be
> very simple and might result in the object doing something or possibly
> doing nothing. this is like calling a method without
> parameters. However, its vary rare you can do much with objects
> without passing them new stuff to operate on - 

But what you are passing is objects, which is much different than string
=> xx, number => 10 ...

Those objects you passed have somewhere before, been set up with their
instance variables.

> without that, they are really just a 'one trick pony'. Most of the
> methods you will define for an object will take parameters. In
> smalltalk, where everything is an object, you have things like
>
> 5 + 1
>
> where 5 is an object, '+' is a message (procedure/method) and 1 is an
> arguement/parameter. In java, you see 'setters' all over the place. If
> you look at the basic Java API, you will see most mehtods take
> parameters. Likewise, C++ uses parameters all over the place.  In fact,
> in an OO design, you will often have objects like iterators that take
> collection objects and  then operate on them - the collection object is
> passed as parameter (usually in the constructor). 
>
>> Oracle's object oriented features are superb, and I'm not talking about
>> just using object types, I'm talking about using OO.  At the end of the
>> day, its all still SQL and PLSQL.
>
> I agree at the end of the day its all SQL and PL/SQL. However, as
> someone who has used some really good and some really awful OO
> languages, I'm afraid Oracle's attempt is not what I would call a superb
> example. 

I consider it superb, in relation to what you have available in any
other database vendor, which is nil.  I consider it superb because it is
starting to allow us database guys to take back what should be handled
in the database and maybe not lose the OR layer argument so much.

> If you want to see a really clear, concise and clean OO
> implementation, look at smalltalk. If you want to see a really
> horrible version, check out C++. Java, lies in the middle somewhere
> and if you want to see a really powerful OO paradigm, then check out
> CLOS (Common Lisp Object System).
>
> I found Oracles OO stuff to be somewhere between Java and C++. It has
> some inconsistencies in how things are treated, especially with respect
> to types. For example, its ability to handle object types depends on
> whether the object was defined at the SQL or the PLSQL level and it
> can't handle %TYPE parameters in method definitions (I'm referring to
> Oracle's object types with methods as opposed to the common object type
> consisting of just object variables) 

Yes, I find that frustrating, but I do believe it will continue to get
better and better.  I'm really excited for the possibilities I see OR
mapping that could come from this.

> The main weakness IMO with Oracle's OO stuff is that it is obviously an
> after thought - something added to the language to hopefully provide
> Oracle sales with another compliance buzzword. 

I look at it differently.  They've done such a good job at committing to
a solid foundation that their object oriented features fit in quite
nicely.

> for me, the only thing it appears to have added is constructors and
> inheritance. to a large extent, we already had OO with PL/SQL package
> Maybe this is what you were referring to when you referred to 'not just
> the object types'? 

Inheritance as well as not instantiable.  The abstract class has alot of
power allowing you to define methods that must be implemented by the
implementing objects.  For a simple example, take the TAPI idea.  Now,
you could have an abstract class called an DML_OBJ that has the not
instantiable methods insert/update/delete.  (Tom Kyte would shoot me for
this :-))

It could have attributes on it that represent the columns of its table.
Then, java could issue

DECLARE
        v_col_obj some_table_cols_obj := some_table_cols_obj();
BEGIN
        v_col_obj.v_col1 := 'AA';
        v_col_obj.v_col2 := 'BB';
        v_obj some_table_obj := some_table_obj(v_col_obj);
        v_obj.insert;
END;
/

So, then the OR layer really maps the bean in java directly to the table
in the database leaving the implementation of the actual DML to database
code.

To me, it allows us to offer java a "simple" OR layer and take back the
SQL operations, which should be in the hand of the database developer.

That is why I call it superb.

> If so, I would agree. PL/SQL packages have provided all the most
> useful properties of the OO paradigm, with the exception on
> inheritance. 

Abstract classes.  Packages do not have that.

> You have encapsulation, polymorphism/overloading of
> functins/procedures, instance variables, private/public and even a
> form of constructor with init blocks. It is even debatable that not
> having inheritance in packages is a loss - inheritance is sometimes
> very useful, but it is also probably the most misused/abused feature
> of OO (with the possible exception of operator overloading in
> C++!). The only thing that would be worse would be multiple
> inheritance!
>
> I've used Oracle's OO types with method and constructors stuff a bit and
> have found it useful for certain cases. For example, I used some of the
> facilities in a 'proof of concept' test harness that I came across called
> 'PLUTO'. The work done by the authors was quite interesting, but the
> implemeentation had a few problems. I've extended it and have been using
> it as the basis for experimenting with a simple test harness for PL/SQL.
> The ideas developed by the original authors (whose names escape me just
> now - I think the code was on googlecode) are interesting and show quite
> imaginative original application of PLSQL. I don't know yet whether it
> will amount to anything relly useful, but its an interesting diversion
> from less inspiring stuff and it gave me an excuse to play with oracle's
> 'full' object definitions. 

I've written a dynamicsql_obj that has a sql_string as its main member,
and methods for appending strings, binding variables, ...  An
execute_immediate method ... Very nice for the place I entered cause
they had dynamic SQL all over the place, and tons of unbound.  Now, all
of it uses an object.  That object also has quite a few ways for the
coder to "see" the sql being executed which is usually a big shortcoming
to committing to alot of dynamic SQL (amongst a bunch of other issues)

I have a logging object that offers methods for instantiating it at the
beginning of some method, tons of methods for sending trace messages
that hit the trace files as well as dbms_output as well as logging
tables.

I have a reporting object that offers up 2 not instantiable
"load_parent_data" and "load_child_data" methods and tons of helper
procedure/functions which the java code calls.  Those objects fill up a
temp table which then is accessed by the reporting tool.  The OR layer
between these objects and the PLSQL that gets executed is embodied in
about 20 lines of java and an xml configuration file that calls out the
name of the implementing object.


> If the OP wants to apply OO paradigms to his design, then I would say go
> for it. Personally, I don't think OO is always the right way to go, but
> it certainly has been the popular choice over the last decade and there
> is bound to be lots of people who will udnerstand such a paradigm and
> provide assistance when/if required. . However, I think it wold be a
> mistake to adopt that paradigm just so that you can avoid using
> parameters in procedures. 

I agree completely.  Packages and procedures are never going to be
replaced, even in a heavy OO PLSQL environment.  The OO env allows one
to use that when it makes sense, and it really does in alot of places.

> Even inside objects, you don't really want all variables to be
> instance or class variables - sometimes you want values to be short
> term or have limited lexical scope. 

Well this is a big big big shortcoming of the OO implementation in
Oracle.  All variables must be instance variables.  There are no hiden
variables and another issue is each method must be public.  Yeah, this
stinks, but I do think Oracle will continue to improve things.

Another missing feature is interfaces.  :-( That would come in really
handy.

> The reduction of calls with parameters comes at a cost - you have to
> design your application from an OO perspective and have to be
> confident that adopting that paradigm really does improve upon what
> you are doing. A badly designed OO system is likely to be a lot worse
> than a reasonably designed procedural model. I've also found that most
> OO approaches also add a considerable amount of coding overhead. java
> is probably the best example of this. While this may not be an issue
> on a really large project, it can be a problem or difficult to justify
> in something not so large.

-- 
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

Galen Boyer <galen_boyer@yahoo.com> writes:

>>> Tim X <timx@nospam.dev.null> writes:
>>>
>>>> LPF <eng.lpsff@gmail.com> writes:
>>>>
>>>>> It's possible to achieve something like this?
>>>>>
>>>>> Procedure1(param1,param2) IS
>>>>> ...
>>>>> begin
>>>>> Procedure2;
>>>>> end Procedure1;
>>>>>
>>>>> Procedure2 IS
>>>>> ....
>>>>> begin
>>>>> --Know the parameters values of Procedure1 at this point(with
>>>>> dbms_debug, dbms_trace, some V$, ....)
>>>>> end Procedure2;
>>>>>
>>>>> The point to this is that i have a lot of procedures(like Procedure1)
>>>>> calling a Procedure(Procedure2).
>>>>> So if this could be achieved, i only have to use a simple line like
>>>>> "Procedure2;" to all procedures(and with the advantage of not being
>>>>> dependent of the number of parameteres, so future changes wouldn't
>>>>> affect the Procedure2 call).
>>>>>
>>>>
>>>> DO NOT DO IT!
>>>>
>>>> this is such a bad idea on so many levels, I can't even begin to list
>>>> them all. I also doubt it will work technically and even if it did, you
>>>> are likely to be in all sorts of mess when the oracle version changes.
>>>> essentialy, you may as well code your whole thing as one huge procedure
>>>> because doing it how you suggest would be about the same. You would lose
>>>> all the benefits of scoping, would rely on side effects, would run itno
>>>> all sorts of type issues, would cripple system efficiency and wuld
>>>> create a monster that cannot be maintained or easily tested. 
>>>>
>>>> Programmers should be lazy, but not that lazy. Use decent tools and
>>>> the changes your talking about are not a problem. In fact, they are a
>>>> benefit as you can make changes and easily identify and test what has
>>>> been modified and you only need to understand the bit of code your
>>>> changing, not the whole system with its hidden side effects.
>>>
>>> Its just the OO world dude.  Instead of calling methods with parameters,
>>> you instantiate objects, set their instance variables and then have the
>>> object operate.  
>>>
>>
>> Well, I guess that is one interpretation of OO! However, that wasn't
>> what the OP asked about, nor was his example referencing an Oracle
>> object definition. While I agree you could possibly reduce the number of
>> calls which pass parameters, OO will not eliminate them. The OPs
>> reference to dbms_debug, trace etc makes it pretty clear that wasn't
>> what he was thinking either. 
>>
>> I don't agree that OO is about procedures without arguments. 
>
> I don't believe that either, but parameterized methods is one thing
> that is quite less prevalent in OO.  The parameterized methods are
> usually either setting up the object for later operation or helper
> methods.
>
>> In fact, every OO language I can think of has
>> methods/procedures/messages which support parameters. The original OO
>> language, smalltalk, used a message passing paradigm. You created an
>> object and then you would send messages to it. those messages might be
>> very simple and might result in the object doing something or possibly
>> doing nothing. this is like calling a method without
>> parameters. However, its vary rare you can do much with objects
>> without passing them new stuff to operate on - 
>
> But what you are passing is objects, which is much different than string
> => xx, number => 10 ...

Well, a parameter is a parameter. The only way they really differ is if
they are pass by reference or pass by value. In many OO languages,
'strings' such as "Hello World" is actually an object, as are numbers,
so I don't really see the relevance of your point. I do concede that you
*may* have fewer method calls with parameters (at least from the
interface perspective), but not in any significant way. 
>
> Those objects you passed have somewhere before, been set up with their
> instance variables.
>

Possibly and more than likely, that would have been through calls to
constructors that would have taken parameters. I don't see this as
removing calls with parameters, just moving them to a different place.

>> without that, they are really just a 'one trick pony'. Most of the
>> methods you will define for an object will take parameters. In
>> smalltalk, where everything is an object, you have things like
>>
>> 5 + 1
>>
>> where 5 is an object, '+' is a message (procedure/method) and 1 is an
>> arguement/parameter. In java, you see 'setters' all over the place. If
>> you look at the basic Java API, you will see most mehtods take
>> parameters. Likewise, C++ uses parameters all over the place.  In fact,
>> in an OO design, you will often have objects like iterators that take
>> collection objects and  then operate on them - the collection object is
>> passed as parameter (usually in the constructor). 
>>
>>> Oracle's object oriented features are superb, and I'm not talking about
>>> just using object types, I'm talking about using OO.  At the end of the
>>> day, its all still SQL and PLSQL.
>>
>> I agree at the end of the day its all SQL and PL/SQL. However, as
>> someone who has used some really good and some really awful OO
>> languages, I'm afraid Oracle's attempt is not what I would call a superb
>> example. 
>
> I consider it superb, in relation to what you have available in any
> other database vendor, which is nil.  I consider it superb because it is
> starting to allow us database guys to take back what should be handled
> in the database and maybe not lose the OR layer argument so much.
>

I'd agree on both those two points. 

>> If you want to see a really clear, concise and clean OO
>> implementation, look at smalltalk. If you want to see a really
>> horrible version, check out C++. Java, lies in the middle somewhere
>> and if you want to see a really powerful OO paradigm, then check out
>> CLOS (Common Lisp Object System).
>>
>> I found Oracles OO stuff to be somewhere between Java and C++. It has
>> some inconsistencies in how things are treated, especially with respect
>> to types. For example, its ability to handle object types depends on
>> whether the object was defined at the SQL or the PLSQL level and it
>> can't handle %TYPE parameters in method definitions (I'm referring to
>> Oracle's object types with methods as opposed to the common object type
>> consisting of just object variables) 
>
> Yes, I find that frustrating, but I do believe it will continue to get
> better and better.  I'm really excited for the possibilities I see OR
> mapping that could come from this.
>

Well, I do agree that the OO support may result in better structured
code in the database, I think there is a danger as well. 

The issue is that once people start designing/working udner an OO
paradigm, they inevitably want to start thinking of the database under
that paradigm rather than under the relational model. What tends to
follow is an attempt to map objects into relations and operate on them
as objects. 

I've yet to see where this has worked out well. Unfortunately, either
because it doesn't map well or maybe because we lack the right approach,
the fit is never good and performance usually suffers. 

On the other hand, if people can apply the OO paradigm to their code,
but still use the relational model for the data, then perhaps it would
work. However, a major part of OO is treating the data as objects, so
you have a fundamental mismatch right from the start. this maybe just a
limitation of how we think about it, but unfortunately, I've seen it
again and again. Hopefully, with PL/SQL based OO, we won't get the
nightmares of 'hybernate' and other OO persistancy/data mapping to
relations, but I'm not confident. 

I can fully understand where your coming from (at least I think I can if
I understand your argument). I am constantly frustrated by (too often)
web) developers who treat the database as a bit bucket and who are not
interested in learning SQL or even udnerstanding the specifics of the
database they are using. They just do the old CRUD and tend to do it
very badly. They tend to only do really basic selects and suck huge
amounts of data into java where they use java objects and iterators to
filter out the data they are not interested in when it could have been
done more efficiently with better SQL. You even get further performance
improvements by smaller result sets being passed around etc. 

My concern with your suggestion is that it just relieves the developers
even further from needing to know/udnerstand anything about the
database. It encourages their ignorance. I guess your approach is more
pragmatic and mine a little more idealistic, but I want developers who
consider it a matter of professional pride to understand the whole tool
chain they are using. Developers need to udnestand the database to
understand how to design their applicaitons. Without this knowledge, we
have this disconnection between the database group and the general
developer group. Developers need to know that Oracle (or even DB2 for
that matter) is not MySQL and that the design you would use for one is
not going to be identical to the design you would use for another. 

>> The main weakness IMO with Oracle's OO stuff is that it is obviously an
>> after thought - something added to the language to hopefully provide
>> Oracle sales with another compliance buzzword. 
>
> I look at it differently.  They've done such a good job at committing to
> a solid foundation that their object oriented features fit in quite
> nicely.
>
>> for me, the only thing it appears to have added is constructors and
>> inheritance. to a large extent, we already had OO with PL/SQL package
>> Maybe this is what you were referring to when you referred to 'not just
>> the object types'? 
>
> Inheritance as well as not instantiable.  The abstract class has alot of
> power allowing you to define methods that must be implemented by the
> implementing objects.  For a simple example, take the TAPI idea.  Now,
> you could have an abstract class called an DML_OBJ that has the not
> instantiable methods insert/update/delete.  (Tom Kyte would shoot me for
> this :-))
>
> It could have attributes on it that represent the columns of its table.
> Then, java could issue
>
> DECLARE
>         v_col_obj some_table_cols_obj := some_table_cols_obj();
> BEGIN
>         v_col_obj.v_col1 := 'AA';
>         v_col_obj.v_col2 := 'BB';
>         v_obj some_table_obj := some_table_obj(v_col_obj);
>         v_obj.insert;
> END;
> /
>
> So, then the OR layer really maps the bean in java directly to the table
> in the database leaving the implementation of the actual DML to database
> code.
>

I've experimented with almost exactly what you have mapped out. While,
from a code perspective, some of it was quite nice, from a performance
perspective it was really really bad. On an Oracle 10gR2 RAC running on
64 bit Linux, the performance hit from applying this style of OO design
was huge. In fact, it was so huge that I was totally discouraged from
even trying to track down why it was so slow. The comparison was with
PL/SQL procedures that did the DML and were called from Java. using this
approach, you get the SQL back into the database wherer it belongs and
the Java code monkeys don't need to do anythin but call the PL/SQL
procedurres/functions - still not my ideal as the Java devs still are
not learning about the database, but it avoids the overhead that appears
to be associated with the Oracle OO stuff and gives much better
performance, so you get the desired outcome, but without the OO overheads.

> To me, it allows us to offer java a "simple" OR layer and take back the
> SQL operations, which should be in the hand of the database developer.
>
> That is why I call it superb.
>
>> If so, I would agree. PL/SQL packages have provided all the most
>> useful properties of the OO paradigm, with the exception on
>> inheritance. 
>
> Abstract classes.  Packages do not have that.

True. 

>> You have encapsulation, polymorphism/overloading of
>> functins/procedures, instance variables, private/public and even a
>> form of constructor with init blocks. It is even debatable that not
>> having inheritance in packages is a loss - inheritance is sometimes
>> very useful, but it is also probably the most misused/abused feature
>> of OO (with the possible exception of operator overloading in
>> C++!). The only thing that would be worse would be multiple
>> inheritance!
>>
>> I've used Oracle's OO types with method and constructors stuff a bit and
>> have found it useful for certain cases. For example, I used some of the
>> facilities in a 'proof of concept' test harness that I came across called
>> 'PLUTO'. The work done by the authors was quite interesting, but the
>> implemeentation had a few problems. I've extended it and have been using
>> it as the basis for experimenting with a simple test harness for PL/SQL.
>> The ideas developed by the original authors (whose names escape me just
>> now - I think the code was on googlecode) are interesting and show quite
>> imaginative original application of PLSQL. I don't know yet whether it
>> will amount to anything relly useful, but its an interesting diversion
>> from less inspiring stuff and it gave me an excuse to play with oracle's
>> 'full' object definitions. 
>
> I've written a dynamicsql_obj that has a sql_string as its main member,
> and methods for appending strings, binding variables, ...  An
> execute_immediate method ... Very nice for the place I entered cause
> they had dynamic SQL all over the place, and tons of unbound.  Now, all
> of it uses an object.  That object also has quite a few ways for the
> coder to "see" the sql being executed which is usually a big shortcoming
> to committing to alot of dynamic SQL (amongst a bunch of other issues)
>
> I have a logging object that offers methods for instantiating it at the
> beginning of some method, tons of methods for sending trace messages
> that hit the trace files as well as dbms_output as well as logging
> tables.
>
> I have a reporting object that offers up 2 not instantiable
> "load_parent_data" and "load_child_data" methods and tons of helper
> procedure/functions which the java code calls.  Those objects fill up a
> temp table which then is accessed by the reporting tool.  The OR layer
> between these objects and the PLSQL that gets executed is embodied in
> about 20 lines of java and an xml configuration file that calls out the
> name of the implementing object.
>
>
>> If the OP wants to apply OO paradigms to his design, then I would say go
>> for it. Personally, I don't think OO is always the right way to go, but
>> it certainly has been the popular choice over the last decade and there
>> is bound to be lots of people who will udnerstand such a paradigm and
>> provide assistance when/if required. . However, I think it wold be a
>> mistake to adopt that paradigm just so that you can avoid using
>> parameters in procedures. 
>
> I agree completely.  Packages and procedures are never going to be
> replaced, even in a heavy OO PLSQL environment.  The OO env allows one
> to use that when it makes sense, and it really does in alot of places.
>
>> Even inside objects, you don't really want all variables to be
>> instance or class variables - sometimes you want values to be short
>> term or have limited lexical scope. 
>
> Well this is a big big big shortcoming of the OO implementation in
> Oracle.  All variables must be instance variables.  There are no hiden
> variables and another issue is each method must be public.  Yeah, this
> stinks, but I do think Oracle will continue to improve things.
>
> Another missing feature is interfaces.  :-( That would come in really
> handy.
>

I guess we will have to wait and see how Oracle develops their OO
support. I still think the mixing of OO and the relational model is
always going to be problematic and am not convinced the OO overheads are
really buying us much and what it is buying us is possibly going to make
matters worse. I can't help but wonder why, despite lots being written
about it, there has never been a successful object database model and
the constant criticism I've seen regarding all object relational mapping
approaches has been that the performance is lousy and usually comes wiht
some fairly significant limitations. However, just now, mainly due to
Java, OO is the main paradigm and I can udnerstand why developers want
to carry that model through to the storage level, I'm just not convinced
it is a good fit because I find any join between the OO model and the
relational model to be an 'arranged marriage'. Of course, just like
arranged marriages, maybe the two will grow to love each other over
time!

there seems to be a bit of a change in the language front just at the
moment. There are lots of new languages coming into existance in recent
years and less seem to be convinced that Java is the one true language.
While many of these languages do support the OO paradigm, other
paradigms are also gaining ground, such as functional programming. Other
database models also appear to be gaining some ground i.e couchdb,
mungodb and that XML one - existdb (???). 

Given the number of new languages that run on top of the JVM and the
number of existing languages that are being ported to run on the JVM, I
would not be at all surprised to find that in the long term, the one
enduring contribution from Java is the JVM. In fact, from what I've
read, many of the features being considered for java 7 and Java 8 are
enhancements to the JVM that will make non-java languages easier to
implement and are of no real benefit to the java language. 

Tim

-- 
tcross (at) rapttech dot com dot au

On 1/30/2010 12:02 PM, Galen Boyer wrote:
....
> I consider it superb, in relation to what you have available in any
> other database vendor, which is nil.  I consider it superb because it is
> starting to allow us database guys to take back what should be handled
> in the database and maybe not lose the OR layer argument so much.
Uhmm.. small history lesson here.
This whole mess started with Ontos, Objectstore, Illustra and the likes. 
Then Informix bought Illustra and integrated it into IDS 9.

Any Informix lurker here will be ready to scratch your eyes out on that 
comment. ;-)

Oracle and DB2 for LUW scrambled to follow Informix since apparently OR 
was the fashionable thing to do (or so Stonebraker told us...).
I was a coop student when I started working on OO (typed tables, views 
and structured types) 14(!) years ago (the ANSI syntax for typed views 
in SQL 99 is mostly based on my externals spec from 1998...).

On the DB2 for LUW side we have never improved anything on OO past DB2 7 
because OO didn't catch on in the mainstream.
Now I'm not intimately familiar with the time line of Oracle's OO 
support, but I do not recall any enhancements in Oracle 11 or 11R2.
Wasn't most of this done by Oracle 9*?

Note that SQL Server completely side stepped OO, but they have deeply 
integrated CLR....that is far from a NIL.

Cheers
Serge
-- 
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab