Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle UTL_SPADV Package

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

This package first appeared in 11g and is used to gather and show statistics related to Oracle Streams. There are two subprograms, COLLECT_STATS and SHOW_STATS. Every parameter in COLLECT_STATS has a default value, as does every parameter in SHOW_STATS. With that in mind, there is no need to list every parameter if one wants to use something other than the default value. The way to accomplish this is to use named notation. List the parameter name followed by "=>" followed by the new value.


The SPADV part of the package takes its name from the Streams Performance Advisor. The advisor is a new feature and the best source of documentation for it (as of this writing) is in OracleŽ Streams Concepts and Administration 11g Release 1 (11.1). The output from SHOW_STATS is spreadsheet ready, and one definitely needs the decoder table to interpret all of the abbreviations used in the output.


The UTL_TCP package enables PL/SQL to communicate with external servers using TCP/IP. The servers must also be using TCP/IP. The package is useful for accessing Internet protocols and email. The newer UTL_MAIL is a wrapper around this package. Examine the sample code for UTL_SMTP and one can see traces of UTL_TCP in place there as well.


The subprograms are based on get, read, write, open and close. The read and write options can handle text lines, binary data, and text data. The output from UTL_TCP requests can be captured and spooled into a file. The contents of the file will be fairly similar to what was obtained using URL_HTTP.


Here is an example set of code to GET lines from a Web site.


set serveroutput on

spool C:\Temp\UTL_TCP_DEMO.html


 conn   utl_tcp.connection;

 retval PLS_INTEGER;


  conn := utl_tcp.open_connection

  (remote_host => '',

   remote_port => 80,

   charset => 'US7ASCII');

  retval := utl_tcp.write_line(conn, 'GET / HTTP/1.0');

  retval := utl_tcp.write_line(conn);



      dbms_output.put_line(utl_tcp.get_line(conn, TRUE));



    WHEN utl_tcp.end_of_input THEN














spool off


In essence, this replicates the example shown in UTL_HTTP. The output file will have a few more artifacts from the package, but once cleaned up or commented out, the resulting HTML file will render normally in a browser. Paths to images may not resolve, so the image box may be seen on the page, but all other text and formatting will appear as seen on the actual page itself.


In comparison, using the UTL_HTTP package to scrape a Web site is easier as it requires fewer lines of code. Most of the default parameters are adequate. One that may need to be varied is the timeout parameter. The default of 0 means not to wait, and a null value means to wait forever. Those conditions may be extreme, so trying a small non-zero value for timeout may help connection attempts succeed more often than when set to 0.  To find out more about this utility for the DBA, please refer to Chapter 6.


As the last of the UTL packages, UTL_URL is useful for escaping and unescaping URLs with spaces in them. There have been URLs with "%20" in between words. That escape sequence represents a space, and a space is an illegal character, so it has to be escaped. The notation is based on %hex-code format.


The following is an example of escaping spaces within a URL.



  utl_url.escape(' with space.html')







Likewise, the unescape function returns the URL back to its original form.








-------------------------------------------------------------------- with space.html



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.