utl_http.req and resp 1

[huchen]

I am trying to use utl_http package. The anonymous block works fine, but it has problem when compile as a named function. Could you tell me why? I am using oracle9i and have DBA role. Please see script below:

--- following works in anonymous block in sqlplus
DECLARE
req Utl_Http.req;
resp Utl_Http.resp;
name VARCHAR2(256); -- Response header name
value VARCHAR2(1024); -- Response header value
line VARCHAR2(4000); -- Response body
BEGIN
req := Utl_Http.begin_request ('

http://www.yahoo.com',

'GET');
resp := utl_http.get_response(req);
dbms_output.put_line('HTTP response status code: ' || resp.status_code);
dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);
utl_http.read_line( resp, line, true );
dbms_output.put_line(line);
utl_http.end_response(resp);
dbms_output.put_line('end');
end;
/


-- same as above, but following does not work in sqlplus:
CREATE OR REPLACE FUNCTION test_http
RETURN VARCHAR2
IS
req Utl_Http.req;
resp Utl_Http.resp;
name VARCHAR2(256); -- Response header name
value VARCHAR2(1024); -- Response header value
line VARCHAR2(4000); -- Response body
begin
req := Utl_Http.begin_request ('

http://www.yahoo.com',

'GET');
resp := utl_http.get_response(req);
dbms_output.put_line('HTTP response status code: ' || resp.status_code);
dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);
utl_http.read_line( resp, line, true );
dbms_output.put_line(line);
utl_http.end_response(resp);
return line;
end;
/

Thank you

 

[Dagon]

It would help if you gave us the compile error message you're getting (i.e. do a "show errors" or select * from user_errors where name = 'TEST_HTTP').

 

[huchen]

Here are the errors:

4/13 PLS-00201: identifier 'UTL_HTTP' must be declared
4/13 PL/SQL: Item ignored
5/13 PLS-00201: identifier 'UTL_HTTP' must be declared
5/13 PL/SQL: Item ignored
10/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

10/5 PL/SQL: Statement ignored
11/5 PLS-00320: the declaration of the type of this expression is
incomplete or malformed


LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5 PL/SQL: Statement ignored
12/5 PL/SQL: Statement ignored
12/59 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

13/5 PL/SQL: Statement ignored
13/61 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

14/5 PL/SQL: Statement ignored
14/25 PLS-00320: the declaration of the type of this expression is

LINE/COL ERROR
-------- -----------------------------------------------------------------
incomplete or malformed

16/5 PL/SQL: Statement ignored
16/27 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

 

[Dagon]

Your problem is the old chestnut that you are trying to create an object based on another object to which you have access via a role rather than a direct grant. You need to log on as sys and issue a "grant select on utl_http" to the user under which the package is being created.