could anybody drop example how can I keep my oracle session context sync through FS application session?

We use a lot oracle session context in our db layer (our DB PLSQL framework depend on values put in context). Because db connection from web container not dedicated I wonder how can I make it me virtually sync through one application login / logout session?

asked 27 Sep '13, 14:33

haattila's gravatar image

accept rate: 0%

Hi Atilla,

You should use api_session API's to achieve what you want. Here is a tutorial: http://theformspider.com/learningcenter/tutorial-21-how-to-use-session-variables/

Hope this helps.

Kind Regards


answered 27 Sep '13, 14:37

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
accept rate: 15%

I read it before and everything is clear how can I make some variables global through application user session.

But my case is following:

  1. sing in with application user
  2. put it every global parameter as global variables in FS applicaiton context
  3. use global variables like in Oracle Forms

But i want some global variables put into oracle session context because we use it in our framework on DB level. In step 2 i put these variables into oracle session context. And i in this oracle connection use it regularly.

Because oracle connection brought from pool i have this java connection and oracle session connected and context is filled when i sing in.

What happend when i through application usage got different oracle java connection (could i get it or in every application session is dedicated on java oracle connecti. If you use some pooling mechanism this not garantee to got same java connection as before)? Then i have empty oracle session context but application context is ok.

My question how can i reset my oracle context in this situation?


answered 27 Sep '13, 15:09

haattila's gravatar image

accept rate: 0%


HI Atilla,

You can't reset your oracle session context. There is no magic trick. This is why I suggested to use api_session. api_session is your new context.

If this does not seem feasible there is one more option: Currently, Formspider uses a session pooling mechanism and there is no guarantee that your code will always execute in the same oracle session. This can be changed though. The middle tier code can be modified so that each application runs with a dedicated database session. This is technically possible.

(27 Sep '13, 15:57) Yalim Gerger ♦♦

I have idea to put some code to postDelta and then I can detect change of java connection a reset my oraclce session context.

Is it doable? What You think about this solution?


answered 27 Sep '13, 16:44

haattila's gravatar image

accept rate: 0%

Hi Atillla,

Let me first make sure that I understand correctly what you mean by Oracle session context: In session A you set a package global X to value 123. The next time you call this package global, (at a later point in the applicaion flow) you still want to it return 123, right? That's what you want?

In Formspider, this doesn't work because each time your code executes it runs in a different database session (session B,C,D etc...).

Best to my knowledge, the only way to get 123 when you reference X is to save the value of X to a database table before you finish executing your code and then the next time repopulate X from this table before you reference X.

I don't know of any other way.

(27 Sep '13, 17:16) Yalim Gerger ♦♦

I didn't mean of package global variables as a context variable.

We have database object CONTEXT which is our containter of global variables. We use SYS_CONTEXT (<namespace>,<variable name="">) and DBMS_SESSION.SET_CONTEXT construction.


answered 28 Sep '13, 05:10

haattila's gravatar image

accept rate: 0%

I don't know much about DBMS_SESSION package but SYS_CONTEXT should just work with Formspider. We've been using it in Formspider source code for a while.

(28 Sep '13, 06:49) Yalim ♦♦

Hi Atilla,

I am not exactly sure what you need to achieve, but there is the concept of an 'Global application context' in the Oracle database. You can create a global session context where values are stored with a session id (in the SGA) , so that later when you make another connection with the same Formspider session, you can reuse these session context values. (every connection sets a session identifier for the global context, like DBMS_SESSION.SET_IDENTIFIER (api_session.getId)

In Glassfish you can set this in - JDBC Connection Pool Advanced settings.

See for decumentation and usage examples: http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm#CIHFJHCG

Might be something to consider.

Best, Michiel A


answered 28 Sep '13, 05:42

Michiel%20A's gravatar image

Michiel A
accept rate: 13%


Your information would be usefull for us. We will investigate it in this direction. Thx!


answered 28 Sep '13, 07:02

haattila's gravatar image

accept rate: 0%

Global Oracle Sesssion context would be a solution in our situation with combination of FS api_session.getId value.

Thank's a lot Michiel and Yalim!


answered 28 Sep '13, 07:31

haattila's gravatar image

accept rate: 0%


I have a datasource based on a view using oracle context:



column2, ...

from MY_VIEW t

where ... and ..

f.REINV_TYP = ctx_pkg.get_context('MY-CONTEXT-VAR')

I use this view as a datasource in formspider.

case 1) when I have set a concurrency column in datasource, I cannot save a new added record(a lot of no data found message are sent by docommit method)

case 2) when i have NOT set a concurrency column in datasource, saving a new record does work!

I supose that docommit method does some validations in case of concurrency column exists, and that docommit is executed in ANOTHER session than my code(that launched docommit), so it does not see whatever context values are set in my session, and this is the reason for those NO DATA FOUND messages.

This is strange, as I've set api_application.setStateful(in_stateful_yn => 'Y'); in post_open event of application.

Anyway, I'd appreciate any insights you'll poste here about how concurrency work.

Thank you!


answered 21 Jun '16, 08:54

brg's gravatar image

accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 27 Sep '13, 14:33

Seen: 2,777 times

Last updated: 21 Jun '16, 08:54

© Copyright Gerger 2017