How does the UTL_HTTP package use Oracle Net

I am using a command like

   UTL_HTTP.set_transfer_timeout (5);

             (   ''
              || 'myuser&password=password&action=update&cacheName=com.rcom.tarcaptu'
              || 're.prxcommon.entity.RTotal&cacheKey=9'
             ).getclob () web_page
     INTO l_web_page

What does TNS have to do with HTTPURITYPE (which is really utl_tcp in a wrapper). I don't understand how TNS gets involved. It would seem to me that the implementation of utl_tcp which in written in C, would create the necessary socket etc. I understand how Oracle Net gets involved for inbound HTTP connections but for outbound connections it doesn't make any sense.

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-12535: TNS:operation timed out
ORA-06512: at "SYS.HTTPURITYPE", line 34
ORA-06512: at line 6

As a side note, the error takes  3 min 44 seconds  before it happens.  It should take  5 seconds to time out, because of  the  UTL_HTTP.set_transfer_timeout (5); command.

The Question:
How can I ensure that control returns to my PL/SQL process after 5 seconds.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yes, that's confusing, but don't worry.

network timeouts are generic errors, the error code 12535 means "time out"  but it's a TNS claimed namespace in the error codes.

You can get that in UTL_TCP doing FTP calls too, and other "TNS" exceptions as well, but there is no sqlnet traffic involved.
mmooreAuthor Commented:

Thanks for the information. I am releaved that SQLnet is not really involved because that doesn't make any sense that it should. However, I am still hoping to get an answer to the question of ....

"How can I ensure that control returns to my PL/SQL process after 5 seconds." I can not afford to wait 3 min 45 seconds while it (whatever it is) times out. If I can't control the wait period then UTL_TCP ( or other packages that use UTL_TCP) is useless for my application.

I'm sorry, I thought I had responded to this thread.  I must have written up my response and forgot to click send.

Anyway, if it's still helpful.....

You can't break out of pl/sql on a timer like that, but you can simulate it.
submit a dbms_job to retrieve your web page and then have your current session sleep (dbms_lock.sleep(5))   for 5 seconds then when it wakes up check to see if the job has completed.  If it has then read the data (presumedly the job would write it to a table for you) if it has not completed then raise an error, log it, wait again, etc.  whatever you need.

Note, 5 seconds might be too quick of a time window for the dbms_job timer to pick up your new job and complete it.  If 5 seconds is a non-negotiable hard limit, you may have to write your process in a java stored procedure that has a more robust tcp api.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mmooreAuthor Commented:
Hi sdstuber,
You did reply before your previous reply, and you gave good information, but you didn't actually answer the question I asked. This time you did. I had considered using dbms_scheduler and the java solution but I was hoping it could be done with pl/sql. quess not.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.