Home » SQL & PL/SQL » SQL & PL/SQL » Limitation in UTL_TCP Package
Limitation in UTL_TCP Package [message #405222] Wed, 27 May 2009 01:32 Go to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
I am using UTL_TCP package of Oracle to connect to remote server. As per my architecture, I am opening multiple connections TCP in the same Oracle session.

I have come arround the limiation of 16 connections with this package.

Does anybody have idea how to resolve this issue.

Re: Limitation in UTL_TCP Package [message #405229 is a reply to message #405222] Wed, 27 May 2009 02:11 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The first step would be to read and then tell us what error message you get.

(Or why you think that there is a limit of 16 connections)
Re: Limitation in UTL_TCP Package [message #405245 is a reply to message #405229] Wed, 27 May 2009 03:00 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Thanks for the reply.
I have implemented the UTL_TCP, but when no of open connections are exceeded from 15. It gives an error.

ora-00018 is is a max sessions exceeded error.


please help, how to increase no of session.



Re: Limitation in UTL_TCP Package [message #405248 is a reply to message #405245] Wed, 27 May 2009 03:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, that has nothing to do directly with UTL_TCP.

00018, 00000, "maximum number of sessions exceeded"
// *Cause:  All session state objects are in use.
// *Action: Increase the value of the SESSIONS 
            initialization parameter.


So, as it says, you have to increase the SESSIONS parameter of the database.
Re: Limitation in UTL_TCP Package [message #405313 is a reply to message #405248] Wed, 27 May 2009 06:17 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Thanks for the reply
Bit confusion,

simply increase the number of session on one connection in database or in Operating System.
Bcz through UTL_TCP we are posting transaction on remote servers.

Re: Limitation in UTL_TCP Package [message #405336 is a reply to message #405313] Wed, 27 May 2009 07:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In whichever database is giving you the ORA-00018 increase the Oracle initialization parameter "sessions" either with "alter system" or with the enterprise manager or in the init<SID>.ora file.

How exactly would depend on your exact version, whether the database was started with a pfile or spfile, and possibly on the OS.

Re: Limitation in UTL_TCP Package [message #405463 is a reply to message #405336] Wed, 27 May 2009 23:06 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Basically Error is ORA-30678 too many open connections.

As stated earlier, I am using UTL_TCP Package.
Re: Limitation in UTL_TCP Package [message #405464 is a reply to message #405222] Wed, 27 May 2009 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
30678, 00000, "too many open connections"
// *Cause: An attempt to open a connection failed because too many
// are already open by this session. The number of allowed
// connections varies as some may be in use through other
// components which share the same pool of allowed connections.
// *Action: Retry after closing some other connection. The number of
// connections supported is currently not adjustable.
Re: Limitation in UTL_TCP Package [message #405466 is a reply to message #405222] Wed, 27 May 2009 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I am using UTL_TCP package of Oracle to connect to remote server.
WHY?
When your only tool is a hammer all problems are viewed as nails.
While a hammer is a great tool, it is sub-optimal when the task is to make 2 boards out of 1 piece of lumber.

A shovel is a great tool for making a hole in the ground.
However it is sub-optimal when the wrong end is used against the dirt.
Are you using the "right end" of Oracle?
Re: Limitation in UTL_TCP Package [message #405522 is a reply to message #405466] Thu, 28 May 2009 04:21 Go to previous messageGo to next message
absolutemath
Messages: 11
Registered: December 2007
Location: Karachi
Junior Member
Thanks for the rep.

Then which package I use to accomplish my task.

please advise.

Re: Limitation in UTL_TCP Package [message #405529 is a reply to message #405522] Thu, 28 May 2009 04:52 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
We can not read minds. You never told us what "your task" is.

Usually when you need to do something in remote Oracle databases from inside another database you use database links.
Previous Topic: "LIKE" query question
Next Topic: Oracle 10g : problem order of row in a select request
Goto Forum:
  


Current Time: Sun Dec 04 10:53:05 CST 2016

Total time taken to generate the page: 0.07389 seconds