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

From: Galen Boyer <>
Date: Sat, 30 Jan 2010 12:02:27 -0500
Message-ID: <>

>> Tim X <> writes:
>>> LPF <> 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).
>>> 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

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


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:// - complaints: ---
Received on Sat Jan 30 2010 - 11:02:27 CST

Original text of this message