Re: Passing values between procedures(without actually passing them)

>>> Tim X <timx_at_nospam.dev.null> writes:
>>>
>>>> LPF <eng.lpsff_at_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). 
>>

>>
>> 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