ADF when you have a lot of PL/SQL?

[st...@vesterli.com]

Reading over the Work-in-progress page at the Wiki, the pre-project
decision point "Skills fit" (Java or PL/SQL) got me thinking:

Should the ADF methodology have two branches?
- one for "greenfield" new apps (where the programmer skill set is
Java-heavy)
- one for new frontends for "old" database apps with a lot of PL/SQL
and possibly PL/SQL table APIs (and with a PL/SQL-heavy skill set)

We can use ADF BC as fronts for the PL/SQL, but will the methodology
need to differentiate? And for the foreseeable future, won't we mainly
be building new apps on top of existing Oracle databases with a lot of
PL/SQL in them?

Cheers

Sten Vesterli

[Rob Nocera]

Sten,

I was thinking along the same lines, at some point during the design, a
decision needs to be made as to what to do with the PL/SQL in the database.


If there are Stored Procedure API's, do you use those API's in ADF or put
the logic in those API's into the middle tier?

What about other Stored Procedures in the database, is there a reason to
move the business logic in those to the middle tier?

If this is an old application that you are "converting" do you migrate some
of the PL/SQL logic in the old application to the database to make the new
application simpler or "thinner"?

Do you take the opportunity to decide what the best place is for each piece
of existing business logic or do you just go with what is there to minimize
the effort?



Robert Nocera
CTO
Vgo Software, Inc
office: 860-533-9383 x102
email: rno...@vgosoftware.com
blog: www.java-hair.com
www.vgosoftware.com

[Chris Muir]

Discussions about thick database vs thin database vs ADF have been
covered by Paul Dorsey in the past. Paul's argument (heavily
paraphrased) is that thick database is key to a successful ADF
project. To quote Aino Andriessen via an Amis blog post (http://
technology.amis.nl/blog/?p=3253), Aino better than I can summarises
Paul's argument at an ODTUG 2008 presentation recently:

"Paul Dorsey with ‘Thick database techniques for Fusion (and other)
Web developers’. He claims that putting business logic (and more) in
the database and reducing the middle tier to its bare minimum improves
a webapplication by factors. It’s scales better, uses less resources,
increases development productivity and reduces the amount of code. His
method relies heavily on hiding the complexity in database views with
instead-off triggers and pl/sql collections. It is certainly a very
interesting and viable approach, but somehow I don’t think the debate
is over yet."

Also check out the ODTUG-java-l mailing list for more discussions on
the thick db approach. An archive can be found here:
http://marc.info/?l=odtug-java-l&r=1&w=2

As for whether the ADF methodology needs to split between the 2 camps,
I don't necessarily see why. I think it's just a decision point on
deciding to leverage the database PL/SQL or not (depending on if you
agree with Paul's point of view or are in the Java camp). ADF in many
cases gives you the ability to integrate heavily with PL/SQL, or
alternatively, go the full Java hog. What you decide to do should be
based around your team skillsets (Java programmers vs PL/SQL
programmers) among other weighted factors, yet overall from the
methodology point of view it's just flagging some efforts will be
database bound, and others Java bound.

CM.

[Rob Nocera]

I always thought that using PL/SQL Collections makes the ADF implementation
multiple times more complex. Am I mistaken with that? Perhaps I
misunderstood the statement though.

Either way, I think it is a decision that needs to be made, as each project
team is going to have their own thoughts about it.

Robert Nocera
CTO
Vgo Software, Inc
office: 860-533-9383 x102
email: rno...@vgosoftware.com
blog: www.java-hair.com
www.vgosoftware.com

 


-----Original Message-----
From: adf-met...@googlegroups.com

[John Flack]

One of the things I'm finding difficult about ADF BC is the fact that
there is a disconnect between updating a VO and the posting of those
changes to the database. For instance, I've been struggling recently
with a failed COMMIT of an INSERT - a trigger did a
raise_application_error. Yet the bad row is still sitting there in
the VO and looks to the user as if the INSERT succeeded. If I was
making all database changes with calls to an API, I think I would have
better control of when changes are posted, and what happens with an
error.

The point - I begin to suspect that you've got to go all one way or
the other. Either all validation is in the middle tier, and there are
NO triggers in your database (I'll make a concession to allow
constraints - the ADF BC framework seems to understand constraints),
or all updates are through an API.

To Rob:
I have query screens with <af:selectMany...> components that bind
arrays in managed beans. These are passed as bind variables in VOs
for which the SELECT looks like "SELECT * FROM
TABLE(my_table_function(:my_bind_array))". Its a little tricky, but
it isn't rocket science.

[Avrom Roy-Faderman]

Ooh ooh! Plug time!

http://www.avromroyfaderman.com/2008/06/the-business-logic-wars/

--
Avrom’s Java EE and Oracle ADF Blog
http://www.avromroyfaderman.com

[Avrom Roy-Faderman]

John -

> The point - I begin to suspect that you've got to go all one way or
> the other. Either all validation is in the middle tier, and there are
> NO triggers in your database (I'll make a concession to allow
> constraints - the ADF BC framework seems to understand constraints),
> or all updates are through an API.

Or you put in a bit of framework code (or, if it makes more sense,
individual entity level code) to help integrate the two tiers. In your
case:

protected void doDML(int operation, TransactionEvent e) {
try {
super.doDML(operation, e);
} catch (DMLException e) {
refresh(REFRESH_WITH_DB_FORGET_CHANGES | REFRESH_REMOVE_NEW_ROWS);
//Throw some sort of exception so the user knows what happened
}
}

For most purposes (e.g., if this is a form) I actually don't think you
want to do this (you'll lose the user's work), but if you really want
failed posts to remove new data from the cache, this will do it.

For more of this sort of stuff, another plug (banner day for it, I see):

http://www.avromroyfaderman.com/2008/06/business-components-without-the-business-part-i/
http://www.avromroyfaderman.com/2008/06/business-components-without-the-business-part-ii/
http://www.avromroyfaderman.com/2008/07/business-components-without-the-business-part-iii/

Best,
Avrom

[Rob Nocera]

John,

I was referring to updates and inserts using PL/SQL tables rather than
selects, though I'm sure that isn't rocket science either.

Considering the bang for the buck you get using JDev's wizards, having to
manually code around those types of issues makes using stored procedures
that take those types of parameters much more difficult to implement in ADF
than updating tables directly and having the rest of the business logic on
your middle tier.

-Rob

 

[Avrom Roy-Faderman]

Rob -

I agree that implementing the manual code to do
inserts/updates/deletes/selects can be a bit annoying, especially compared
to using ADF's wizards. But one point I really want to make with the
reusability stuff is that if you do it right, it's annoying *once per
enterprise*. Set up the right kind of framework code, and you can do all
this sort of stuff declaratively in the editors (making use of custom
properties, generally), at the case-by-case level. Once-per-enterprise
annoyingness really isn't all that annoying, IMO.

One of the projects I'd really like time to work on is something even more
genericizable; I think it's possible to get it from once-per-enterprise to
once period. Of course, for all I know, that sort of thing is what the JHS
team has planned for 11g.

-Avrom

[Eric Marcoux]

Hello,

I think that one branch should be enough. During my past experience with
ADF, I did the following three scenarios :

- implementing the business logic in PL/SQL only and invoking that logic
from ADF Faces
- implementing the business logic in Java only (ADF BC and even EJB with ADF
Faces as the front end)
- implementing the business logic in Java (ADF BC) AND PL/SQL (both)

This is really a design and architecture decision that you must take
depending on how reusable your PL/SQL stored procedures are and also based
on the expertise of your dev team. Technically, there is no barrier, just
compromises in certain cases.

I think a good approach would be to focus on how to create and implement a
good SOA. For me SOA is not necessarily a full fledge BPEL or WS enterprise
architecture but also a business service architecture for web applications.
I did a SOA using ADF BC and another one using PL/SQL procedures and the
difference was not that big when your visual components need to invoke these
services (small technicalities). Of course, in some cases, it may be easier
to use ADF BC or PL/SQL depending on the situation but again, this is not
something that will have the biggest impact in your architecture based on
the experience I have in building web applications using ADF.

Hope this helps.

Eric Marcoux
Conseiller principal en architecture de systèmes (Chief Architect)
Vice-rectorat exécutif, Université Laval
Oracle ACE, Oracle ACE Director, SCJP, SCWCD & SCBCD
Blog: http://emarcoux.blogspot.com


-----Message d'origine-----
De : adf-met...@googlegroups.com
[mailto:adf-met...@googlegroups.com] De la part de st...@vesterli.com
Envoyé : 29 août 2008 09:35
À : ADF Methodology
Objet : ADF when you have a lot of PL/SQL?

[fnimphiu]

I agree with Eric. The use of PLSQL in the database is a design
decision to make. I think among the main motivations to use PLSQL in
ADF is

- legacy PLSQL code
- skill set is PLSQL, not Java making PLSQL the preferred choice
- PLSQL performs data centric tasks (e.g. batch updates etc.)

For the first two cases I think that, because most likely a return
value is to retrieve, using a Web Service abstraction is the better
choice. For the latter the integration that is possible and documented
in the developer guides is sufficient. Note that we do have a PLSQL
wizard for ADF BC on our feature wishlist that wraps a stored
procedure because we see a demand for this. I think the Oracle Forms
block based on a stored procedure is a nice feature. Still, I think
the better implementation is through services. So the methodology
guide should be when to use which approach with PLSQL

Frank

[Eric Marcoux]

In a past project, we have used something called SQL/J (not sure if this is
still available on OTN for download nor if this is still supported by
Oracle) to automatically generate Java code to invoke PL/SQL procedures.
This has been an easy way not to manually manage JDBC code, handle
Collections, Records and Tables and it worked pretty well.

A wizard would be more interesting of course - meanwhile, you may use that
has an option if these libraries are still available on OTN.

Hope this information is useful.

Eric Marcoux
Conseiller principal en architecture de systèmes (Chief Architect)
Vice-rectorat exécutif, Université Laval
Oracle ACE, Oracle ACE Director, SCJP, SCWCD & SCBCD
Blog: http://emarcoux.blogspot.com

-----Message d'origine-----
De : adf-met...@googlegroups.com

[mailto:adf-met...@googlegroups.com] De la part de fnimphiu
Envoyé : 3 septembre 2008 18:57
À : ADF Methodology
Objet : Re: ADF when you have a lot of PL/SQL?

[Chris Muir]

I've certainly used SQL/J in the past and it is a viable option. I'm
not a big fan of these code generator type tools though, given the
lousy maintainable code they seem to generate, including SQL/J. Why?
Having experienced a project in the past which used Headstart for
Oracle Forms, and the client decided after a number of years to stop
upgrading, we were left with the decidely annoying job of maintaining
the Headstart generated libraries, which were never really meant to be
changed by hand, but regenerated via Headstart. As such the code
generated by Headstart was very messy, not easily readable, and
maintainable. The SQL/J code reminds me of this.

Arguable of course I could say the same for ADF BC, that it's a code
generator tool, but I like ADF BC ;-)

(and please note I'm referring to Headstart, not JHeadstart, which I
have no experience with).

As for the provisioning of ADF BC support for sitting on top of PL/SQL
(a'la Oracle Forms blocks stored procedure approach as mentioned by
Frank previously), as I discussed with Lynn Munsinger at ODTUG this
year, if ADF wants to be seen to be competitive against Apex, this is
a core requirement. One of the key arguments of the Apex crowd is
that it (near) seemlessly integrates with existing PL/SQL, and given
Forms programmers moving to JDev only know PL/SQL, this would reduce
learning curve, as well as allowing ADF to sit on top of legacy
systems more easily. Such direct ADF BC->PL/SQL support would bring
JDeveloper a step closer to negating the Apex argument.

(As a separate discussion, ADF also needs to support the ease of
Authentication methods (read: declarative) that Apex does, namely
easily switching between the current ADF JAAS/JAZN approach, and
database user-account authentication. Again this requirement is key
because the Forms crowd are only familiar with the db account method).

Cheers,

CM.

[Nathalie Roman]

Hi gang,

Referring to SQL/J, when you define a webservice on top of pl/sql
function/stored procedure you will be using JPublisher under the hood,
which means the relational to java mapping will be handled by the
wrapper classes.

Using wizard-based approach will always give you the 'generated code'-
aspect, which allows you to develop fast. If the generated code isn't
giving you all the needed functionality you should be deciding if it's
a needed enhancement in the generator or you need to rethink your base
code, e.g. your pl/sql stored procedure.

When using ADF BC or Webservices you still need to be experiences with
the generation framework to be able to grasp what's being generated,
what MVC means what a webservice is all about. That's an investment
each company needs to make once and depending on the skills they
already have they can stick to either PL/SQL or move towards ADF BC
(to have 4GL-experience) or move towards JPA when JEE experience is
already in place. Either way it's indead always a decision choice
which depends on the skill-set already in place and the goal the
company has defined.

Apex is indead a RAD tool, which ADF is not because you need to have
more skills but ADF also has the Web 2.0 collaboration features which
are a must have for Apex as well. If you look at the declarative end-
user support in Apex such active reporting, filtering,
dashboarding, ... which isn't available out-of-the-box in ADF ... we
have some tough competition. On the other hand it's great both UI's
exist, we keep on challenging eachother and adding more features, more
user friendlyness.

Maybe it would be usefull to make a comparison of what needs to be
available in ADF as well, to be more competitive with e.g. Apex and
have more end-user support available.

Kind regards,
Nathalie

[Brenden Anstey]

Hi all,

One of the key successes of any application is the usability of the UI
and in particular it's management of validation and error handling.
I think from a UI usability perspective the validation and error
handling should be as close to the UI as possible. If you are burying
validation deep down in the database then your error stack spans the
the entire MVC stack and into the database. From a performance
perspective this cannot be good because of the communication overhead
between tiers and coordination of error messaging. Also a common
occurrence is that a record is created in ADF/BC but errors when
posting to the database, if you've gone down the thick db approach
it's then up to you to get out the 'mop and bucket' when this happens!

I have used a lot of PL/SQL in Application modules mainly to call
procedures to pre-create a bunch of data or else set database session
variables. However would not use it at entity level because it
undermines one of the primary benefits of ADF BC, code generation and
synchronization. When I think of how many times I have right-clicked
on an EO and selected synchronize with database, the thought of coding
these manually seems in the realm of madman or genius (IMHO!).

The key thing I always consider when doing any PL/SQL in packages or
triggers is keeping the persistence layer in sync. It's not a major
thing, but coding rexecution VO's is needed in most cases which does
detract somewhat from the benefits of coding in the database.

From an experience perspective I've probably coded more PL/SQL the
Java, but I would still opt for coding validation and error handling
in Java in the UI simply because it feels cleaner, simpler, less
clunky and I think it produces a better UI experience.

Brenden

[Chris Muir]

Brendan, given your Java background I think you're broaching the
classic thick/thin mid-tier argument. The Java programmer will want
to take the mid-tier approach, while the database programmer puts all
the logic in the database of course.

Often when we adopt a technology, we need to also adopt the way it
wants to work. So if ADF impresses upon us a certain way to handle
errors, we pursue that approach. With greenfield projects this is
pretty easy as we have no legacy architectures to deal with, and thus
I can see taking on error/exception handling in the way the UI
technology wants to do it as the path of least resistance.

Yet pure greenfield projects are becoming few and far between for some
sites. *All* but 1 of the ADF projects I've worked on had to sit on
top of legacy Oracle database system, with inherited validation and
error frameworks defined within the database. Regardless ADF wants to
work in 1 particular direction with error/exception handling to give a
suitable UI-user experience, certain sites have the need that ADF can
facilitate database errors. Indeed as Oracle are the designers of
ADF, I'd hope they were giving due consideration to this, because they
know their database architecture after all.

Thinking about it, I think this is one of the concepts that a good ORM
has to deal with. Not just relational tables to objects, but
relational errors to the implemented Java exception framework, an EEM
if you will ;-)

It's interesting to note that database error handling is also limited
in Apex. The Apex team hasn't considered yet a wholesome approach to
thrown ORA-20000 database exceptions during processing without
programmer intervention. I would hope for a declarative property and
interception mechanism. So much for Apex's fantastic database
support.

Finally, as a database programmer first, and a Java programmer second,
I still have a fundamental issue with the thin-database thick-middle-
tier approach. I'm willing to bet in 10 years time the Oracle
database will still be going strong at most of my clients' sites, yet
in 10 years time I'm not willing to put money on will ADF still be
around, as the web market is in a complete state of flux. If we all
moved to the new golden challice mid-tier technology X in 10 years
time, that would involve a total rewrite and shift of the mid-tier
logic to technology X from ADF, but if all the logic was put in the
database in the first place, we'd save ourselves a h3ll of a lot of
work. And to prove my fact, this has already happened for our move
from Forms to ADF, where sites are now cursing the fact that they've
hardcoded validation & errors in their Forms apps.

CM.

[Brenden Anstey]

Hi Chris,

Agreed rewriting UI's for an existing legacy apps have their own
challenges, I was really thinking in the context of a greenfields
application.

I also agree with your point on the longevity of the database over UI
technologies (UIX is a perfect example).

I too come from a DB Programmer background, but I do feel that the
thick db approach does introduce it's own set of complexities that
should not be underestimated, particularly in the way that ADF/BC will
have to interact with all the smarts happening in the db. Any notion
that thick db simplifies is a bit of a furfy in my mind..

It's also worth mentioning ADF/BC validators in this context, which
are created declaratively in XML and go a long way reduce the need for
Java coding. If this eventually evolved into a (standardized) BPEL-
style business process model for a page it would have a distinct
advantage over coding business logic and validation in the database or
Java.

regards,
Brenden