Search code examples
plsqloracle-sqldeveloperreserved-wordssettings

Grant UTL_HTTP permission in PLSQL

I would like to get HTML content from a certain webpage in my function. I read I can do it with the UTL_HTML package in PLSQL. So I made the following code in the project:

v_webcontent := utl_http.request(v_weblink);

Here the v_webconent and v_weblink are declared earlier. running this in de function gives an PLSQL exception: PLS-00201: identifier 'UTL_HTTP' must be declaredI guess this problem is because the package isn't available (from this link: same error message).

I followed the advice. So I created a new database connection in sql developer as the SYSTEM role (SYS didn't work, it sayd I could only logon using SYSDBA or SYSOPER but both wouldn't take the standard password I created with the database). Then I entered the code in the link above.

GRANT EXECUTE ON SYS.UTL_HTTP TO [database];

The user I created is named 'Database'. It first gave me an error without the [] square brackets. Table or view does not exist so I then put the brackets around it. Now it gives error:

    Error starting at line : 1 in command -
GRANT EXECUTE ON SYS.UTL_HTTP TO [database]
Error report -
SQL Error: ORA-00987: missing or invalid username(s)
00987. 00000 -  "missing or invalid username(s)"
*Cause:    
*Action:

So I have no idea how to fix this. In the link above OP said that he got an other error, so I also checked if I didn't have the same problem. I entered:

SELECT * FROM dba_objects WHERE object_name='UTL_HTTP'

It returned 4 entry's. With owners: SYS, SYS, PUBLIC and APEX_040000.

Can somebody help me? Do I need to logon as SYS and with what passwords?

Solution
    1. Log on as SYS AS SYSDBA.
    2. Execute grant execute on sys.utl_http to "Database"; Do not use any square brackets!

    That should work.

    Piece of advice: Do not name your DB user 'Database'.

    To reset your SYS password

    1. Run cmd.exe as administrator.
    2. cd to your ${ORACLE_HOME}/database.
    3. Find the PWDsomething.ora file there (where something will be your instance name), copy its name (into clipboard).
    4. Run orapwd file=PWDsomething.ora password=SomePasswordOfMine force=y, where PWDsomething.ora will be replaced with the file name from the step 3 and SomePasswordOfMine must be replaced by whatever password you wish to have.

    That might work.