Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

APEX Listener and Caching

915154Nov 29 2012 — edited Dec 1 2012
I'm having a discussion with a colleague (ok an argument) so I have a basic question about the APEX Listener and caching for the forum. Our current environment is:

OHS 11g (with the weblogic plug-in) --> WebLogic 11g (with APEX Listener deployed) --> Oracle 11g (APEX packages deployed).

The user goes to the APEX URL via the browser, it's forwarded by the weblogic plug-in to the APEX Listener, which in turn forwards it to the DB. The APEX application processes the request and does its thing to generate an HTML page and returns it eventually to the browser. On and on, etc.

My question is this. Does the APEX listener in this type of scenario send any application specific sql or pl/sql calls to the Database (other than dealing with the connection pool) for processing by the application or does it just act as a proxy and forward HTML to the database for processing by the DB/APEX application which does all the pl/sql calls?

My colleague wants to turn on caching on the APEX Listener but I'm thinking this won't help because I assumed that the listener doesn't generate any application specific SQL or PL/SQL calls to be cached.

Thanks,

rowdycholin

Comments

Frank Kulash
Answer

Hi, @jian-cdo
wonder if it's possible I can store the case when part for each study as a static parameter, then later feed it into the sql query when the study_id is passed.
Sure. That's how dynamic SQL is usually generated: some parts of the query are static (never changing), and other parts depend on something like the data in some table. If you have trouble, post a concrete example. (You can simplify the example: instead of posting a CASE expression with 200 WHEN clauses, post one with only 3 WHEN clauses, but just point out that you really have 200.
Maybe a CASE expression isn't the best tool for the job. Perhaps you can get the same results (perhaps more efficiently) by joining to a table, or with a user-defined function.

Marked as Answer by Jian-cdo · Oct 16 2021
Jian-cdo

Thanks for the prompt reply. So far I will stick with the case expression since it's a legacy query. So here is the example of the query (regardless of other tons of table join and sub query):
case when study_id=38 and maxvisit_order<4 then 1
case when study_id=38 and maxvisit_order>4 and maxvisit_order<10 then 2
......
case when study_id=27 and maxvisit_order<6 then 1
case when study_id=27 and maxvisit_order>6 and maxvisit_order<15 then 2
.....
So like the above two parts (for 2 studies), how I can save them somewhere as a static parameter. Thanks.

mathguy

That kind of case expression is exactly what Mr. Kulash had in mind when he said something like "storing data in a table and using a join may be a better approach". A separate table with columns for study_id, maxvisit_order_low, maxvisit_order_high and the values you must assign - and a join on study_id and inequalities for maxvisit_order - will be much easier to maintain than the monster you have now. When needs change, you would only need to change the data (the additional table), you wouldn't have to hunt through the query itself to change its code. The queries will also run much faster.
You say "I will stick with the case expression." WHY? I don't know about other posters here; but when I see a rejection like that (rejection of excellent advice provided here to help you), without a good reason being given by the poster, I choose to move on to the next thread. Good luck!

1 person found this helpful
Jian-cdo

Thanks for further explanation, now I understand what it means by using join. Yes, now I'd like to get rid of Case.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 29 2012
Added on Nov 29 2012
3 comments
326 views