This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,182 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Problem calling UTL_HTTP in oracle XE.

SamB
SamB Member Posts: 592
edited Jan 25, 2007 2:13AM in SQL & PL/SQL
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:

Comments

  • 84735
    84735 Member Posts: 30
    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
    SamB Member Posts: 592
    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
    rbglossip Member Posts: 670 Bronze Badge
    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
    SamB Member Posts: 592
    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
    rbglossip Member Posts: 670 Bronze Badge
    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
    SamB Member Posts: 592
    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.
This discussion has been closed.