Skip to content

troubleshooting oracle utl_http package – for the new oracle dba or developer





you are a new oracle developer or dba and you would like to learn about to debug the utl_http package. Below metalink article gives you more info.

 

Troubleshooting the UTL_HTTP Package [ID 230917.1]

To Bottom


Modified:07-Mar-2012Type:TROUBLESHOOTINGStatus:PUBLISHEDPriority:3
Comments (0)

 

 

Applies to:

Oracle Fusion Middleware – Version: 9.0.2.0.0 to 10.1.2.3.0 – Release: 9iAS to AS10gR2
PL/SQL – Version: 9.0.1.0 to 10.2.0.1   [Release: 9.0.1 to 10.2]
Oracle Server – Enterprise Edition – Version: 9.0.1.0 to 10.2.0.1   [Release: 9.0.1 to 10.2]
Oracle Security Service – Version: 9.0.1 to 10.2.0.0   [Release: 9.0.1 to ]
Information in this document applies to any platform.
Checked for Relevance on 07-Mar-2012

Purpose

SCOPE & APPLICATION
====================

This document is intended to assist the user when troubleshooting the UTL_HTTP
package. It can also assist a Support Engineer when deciding which competency
area is best suited to handle the different facets of this package. This is a
document that can followed with a Customer and Support Engineer across different
competencies. It may be an initial Oracle Support triage document in deciding
where an actual UTL_HTTP issue should be assigned. Its goal is to decide where
a problem exists within the UTL_HTTP database PL/SQL package, in order to take
any etxra steps to diagnose and solve a problem

Last Review Date

March 7, 2012

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

About the UTL_HTTP Package

==========================

 

The actual UTL_HTTP packages is a PL/SQL package. Its use allows a method to

obtain text from a given webserver page, (html text), and use it within your

application,(usually parsing the text for data). Your application would be

a PL/SQL procedure that is executed within the database, and makes a call to

this UTL_HTTP package, passing in desired parameters. Once processing the

results, the application can do a variety tasks.

 

While it is named with “http”,it is not to be confused with the PL/SQL Toolkit

used with the Oracle HTTP Server to access procedures in the database,

generating html pages back to the user. While UTL_HTTP can be used within an

application accessed over the web, its processing does not send anything back

to the browser, unless a further point of the custom application makes a call

to the PL/SQL Toolkit, (OWA and HTP packages).

 

UTL_HTTP is used within the database, makes an http/https connection on the

internet/intranet, and brings back text for an application to process. No

HTTP Server, except that which it is accessing, is needed. It can be *any*

webserver that serves http/https requests. When making HTTP callouts from

PL/SQL or SQL, it basically turns the database into a text-based browser.

 

 

 

Troubleshooting the UTL_HTTP Package

====================================

 

The UTL_HTTP package is used to access a given URL from either an internal,

external or secure web site. Trouble shooting this package may require

expertise from different competence area. This note will step you through a

checklist of items. Depending on where in the checklist an error occurs, this

Note will determine the competence area best suited for further assisting the

customer with the actual issue at-hand.

 

As each step in the Note is accomplished, the associated SR should be updated

accordingly. Also, the basis for transferring to another competency area should

be justified by the associated step that failed.

 

 

Step 1: Database Check

======================

 

– Verify the UTL_HTTP Package is valid and privileges are set correctly.

– Check if errors occur (an ORA-00600 or ORA-03113)

– Are there any errors in the alert.log that are written as a result of

executing the UTL_HTTP package?

 

Using the following command, verify the package is valid. Keep in mind, the

dependent objects may differ based on the version of the database.

 

SQL> column object_name format a15

SQL> select object_name, object_type, status from dba_objects

2  where object_name in (select referenced_name from dba_dependencies

3  where name=’UTL_HTTP’) order by object_name, object_type;

 

If using a 9i database, the following should be displayed

 

OBJECT_NAME     OBJECT_TYPE        STATUS

————— —————— ——-

PLITBLM         PACKAGE            VALID

PLITBLM         SYNONYM            VALID

STANDARD        PACKAGE            VALID

STANDARD        PACKAGE BODY       VALID

UTL_HTTP        PACKAGE            VALID

UTL_HTTP        PACKAGE BODY       VALID

UTL_HTTP        SYNONYM            VALID

UTL_HTT_LIB     LIBRARY            VALID

UTL_RAW         PACKAGE            VALID

UTL_RAW         PACKAGE BODY       VALID

UTL_RAW         SYNONYM            VALID

 

If using an 8i database, the following should be displayed

 

OBJECT_NAME     OBJECT_TYPE        STATUS

————— —————— ——-

STANDARD        PACKAGE            VALID

STANDARD        PACKAGE BODY       VALID

UTL_HTTP        PACKAGE            VALID

UTL_HTTP        PACKAGE BODY       VALID

UTL_HTTP        SYNONYM            VALID

 

 

If any are not VALID, you should run utlrp.sql to validate them:

 

cd $ORACLE_HOME/rdbms/admin

sqlplus

SQL> select OBJECT_NAME from DBA_OBJECTS where status = ‘INVALID’;

SQL> connect / as sysdba

SQL> @utlrp.sql

SQL> quit

 

 

If the packages are all being returned with a status of VALID,

then check the privileges:

 

SQL> column grantee format a10

SQL> column owner format a6

SQL> column table_name format a15

SQL> column grantor format a10

SQL> column privilege format a10

SQL> select * from dba_tab_privs where table_name=’UTL_HTTP’;

 

GRANTEE    OWNER  TABLE_NAME      GRANTOR    PRIVILEGE  GRA HIE

———- —— ————— ———- ———- — —

PUBLIC     SYS    UTL_HTTP        SYS        EXECUTE    NO  NO

 

If no errors are returned to the user then check the alert.log for additional

information relevant to the time the UTL_HTTP package was executed.  The

location of the alert.log can be found in the location pointed to by the

background_dump_dest parameter.

 

SQL> column value format a40

SQL> column name format a30

SQL> select name, value from v$parameter where name=’background_dump_dest’;

 

NAME                           VALUE

—————————— —————————————-

background_dump_dest           /database/?/?/?.

 

If any of the package(s) are invalid, privileges not granted, errors in the

alert.log or if an ORA-00600 or ORA-03113 is the result of executing

any UTL_HTTP function/procedure then any further troubleshooting should be

within the database DBA group.

Step 2: Web Site Check (for non-ssl web sites)

=================================================

 

– Using a Browser, verify that any internal site can be accessed

– Using a Browser, verify that any external site can be accessed

– Is access to a secure web page being attempted?

 

When making HTTP call-outs from PL/SQL or SQL, it basically turns the database

into a text-based browser. The standard browsers we know today, Internet Explorer (IE)

and Netscape also use a URL to make HTTP requests. Because of this, we must

treat connection problems with the same logic. The question must be asked, what

does it take to access a particular website?  For instance, if a database is

behind a firewall, we must include a proxy parameter, just like in a browser.

 

If a request can be made in a browser, it can be made with UTL_HTTP. (Within

the scope of the package). Since it is assumed that a desired URL is working

before configuring UTL_HTTP, there should not be an HTTP Server problem, as no

configuration is needed on an HTTP Server for UTL_HTTP to work. If there is an

HTTP Server problem, please resolve that before using UTL_HTTP.

 

If the requested URL is working from one client machine, be sure to test the

URL from a browser installed on the same machine as the database, as it is the

connection from *that* machine to the Internet that is important. Some servers

do not have a browser installed, and therefore using a browser to test may not

be feasible. To test the connection from the database machine to the requested

machine, you can use the following tests:

 

%> ping -a

 

Example:

%> ping -a www.oracle.com

 

If that results in a successful ping, then UTL_HTTP will work the following way:

SQL> SELECT utl_http.request(‘http://www.oracle.com’) FROM dual;

 

If you know a proxy server will be needed, test this connection:

 

%> ping -a

 

Examples:

%> ping -a www-proxy

%> ping -a www-proxy.us.oracle.com

 

If that results in a successful ping, then UTL_HTTP will work the following way:

 

SQL> SELECT

utl_http.request(‘http://www.oracle.com’, ‘www-proxy.us.oracle.com’)

FROM dual;

 

If the pinging is not successful, you will need to diagnose the network connectivity

on that machine, and ensure it is properly configured within the network. This may

require contacting the Network Administrator within your own company.

 

The output of the above examples would be the HTML text from the requested URL.

Testing this functionality is the very first step to diagnosing a problem with

UTL_HTTP. The HTTP Server can be ANY HTTP Server, not just Oracle’s. The setup

of the HTTP Server responding is separate from any of this functionality. This

should always be tested with HTTP requests, before attempting HTTPS (SSL) requests,

which needs further configuration on the Server.

 

If the HTTP Server in use is Oracle HTTP Server, and this cannot be accessed

in a browser, (without UTL_HTTP), the Oracle HTTP Server support team should

be helping to resolve this issue. In theory, it can be any web page that UTL_HTTP

accesses, from any HTTP Server.

 

If the connection from the database to the HTTP Server is not working, a network

administrator, or system administrator should be consulted to properly configure

the network on this machine.

 

There are also cases when the database error is the result of an underlying

SQL*Net issue, where the actual cause can be a variety of things:

 

SELECT utl_http.request(‘http://hostname.domainname’) FROM dual;

ORA-29273: HTTP request failed

ORA-06512: at “SYS.UTL_HTTP”, line 1577

ORA-12570: TNS:packet reader failure

ORA-06512: at line 1

 

For this case, SQL*Net and or Networking troubleshooting should begin.

 

 

Step 3: Secure Web Site Access (HTTPS)check

===========================================

 

– Verify if access to a secure web site is being attempted

– Verify the wallet location

– Verify that Oracle has permission to open the wallet

– Verify that the wallet password is correct

 

If the target URL contains  instead of  then the web

site is a secure web site. NOTE: If a browser is available on the server you

should verify that the secure URL can be accessed. If it cannot be accessed by

the browser utl_http will not be able to access it either. Make sure that a

dialog box does NOT appear requesting client authentication, as this is not yet supported.

 

Just as with the browser, the UTL_HTTP package also supports HTTP over the Secured

Socket Layer protocol (SSL), also known as HTTPS, directly or through an HTTP

proxy. An Oracle wallet is required to make an HTTPS request using the UTL_HTTP

package. Non-HTTPS fetches do not require an Oracle wallet. The Oracle wallet

contains the list of certificate authorities that are trusted by the user of the

UTL_HTTP package. When a wallet is created, it is populated with a set of well-known

certificate authorities as trust points. If the certificate authority that signs

the certificate of the remote HTTPS Web server is not among the trust points then

you should obtain the root certificate of that certificate authority and install

it as a trust point in the wallet using Oracle Wallet Manager. A step by step process

for creating an Oracle wallet using Oracle Wallet Manager can be found in Note 169768.1.

 

When the UTL_HTTP package is executed in the Oracle database server, the wallet

must be accessible. You can confirm the existence of the Oracle wallet and also

the file permissions by navigating to the wallet directory using the system shell.

The directory should have a file named ewallet.p12 and the file permissions should

be set with at least read permissions for the Oracle user. On Unix you should see

something like the following:

 

[sunsys]/etc/ORACLE/WALLETS/oracle> ls -al

 

drwxr-xr-x   2 oracle   dba          512 Jan 28 11:33 ./

drwxr-xr-x  10 oracle   dba          512 Jan 30 08:39 ../

-r——–   1 oracle   dba         8581 Jan 17 11:31 ewallet.p12

 

 

With the wallet configured, access to the secure web site can be tested using

the following SQL.

 

SELECT utl_http.request(”, ”,

‘file:’, ”) FROM DUAL;

 

For example:

SELECT utl_http.request(‘https://www.xyz.com’,’proxy.us.oracle.com:80′,

‘file:/etc/ORACLE/WALLETS/oracle’,’welcome1′) FROM DUAL;

 

IMPORTANT:

 

For simplicity, please test with a simple wallet password (such as welcome1), as

there has been reports that some keyboards are not translating the password

as expected. For example, type a password with numbers in it, it does not

generate the word typed, “6n2ml29y”, on french keyboard appears to be “^n@;l@(y”

(with American keyboard) in OWM java GUI, but there is no way the see this difference

because of the password obfuscation (appears as “********” in the pwd field). If this is

the issue, the following errors are seen:

 

ORA-29273: HTTP request failed

ORA-06512: at “SYS.UTL_HTTP”, line 1568

ORA-29106: PKCS12 wallet import  failed

ORA-06512: at line 1

 

 

If any errors are returned that indicate problems opening the wallet re-check

the syntax of the SQL statement with regard to the file location and wallet

password. A check of the wallet password can be performed by attempting to open

the wallet using Oracle Wallet Manager. Oracle Wallet Manager will not allow you

to open the wallet without the appropriate password. (Instructions on how to

start and use Oracle Wallet Manager can be found in Note 169768.1).

 

UTL_HTTP will not work with HTTPS unless it works appropriately with HTTP. Use

the checks in Step 2: “Web Site Check (for non-secure web sites)” to confirm

that UTL_HTTP is working properly with HTTP before attempting to access web

sites that require HTTPS.

 

If access to the internal and external web site works but only access to the

secure site fails even after using the Note for configuring the wallet and following

the checks above, then the Oracle Security team (previously was handled by the Networking
team) is responsible for the Oracle Wallet Manager.

 

If the HTTP Server in use is Oracle HTTP Server, and this cannot be accessed

in a browser, the Oracle HTTP Server support team should be helping to resolve

this issue. This includes setting up Oracle Wallet Manager for use with the

Oracle HTTP Server. However, the use of UTL_HTTP is usually goingto use an

Oracle Wallet manager installed with the Database software.

 

If the standard HTTP connection from the database to the HTTP Server is not

working, a network administrator, or system admin should be consulted to

properly configure the network on this machine. Once HTTP is working, HTTPS

should work once the wallet is configured with a valid certificate.

 

There are also cases when the database error is the result of an underlying

SQL*Net issue, where the actual cause can be a variety of things:

 

SELECT utl_http.request(‘https://hostname.domainname’,’file:/etc/ORACLE/WALLETS/oracle’,’welcome1′) FROM dual;

ORA-29273: HTTP request failed

ORA-06512: at “SYS.UTL_HTTP”, line 1577

ORA-12570: TNS:packet reader failure

ORA-06512: at line 1

 

For this case, SQL*Net and or Networking troubleshooting should begin.

 

 

Step 4: Configuration Check

===========================

 

– Verify if UTL_HTTP is being used in conjunction with another product?

(Note: using SQL*Plus or PL/SQL does not constitute the use of another product)

 

Verify that UTL_HTTP is being used in conjunction with another Oracle product or

component (i.e. Reports, Portal, Discoverer). If so, then any Support Service

Request should be transferred to the associated competence area. However, it is

recommended to test a plain/simple .html page. If a simple page works with UTL_HTTP,

but another component’s pages does not work, the issue is within the component being

used, perhaps the way the page is rendered back to the UTL_HTTP package.

 

The reason for transferring to the associated group is because at this point,

the UTL_HTTP package has been confirmed to be successfully installed, and

connection to an internal and external site can be made (along with a secure

site if applicable). For example, when used with Reports, the URL for the

Reports server is generally used and since connection to other sites work fine,

the issue may be related to the actual Reports Server and the URL.

 

 

Step 5: Language Check

=======================

 

With the addition of new functions within the UTL_HTTP package, the chance of

integrating within PL/SQL specific functionality has increased, and the Language

group should be involved if the customer has programmatic issues. This falls

under the guidelines of using the results from the UTL_HTTP package within

expressions, within control structures (IF-THEN-ELSE), or Loops (Simple, While, For).

 

The actual UTL_HTTP packages is a PL/SQL package. Its use allows method to

obtain text from a given webserver page, (html text), and use it within your

application,(usually parsing the text for data). Your application would be

a PL/SQL procedure that is executed within the database, and makes a call to

this UTL_HTTP package, passing in desired parameters. Once processing the

results, the application can do a variety tasks. UTL_HTTP is used within the

database, makes an http/https connection on the internet/intranet, and brings

back text for an application to process. No HTTP Server, except that which it

is accessing, is needed. It can be any webserver that serves http/https requests.

 

If you can successfully accomplish all the above tasks, your issue is likely

because of custom application usage, or a bug with the package or interaction

with the database. Be sure to always apply the latest database patchset, as

this package would be updated with it.

 

 

Step 6: Final Step

==================

 

If you navigated to this final step, and still unable to accomplish your goal,

the next step would be to obtain a collaborative effort from Oracle Support to

decide the next steps. Please be prepared with a testcase that can be used to

attempt to reproduce the issue.

 

Any testcases or bugs to be filed would be performed by the Database team responsible

for the UTL_HTTP package, and usually a dual-effort between the Language and Database

Support teams.

 

 

 

 

RELATED DOCUMENTS

—————–

Note 169768.1

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.