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] |
| Modified:07-Mar-2012Type:TROUBLESHOOTINGStatus:PUBLISHEDPriority:3 |
|
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
