Problem calling UTL_HTTP in oracle XE.

SamB

I'm running 10g express and have tried running this connected as system well as other users.

SELECT COUNT(*) from dba_objects where object_name='UTL_HTTP' AND OBJECT_TYPE ='PACKAGE';

COUNT(*)
1
declare
req utl_http.req;
BEGIN
NULL;
END;
ORA-06550: line 2, column 9:
PLS-00201: identifier 'UTL_HTTP' must be declared
ORA-06550: line 2, column 9:

84735

SQL> select owner from dba_objects where object_name='UTL_HTTP' AND OBJECT_TYPE
='PACKAGE';

OWNER
------------------------------
SYS

SQL> declare
2
3 req sys.utl_http.req;
4
5 begin
6 null;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL>

Depends on whic user you are running it with...

SamB

There exists a public synonym for UTL_HTTP so I didn't think I would need to prefix it with SYS.

I got the following error when I ran your code

ORA-06550: line 3, column 6:
PLS-00201: identifier 'SYS.UTL_HTTP' must be declared
ORA-06550: line 3, column 6:

rbglossip

Does the user you are logged in as have privilege to execute UTL_HTTP? There is a public synonym for UTL_HTTP but PUBLIC does not, by default, have execute privilege on UTL_HTTP.

SamB

I am doing this through the sql command line (Wont have access to any other interface until tomorrow) in APEX after logging in as system.

rbglossip

Out of the box, SYSTEM does not have execute privilege on UTL_HTTP in XE

$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 25 00:28:09 2007


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production


SQL> desc utl_http

PROCEDURE ADD_COOKIES

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 COOKIES                        TABLE OF RECORD         IN

.

.

.

PROCEDURE WRITE_TEXT

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 R                              RECORD                  IN/OUT

   URL                          VARCHAR2(32767)         IN/OUT

   METHOD                       VARCHAR2(64)            IN/OUT

   HTTP_VERSION                 VARCHAR2(64)            IN/OUT

   PRIVATE_HNDL                 BINARY_INTEGER          IN/OUT

 DATA                           VARCHAR2                IN

SQL> conn system

Enter password:

Connected.

SQL> desc utl_http

ERROR:

ORA-04043: object "SYS"."UTL_HTTP" does not exist

SamB

Thankyou this does seem to be the problem in this case. I will probably have to wait until I have access to the machine to grant this privelege since the only access I have now is remotely through APEX.