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

s_franklinCommented:
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

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