Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Pinning a tcp connection in oracle.

Re: Pinning a tcp connection in oracle.

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 2 Apr 2005 04:35:04 -0800
Message-ID: <1112445304.463843.130230@l41g2000cwc.googlegroups.com>

DaLoverhino wrote:
> Hello. I need to send data to a server using utl_tcp.connection.
Now,
> the problem is the set of procs that do this get called often and
> there's an overhead caused by opening and closing a connection every
> time the proc is executed.
>
> I'd like to have one connection used, and be shared by all the procs
> across any session open. How may I be able to do this?
>
> What I'd like to do is 'pin' the connection into SGA. Using
> dbms_shared_pool.keep(), but it appears this only works with packages
> and procedures, but not variables.
>
> Is there a way to create a global connection in Oracle to be shared
by
> all procs across sessions?
>
> Thanks.

You can keep the connection live ONLY within a session by using package variables. However... the same connection CANNOT be shared by other sessions.

One of the ways I can think of to work around this situation is to create a table that different sessions can post their requests to with a e.g. "Posted" flag. Create a background job that wakes up e.g. every 5 minutes or so and processes all the "Posted" requests. Mark the flag afterwards e.g. as "Processed" or "Failed". Now you could have multiple requests from different sessions that can all be handled within one connection every 5 minutes. This can also be more secure as the background job user is the only one who can send data on a tcp connection.

Regards
/Rauf Received on Sat Apr 02 2005 - 06:35:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US