Connection Limit with FTP Package

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Connection Limit with FTP Package

Postby dtseiler » Thu Jan 17, 2013 6:24 pm

Hi Tim. This might be an issue you are familiar with as I'm told a colleague already spoke to you about it.

I have a client that is using your FTP PL/SQL package as a part of their order-taking process (contacting 3rd parties). They found things were choking and I'm told that when my colleague asked you about it, you found that there was a connection limit of 11? The client doesn't have any more details and my colleague is engaged elsewhere so I want to get something in the public forum here. If you recognize this, can you explain for my benefit? Is there any way to increase this?

Cheers,
Don.
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby Tim... » Thu Jan 17, 2013 7:10 pm

Hi.

I can't remember anything about a connection limit of 11. I get a lot of questions though, so maybe I've just forgotten. :)

Do you know anything else about the issue that will help me search for it? If it was a conversation on the forum I should be able to find it. Do you know the name of the person. Perhaps that will help me find it?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Connection Limit with FTP Package

Postby dtseiler » Thu Jan 17, 2013 7:36 pm

It was some time in 2012. I'm told Kellyn P. contacted you.

My understanding (told to me by client contact) is that once they get to 11, other attempts fail. I've asked him to confirm this and to try to provied me with any error message.
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby dtseiler » Thu Jan 17, 2013 7:57 pm

The error that is returned is "ORA-29260: network error: TNS:lost contact".
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby Tim... » Thu Jan 17, 2013 8:08 pm

Hi.

I feel like I would have remembered if Kellyn had asked me, but to be fair I have a terrible memory. If I don't write something down it doesn't exist. :)

If you can give me a little more information I'll happily look into it though.:)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Connection Limit with FTP Package

Postby dtseiler » Thu Jan 17, 2013 8:10 pm

Check that, the error is "ORA-30678: too many open connections"

I didn't see it since it hasn't happened since July but they are hoping to use the package more and expect it would happen more frequently then. Now that I know the error I can perform my own due diligence now.

From what I see, looks like UTL_TCP has a connection limt of 16 per session. I'm guessing the client code needs to properly close these.
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby Tim... » Thu Jan 17, 2013 8:35 pm

Hi.

OK. That error would make more sense. Certainly they need to close open connections. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Connection Limit with FTP Package

Postby dtseiler » Thu Jan 17, 2013 9:05 pm

I do see they are callinng the logout procedure in your code, looking for other uses of UTL_TCP that might be leaving open connections.

Otherwise I have suggested simply calling UTL_TCP.CLOSE_ALL_CONNECTIONS at the start of a new order, since sessions are kept open by the connection pooling.
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby Tim... » Thu Jan 17, 2013 9:06 pm

Hi.

That certainly sounds like a plan. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Connection Limit with FTP Package

Postby dtseiler » Tue Feb 12, 2013 6:49 pm

Tim,

In PUT_REMOTE_ASCII_DATA(), I see you call UTL_TCP.CLOSE_CONNECTION(). Is there any value in calling the LOGOUT() function after this? The code path where my client is having the TCP connection limit problems includes a section that only does this with FTP:

Code: Select all
ftp.login
ftp.ascii
ftp.put_remote_ascii_data
ftp.logout


Anyway, so far I'm not seeing any place that connections are not being properly closed, but I did come across this apparent redundancy in walking through the code path. Let me know what you think.
dtseiler
Member
 
Posts: 17
Joined: Mon Nov 10, 2008 6:59 pm

Re: Connection Limit with FTP Package

Postby Tim... » Wed Feb 13, 2013 2:14 pm

Hi.

The first thing the procedure does is open a passive connection.

Code: Select all
l_conn := get_passive(p_conn);


The close you mention is of that passive connection, not the main connection.

Code: Select all
UTL_TCP.close_connection(l_conn);


Notice the "l_conn" connection is closed, not the "p_conn" connection, which is passed in as a parameter.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests