I am trying to create a stored procedure that request some XML data from a service. I have found several examples on-line and all of them point to using this UTL_HTTP package. However, every time I tried to compile my store procedure with that I get the error:
PLS-00201: identifier 'UTL_HTTP' must be declared
Here is the basic skeleton of the code I want to use.
PROCEDURE GET_XML_DATA2 AS
BEGIN
DECLARE
v_soap_request VARCHAR2(32767);
v_soap_response VARCHAR2(32767);
v_http_request UTL_HTTP.req; --Fails here
v_http_response UTL_HTTP.resp; -- Fails here too
v_action VARCHAR2(4000) := '';
BEGIN
null;
END;
END GET_XML_DATA2;
It fails in the indicated lines and does not compile. I am using Oracle Express Edition and I have already tried to grant my user execute rights to that package. It did not work. What else can I look at? What else could be causing this? Thanks!
As you already figured out yourself, this seems to be a permission problem. Your user does somehow not have access to the UTL_HTTP package. Make sure your user has the EXECUTE permission on the package:
GRANT EXECUTE ON SYS.UTL_HTTP TO my_user;
Note that you might have to do this as SYS.
Using SQL Developer (which I can recommend if you're doing PL/SQL development), see if you can then look at the package somehow. If that does not help, please post the permissions that your user currently has.