ODAC + Kylix + external proc & assigning session

Peter9^2 · Post by **Peter9^2** » Sun 05 Dec 2004 17:56

Hello,

I need to call external procedure from Oracle. The procedure must
assign session # it receives (in that call, retrieved through context/OCI)
to ODAC components. Is this possible with ODAC ? I saw TOraSession.
AssignConnect procedure, but it requires other TOCIConnection object.
Only thing I get from server is its context, thus I don't have
TOCIConnection object. Is it possible to prepare TOCIConnection
object only by knowing context ? Or is there any other possibility to
accomplish this ?

Thank you very much for any help !

Peter Skvarenina

Paul · Post by **Paul** » Tue 07 Dec 2004 07:31

You cannot create TOraSession of TOCIConnection with the specified session ID or assign session ID to the existing session.

Peter9^2 · Post by **Peter9^2** » Tue 07 Dec 2004 10:12

Hm, that is unfortunate.
Nevertheless, I was experimenting a bit and I saw undocumented
procedure TOCIConnection.SetSvcCtx. I thought, that it was provided to
change server context of connection. So I tried to use it... It was also
necessary to comment out CheckOCI80 call in SetSvcCtx, because I'm
using Oracle 9.2.

I got 'ctx' context from Oracle to my external procedure.
Then I tried following code:

l_OCIConnection := TOCIConnection.Create;
l_OCIConnection.SetSvcCtx (pOCISvcCtx (ctx));
l_OraSession := TOraSession.Create (Nil);
TOraUtils.SetIConnection (l_OraSession, l_OCIConnection);
l_dts := TOraDataSource.Create (Nil);
l_Query := TOraQuery.Create (Nil);
l_dts.DataSet := l_Query;
l_Query.Session := l_OraSession;
l_Query.SQL.Text := 'SELECT * FROM SYS.TEST';
l_Query.Open;

And there I've got segfault.
Maybe I'm doing something wrong... Maybe I misunderstood SetSvcCtx
method...
Is there any possibility, to enhance ODAC to be able to run
in external procedures using Oracle provided context ?

Thank you !

Peter

Paul · Post by **Paul** » Wed 08 Dec 2004 10:07

You cannot assign any value received from Oracle server to "SvcCtx" parameter. Let's
try to review your stored procedure. What is the type of "session #"? How did you get
"session #" in your stored procedure?
Why do you want to use it in client session object? What are your purposes?

Peter9^2 · Post by **Peter9^2** » Wed 08 Dec 2004 17:20

I'm sorry, probably I confused you with inaccurate terminology.

I need to write Oracle external procedure in Kylix. It means, that if I use
ODAC, ODAC must be able to use connection to Oracle according to the
context (=pointer or handle, I don't know what it is in reality) it receives
from the Oracle in that call. You receive context when you specify WITH
CONTEXT in PL/SQL external library definition. Using context, you are able
to access session variables. Therefore, I thought that SetSvcCtx changes
context of TOCIConnection to the one provided by Oracle, and then I can
safely assign this connection to ODAC's session object to be able to use
ODAC in external procedure. Surely, I can create another connection with
ODAC (this probably works, but I haven't tested it), but I rather want to
attach ODAC to the context provided by Oracle to use connection
properties of the external procedure caller, and thus be in the same
session as the caller.

I hope I explained it a little better...

Is there any possibility to use ODAC this way ? If not, do you plan
to include support of external procedures in future releases ?

Thank you !

Peter

Paul · Post by **Paul** » Thu 09 Dec 2004 10:52

Now I understand you. ODAC does not support Oracle external procedure development now. We will review in the future. So you cannot work with OCIExtProcContext type directly. You can release it yourself in your application. Please see OCIExtProcGetEnv function how to get needed handles.

Peter9^2 · Post by **Peter9^2** » Thu 09 Dec 2004 12:07

Thank you for your answer. I'll continue by calling OCI itself.

Peter