Forum Stats

  • 3,852,068 Users
  • 2,264,066 Discussions
  • 7,904,960 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.