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.
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
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;
> 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.
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;
> > 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.
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
On 13 Οκτ, 05:44, Chetan Dihenia <cdihe...@gmail.com> wrote:
> 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'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
On Oct 15, 3:53 pm, Michael Koniotiakis <mko...@hotmail.com> wrote:
> 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
> On 13 Οκτ, 05:44, Chetan Dihenia <cdihe...@gmail.com> wrote:
> > 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.
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.
On Oct 15, 11:53 am, Simon Haslam <Sim...@veriton.co.uk> wrote:
> 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
> On Oct 15, 3:53 pm, Michael Koniotiakis <mko...@hotmail.com> wrote:
> > 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
> > On 13 Οκτ, 05:44, Chetan Dihenia <cdihe...@gmail.com> wrote:
> > > 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.
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.
> 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.
> On Oct 15, 11:53 am, Simon Haslam <Sim...@veriton.co.uk> wrote:
>> 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
>> On Oct 15, 3:53 pm, Michael Koniotiakis <mko...@hotmail.com> wrote:
>> > 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
>> > On 13 Ïêô, 05:44, Chetan Dihenia <cdihe...@gmail.com> wrote:
>> > > 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.
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?
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.
> 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?
> 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
> > 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?
|
