Connection Limit with FTP Package

[dtseiler]

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.

[Tim...]

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...

[dtseiler]

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]

The error that is returned is "ORA-29260: network error: TNS:lost contact".

[Tim...]

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...

[dtseiler]

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.

[Tim...]

Hi.

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

Cheers

Tim...

[dtseiler]

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.

[Tim...]

Hi.

That certainly sounds like a plan.

Cheers

Tim...

[dtseiler]

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.

[Tim...]

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...