using utl_http from oracle DB

I'm using oracle DB 8.1.5 on sun solaris os7. I'm trying to the function request from package utl_http, and getting an error on any URL: 'ORA-06510: PL/SQL: unhandled user-defined exception'. What is the reason for the problem, and how can I solve it??

Thanks,
   Ofra
 
ofraAsked:
Who is Participating?
 
s_franklinConnect With a Mentor Commented:
Always be sure to check the package specification when you run into problems like this. A tool like pledit from Benthic software easily lets you browse and view the spec/body of stored logic on the server.

The package spec frequently gives you good hints and additional detail about the functionality you're trying to use. In this case, the utl_http package throws exceptions that appear as user-defined exceptions unless caught explicitly.

You should certainly look for:
init_failed
request_failed
malformed_url

Once you catch these exceptions explicitly, you will be able to easily see what is going wrong and deal with it appropriately.

I believe there is an issue with size that you also have to watch out for. You may need to look at request_pieces(...) instead of request(...). I dn't think this throws an exception (request of a file greater than 2000 bytes) but you may be wondering why you're not seeing the entire file you wish to download.

Steve
0
 
ofraAuthor Commented:
Steve,

 Thanks for your quick response.
Can you tell me more about this pledit tool?

 I tried to get the exact exception with sqlcode and sqlerrm - but all I got is 1 and  User-Defined Exception (respectively). So how do I catch these exceptions ?? maybe I need some special initializations for using this package?

   Regards,
     Ofra


0
 
s_franklinCommented:
Simplify your code so that it looks like the following. In fact, use the following and make sure it works:

PROCEDURE doit
IS
  htmlChunk VARCHAR2(2000) := NULL;
BEGIN
  htmlChunk := utl_http.request('http://www.cnn.com/');
  dbms_output.put_line('Data retrieved:');
EXCEPTION
WHEN utl_http.init_failed then
  dbms_output.put_line('Init failed');
WHEN utl_http.request_failed THEN
  dbms_output.put_line('Request failed');
END;

If you substitute a bad url (i.e. 'http://www.cnn.cmmz') you will get a "request failed" because the utl_http package couldn't retrieve the page successfully. A valid URL will give you a "data retrieved" in your console output window. You would run the above as "exec doit;" after storing the procedure on your server.

Steve
0
 
s_franklinCommented:
Simplify your code so that it looks like the following. In fact, use the following and make sure it works:

PROCEDURE doit
IS
  htmlChunk VARCHAR2(2000) := NULL;
BEGIN
  htmlChunk := utl_http.request('http://www.cnn.com/');
  dbms_output.put_line('Data retrieved:');
EXCEPTION
WHEN utl_http.init_failed then
  dbms_output.put_line('Init failed');
WHEN utl_http.request_failed THEN
  dbms_output.put_line('Request failed');
END;

If you substitute a bad url (i.e. 'http://www.cnn.cmmz') you will get a "request failed" because the utl_http package couldn't retrieve the page successfully. A valid URL will give you a "data retrieved" in your console output window. You would run the above as "exec doit;" after storing the procedure on your server.

Steve
0
 
s_franklinCommented:
Sorry - they really have to make it so a refresh doesn't resubmit :)

Steve
0
All Courses

From novice to tech pro — start learning today.