PL/SQL global context and global temporary table in ADF

[Chetan Dihenia]

Hi All,
I have read many different topics around lots of PL/SQL and ADF. I
still want to start this new thread to address one of our biggest road
block of ADF project. Our current project has so many PL/SQL code and
many API calls involve PL/SQL global context setting where other PL/
SQL APIs depend on last calls. Also there are some Global Temporary
tables involve where one API populates this table and other APIs
relying on these rows. Now with connection pooling in place every PL/
SQL call can be in different Database session.
There are some options on table such as -
(1) Writing wrapper in PL/SQL to do all calls in one API
(2) Moving PL/SQL code in Java (Around 75 man-years)
(3) Setting Release Level to Reserved (backward compatible) at
run-time before doing such transaction etc...
(4) Overriding connection pooling (We use web-logic connection
pooling) so that context can be maintained across different Database
sessions

Can anyone shade light on this topic to handle this situation best
way with some pros/coms will be appreciable.

Chetan.

[florin pop]

I don't know exacly how to do it,but i'm thinking of a custom auhuthentication/connection at runtime(authenticate with the database user(temporary tables are ok), if i remember well Steve Muench did this on his 'Dive into ADF ' site).
Another thing would be setting up the application server to do the DataSource by proxy-ing the user passed in the authentication.
This is just a thought of mine, it is not a certified advice

[Marcos Ortega]

Hi, Chetan;

     About Context variables, inside oracle session, you can specialise passivate  / activate methods from  application module; there you can resset the oracle's context variables restoring then from a previous xml node saved on AM's passivate method;

     Now about global temporary tables, humm, that's reminds me Client / Server architeture;

     I consider transforming  then in normal tables , and create more two Pl/Sql calls .

          BeginMyLargeBusiness();

                 Clear the tables from an possible last unterminated process;

          EndMyLargeBusiness();

                 Clear the tables for a normal process finalize;

      And make strategic pontual calls on those procedures;

    Maybe to simple answer; 

           

    

      Marcos Ortega
 Analista de Sistemas
  Campo Grande - MS
http://www.santoandrea.com.br

2009/10/12 Chetan Dihenia <cdih...@gmail.com>

[Chetan Dihenia]

Thanks Marcos and Florin for your valuable inputs. I will try the
methods you have suggested.

In my project currently Database context issue is not as big as
Global Temporary table issue. Marcos you are right that this design
came from our big legacy Client-Server system where one of our main
Business Event module is highly relying on Global temporary table
where this table is getting populated from 1000s of different PL/SQL
APIs and getting flush on user define save() method, which is wrapper
of Database commit method. This temporary table is getting called from
so many places that converting it to regular table may cause too many
redo log entries and Database troubles. Our Forms application will be
still around for few years and changing Database design is bigger step
to take, though we are already considering it.
Before doing too much investment and taking major steps, I would like
to know from experts that Database Global Temporary tables and Global
contexts can't be handled well by connection pooling and web
technologies and its better not to carry it for longer....

On Oct 13, 4:13 pm, Marcos Ortega <mar...@santoandrea.com.br> wrote:
> Hi, Chetan;
>      About Context variables, inside oracle session, you can specialise
> passivate  / activate methods from  application module; there you can resset
> the oracle's context variables restoring then from a previous xml node saved
> on AM's passivate method;
>
>      Now about global temporary tables, humm, that's reminds me Client /
> Server architeture;
>
>      I consider transforming  then in normal tables , and create more two
> Pl/Sql calls .
>
>           BeginMyLargeBusiness();
>                  Clear the tables from an possible last unterminated
> process;
>
>           EndMyLargeBusiness();
>                  Clear the tables for a normal process finalize;
>
>       And make strategic pontual calls on those procedures;
>
>     Maybe to simple answer;
>
>       Marcos Ortega
>  Analista de Sistemas
>   Campo Grande - MShttp://www.santoandrea.com.br
>

> 2009/10/12 Chetan Dihenia <cdihe...@gmail.com>

>
>
>
>
>
> > Hi All,
> >   I have read many different topics around lots of PL/SQL and ADF. I
> > still want to start this new thread to address one of our biggest road
> > block of ADF project. Our current project has so many PL/SQL code and
> > many API calls involve PL/SQL global context setting where other PL/
> > SQL APIs depend on last calls. Also there are some Global Temporary
> > tables involve where one API populates this table and other APIs
> > relying on these rows. Now with connection pooling in place every PL/
> > SQL call can be in different Database session.
> >    There are some options on table such as -
> >      (1) Writing wrapper in PL/SQL to do all calls in one API
> >      (2) Moving PL/SQL code in Java (Around 75 man-years)
> >      (3) Setting Release Level to Reserved (backward compatible) at
> > run-time before doing such transaction etc...
> >      (4) Overriding connection pooling (We use web-logic connection
> > pooling) so that context can be maintained across different Database
> > sessions
>
> >  Can anyone shade light on this topic to handle this situation best
> > way with some pros/coms will be appreciable.
>

> > Chetan.- Hide quoted text -
>
> - Show quoted text -

[Michael Koniotiakis]

This is a realy complex case you are describing.
I can imagine 75 man - years spend to develop things like that.
Was it realy needed.

In order to simplify the problem, i will ask this question:
Are Temporary tables handled by ADF?
I guess not, since the session can be lost in every request.

In cases we had Temporary tables we made them permanent using a
session_id field that was initialised on the ADF sessionInfo bean from
a database sequense

[Simon Haslam]

Chetan's right - setting the app module release level to Reserved is
the only way I can think to preserve the database session to the
application module instance to the browser session, in order that data
in a global temporary table is preserved from request to request. This
is what I'd call running ADF in Client-Server manner - every single
user has their own copy of what would have been thier forms app
running on the app server (much like running Forms over the web too I
suppose).

Obviously this approach has several drawbacks:
* potentially heavy memory requirements in the mid tier, meaning you
will probably certainly need more applications servers than the
default ADF model,
* HA options are pretty restricted - unless your app can recreate the
GTT from scratch without losing state data from a previous request
then a node failure (i.e. effective loss of GTTs too) will mean some
kind of impact on the user,
* scalability and architectural inelegance - this isn't the model we
want to ensure a scalable mid-tier (though this discussion is quite
subtle and I'd need to think it through a bit more),
* future support - at the moment this is I think an uncommon
configuration (though some of the older ADF apps in E-business may use
it I think???) and would be "frowned upon" for new apps so I just
wonder whether the framework will support this mode indefinitely.

On the plus side, this model is like Forms on the web, and if you're
already got a production app that works to your capacity requirements
then it could be considered "scalable enough", certainly when you
consider the amount of recoding (and more significantly retesting)
that would be required to move all the PL/SQL into the mid-tier.

Hope this helps,


Simon

[John Flack]

I've been doing quite a lot with PL/SQL in ADF and I've been think of
writing an article about it, and even may write an XML Extension to
make it a little easier. I have one application that does use global
temporary tables, but because it worked for the small number of users
we have, I never considered the implications of passivation/activation
on this until I heard what Steve Muench had to say about it this
week. Fortunately I'm working on version 2 which was doing away with
the GTT for other reasons. Now I have one more reason - and I hope V2
is operational before my users find out the hard way about this fatal
flaw in V1. That said, you would have no problem using a GTT in a PL/
SQL procedure or function called from ADF if you empty it before using
it, fill it with data and only use the data it contains within the
same procedure or function call.

[Avrom Roy-Faderman]

If you don't need any user interaction between API calls, wrapping all the
APIs into a single one, or simply calling all the APIs one after another
during a single request (activation/passivation only happens between
requests), is certainly your best bet.

Otherwise, I can think of a way to do something like this, but I know too
little about the DB to know what the performance consequences would be.
It's pretty ugly to implement, too, but probably not as ugly as any of the
other techniques you're considering (certainly <<75 developer-years).

1) Replace your GTT to a writeable view on a non-temporary table with one
additional column, BC_SESS_ID. The view looks on the current database
context to get a value for BC_SESS_ID to use in the WHERE clause and in
the INSTEAD OF triggers.
2) Add a String field, sessionStr, to your ApplicationModuleImpl custom
framework class.
3) Implement your own ConnectionStrategy (extending
DefaultConnectionStrategy), overriding createApplicationModule() and
reconnect() to store the SessionCookie's getValue() on the sessionStr of
the application module.
4) As part of prepareSession() and activate() on the application module,
set the DB context to the sessionStr value.
5) As part of afterRollback() on the application module, clear out the
appropriate rows from the table (to mimic "temporariness")

Basically, you'd tracking rows for a particular user's session based on
their uniquely identifying (and persisting over passivation/activation
cycles) SessionCookie.

Thoughts?

Best,
Avrom

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

[Chetan Dihenia]

Hi Avrom,
I would like to explore above possibility in more detail. We are
using JNDI Name for a Data source (NOT JDBC URL) so basically we are
not using ADF Database connection pooling but using default J2EE
container (Weblogic Server) Database Connection Pooling. We are still
using Application Module connection pooling and planning to keep
jbo.doconnectionpooling = false (default setting).

Now let's say we redesign our new system in such a way that we
completely avoid user interaction between API calls (Whenever PLSQL
globals are involved). So for one HTTP request if I call all PLSQL
APIs one after another from BC layer Different Application Modules
(E.g. HRAppModuleImpl and PersonalAppModuleImpl) would it guarantee
that I will get same Database connection for all these sequencial
calls? Reading Chaper 40 of Fusion Developer Guide I understand that
this is the case for JDBC URL (I think that this is not different for
JNDI name case) for ONE Application Module but can't say for sure when
different Application Modules are involved in one Http Request and one
PLSQL call is happening in one Application Module and another in
different Application Module.
Also is there any easy way to test connection pooling's
possibilities (Especially for JNDI case) so that we can reproduce
these cases?

Chetan

[Avrom Roy-Faderman]

Hi Chetan,

I think that, as long as you use jbo.doconnectionpooling=false, an AM
instance never releases its particular DB connection, so even if you have
a pooled datasource, this shouldn't be an issue.

This would work if one of the application module instances were nested in
the other (nested AMs share the same transaction), but not if they were
separate top-level AM instances. But I'd think you'd want to do that
anyway--separate top-level instances would *never* share a DB Transaction,
so it wouldn't even make sense to *want* to share the same temp table (or
any other table that you're going to be writing to--you'd get inconsistent
views of the data).

Yes, there are general warnings (in terms of performance) about using
nested AM instances, but I think this is only a real issue if you nest AM
instances too deeply--a single level of nesting is nothing much to worry
about.

If you use a nested AM, be careful to always drag from the nested instance
in the Data Controls panel. In fact, you should ensure that your
ViewController project doesn't even *contain* a separate data control for
the nested AM--if it does, you're risking using a top-level instance of
that AM (which will have a separate transaction) rather than the nested
instance. In code, use
yourTopLevelAM.getApplicationModule("yourNestedAmInstanceName") to
retrieve the nested instance.

Best,
Avrom

[Chetan Dihenia]

Thanks Avrom. Your inputs are very valuable to take some strategic
decision for my project.

I am working on creating some test cases and post a feedback if I will
find something interesting during the test.


THANKS
Chetan Dihenia


On Oct 17, 1:16 pm, "Avrom Roy-Faderman" <av...@avromroyfaderman.com>
wrote:

> Avrom’s Java EE and Oracle ADF Bloghttp://www.avromroyfaderman.com- Hide quoted text -