LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Team,

We have an option in oracle EBS (multi org) to set the session context for org_id so that only that specific organizations values are selected in the session
exec mo_global.set_policy_context('S',81);

I am unable to replicate the same functionality in Lavastorm where each DB query node selects from tables and does not care about session context.

My questions:
1. Is there a way to set the session context at BRG level?
2. Is there a way to set the session context (using exec statement) at node level?

Please help me ASAP!

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Bharath, unfortunately, I do not believe there is a way to do this. Currently, each DB Query node executes as a single connection. Since sessions are tied to a connection, there is no way to share information across a single session between node executions. Is that what you are asking? I am not exactly clear, my apologies, on the specific meaning of "setting a session context" in this regard. Is that something you can set on a connection, but that you have created outside our application? If that is the case, there might be a way; but if a session context is tied to a session (I think it is?) there is no way to do this, currently.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Rich,

In PL/SQL a complete package can execute in a session. So if I run a command like mo_global.set_policy_context('S',81);, it will be applicable for whole package execution. This is what I refer to as setting a session context.

1. "Is that something you can set on a connection, but that you have created outside our application? If that is the case, there might be a way;" - What is that?

2. Can I have a execute command and a query in a single node? [IMPORTANT]

Thanks in advance, Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: matt

Originally posted by: snbkumar
					

Hi Rich,


In PL/SQL a complete package can execute in a session. So if I run a command like mo_global.set_policy_context('S',81);, it will be applicable for whole package execution. This is what I refer to as setting a session context.


1. "Is that something you can set on a connection, but that you have created outside our application? If that is the case, there might be a way;" - What is that?


2. Can I have a execute command and a query in a single node? [IMPORTANT]


Thanks in advance, Bharath
			

In response to 2, using the DBExecute node you can execute multiple SQL statements within the same node and therefore within the same session. To execute DML and other non-select statements you will need to use the sqlNonSelect() BRAINscript operator. For select statements use the sqlSelect operator. See the BRAINscript help for more information.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Matt,

I just tried using DBExecute option for my functionality. It works great. Thank you.
Can you also check if the Development team can try to incorporate this in a future release?

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Bharath, I am not sure what specific functionality you are looking to incorporate in the future? You are using the DBExecute, and it sounds like it has that functionality and is working now?

Do you mean you just are looking for another parameter on the node to specifically set the session context? If so, you might be able to construct one and place in your library for reuse, where you can get the session context, and partially populate the DB Execute with that parameter referenced. Does that make sense? If I am missing something, please let me know and we can great an enhancement request to capture it.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: snbkumar

Hi Rich,

What we do is:
1. Set oracle session context using: exec mo_global.set_policy_context('S',81);
2. Run any query in session. eg: select * from cn_commission_lines; (It will use the context 81) by oracle functionality.

What you ask us to do: (as I understand)
1. Create parameter with session context value (81);
2. Use it in each query eg: select * from cn_commission_lines_all where org_id=81; (not good, because of extra conditions)

What Matt suggested:
1. In DBExecute, execute: exec mo_global.set_policy_context('S',81);
2. In same DBExecute, have sqlSelect ("select * from cn_commission_lines;");
In this method, step1 is executed for each node unnecessarily.

Thanks,
Bharath

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Bharath, that's close. What I was suggesting is this:

Create a library node, called "DB Execute with Context" for example, and add a param called "OracleContext" to it. Then configure the script to be prepopulated with:
exec mo_blobal.set_policy_context('S', {{^OracleContext^}});

Or something like that. You could introduce a new query parameter, hide the existing, and make it a bit cleaner, but hopefully you get the idea. You are correct though, with this mechanism, you are setting the context on each node, which above you mention I believe as "step1 is executing unnecessarily"--however it is necessary, in that we don't have a session that stays alive across nodes, so you need to do this. There is an enhancement request for this functionality, Issue 4556. FYI, this is not currently scheduled for any upcoming release at this time.

Cheers
Rich