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?

[Webinar] Streamline your web hosting managementRegister Today

sdstuberConnect With a Mentor Commented:
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.

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.

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

From novice to tech pro — start learning today.