Oracle Magazine July August 2011

JULY/AUGUST 2011

First Line of Defense Oracle Database Firewall monitors database conversations /19 Upgrade to Oracle Business Intelligence Enterprise Edition 11g Take advantage of the latest OLAP analysis, mapping, KPI, and scorecard features /43 Add Advanced List Interaction Use model-driven LOVs in Oracle ADF to implement smart lists /47 Control the Flow of Execution Part 2 in a series of articles on understanding and using PL/SQL /50 On Better Loading and Fact-Checking Our technologist enjoys the rule of external tables and debunks index scan myths /57

STORAGE FROM A TO ZFS

How organizations optimize storage with tiered Sun flash, disk, and tape solutions from Oracle and enable unified storage with the Sun ZFS Storage Appliance

Rise of the Machines

One organization lays a foundation for shared services with Oracle Exalogic and Oracle Exadata

First Line of Defense

Oracle Database Firewall monitors database traffic and blocks unauthorized activity

SUN ZFS STORAGE 7420 APPLIANCE

Trusted Trusted Future Future Protection Protection

Fujitsu's Fujitsu's SPARC SPARC ENTERPRISE ENTERPRISE delivers delivers Trust Trust andand Business Business Continuity Continuity Fujitsu Fujitsu and Oracle and Oracle together together are creating are creating UNIXUNIX history history through through progressive progressive delivery delivery of of server server solutions solutions with with SPARC SPARC ENTERPRISE. ENTERPRISE. Our customer Our customer commitment commitment is reflected is reflected in in overwhelming overwhelming performance performance and excellent and excellent reliability reliability that that will protect, will protect, grow,grow, and expand, and expand, the unique the unique valuevalue of your of your business business assets. assets. Please Please visit visit : www.fujitsu.com/oracle/ : www.fujitsu.com/oracle/ 2011 Fujitsu 2011 Fujitsu Limited. Limited. All rights All reserved. rights reserved. Fujitsu,Fujitsu, the Fujitsu the Fujitsu logo and logo SPARC and SPARC Enterprise Enterprise are registered are registered trademarks trademarks or trademarks or trademarks of of FujitsuFujitsu LimitedLimited in the United in the United States and States other andcountries. other countries. UNIX isUNIX a registered is a registered trademark trademark of openofgroup open in group the United in the United States and States and other countries. other countries. Oracle Oracle and Java and are Java registered are registered trademarks trademarks of Oracle of Oracle and/or and/or its affiliates. its affiliates. All SPARC All SPARC trademarks trademarks are used areunder used under licenselicense and areand registered are registered trademarks trademarks of SPARC of SPARC International. International. Inc. in the Inc. United in the United States and States other andcountries. other countries.

flash

FeatureD Content Volume XXV - Issue 4

Contents

Storage from A to ZFS /30

tape

disc

2

Most business users don’t spend much time thinking about storage. They follow a few simple guidelines and expect their critical information to be saved, backed up, and duplicated. The vast storage infrastructure that automates these activities is largely behind the scenes. Yet as the demand for information grows, so do storage infrastructures. Strategic storage solutions—from tape libraries to flash and disk arrays to unified storage—can simplify the essential tasks of storing and managing enterprise data management. Find out how Novant Health and the Australian Bureau of Meteorology manage terabytes of new data every day with business-ready storage systems from Oracle. —By David Baum

Rise of the Machines / 36 Oracle Exalogic Elastic Cloud is the world’s first engineered system specifically designed for implementing a private cloud computing environment that can host everything from small-scale departmental applications to large-scale enterprise resource planning systems. Since its debut at Oracle OpenWorld in September 2010, Oracle Exalogic has changed the cloud conversation as organizations explore how they can take advantage of this unique middleware machine. And the perfect datacenter partner for Oracle Exalogic? Oracle Exadata Database Machine. Together, these standards-based, engineered systems deliver extreme performance for data warehousing and online transaction processing applications. Read why PHH Corporation is consolidating disparate technology stacks into a shared services environment anchored by Oracle Exalogic and Oracle Exadata. —By David Baum

July/August 2011 Oracle.com/oraclemagazine

SEARCH LESS. DEVELOP MORE.

WITH THE INTELLIGENT LIBRARY IN THE CLOUD.

REGISTER YOUR TEAM FOR A FREE TRIAL NOW Find all the latest and most relevant resources for Oracle developers and IT professionals at Safari Books Online.

LEARN MORE AT:

safaribooksonline.com/oracle

…and get access to the world’s most popular, fully searchable digital library.

Move your library to the cloud and get instant, unlimited, fully searchable access to the tech books you need – including exclusive online access to books from O’Reilly Media, Addison-Wesley, Prentice Hall and more! See for yourself why more than 15 million IT and business professionals, developers and web designers from corporations, government agencies and academic institutions use Safari Books Online.

4

Departments Volume XXV - Issue 4

Up Front / 6 From Our Readers / 6 Readers tell us what they think.

From the Editor / 7 Portable Development for the Mobile Business

—Tom Haunert

MashUp / 8 News, views, trends, and tools.

At Oracle / 11 Events / 11

Resources / 13

Find out about upcoming technology and industry events.

Your guide to Oracle Webcasts, podcasts, blogs, and more

Briefs / 16 The latest product news

Interview / 19

First Line of Defense

Steve Moyle, chief technical officer of Oracle Database Firewall, talks about the product’s cutting-edge security features. —Tom Haunert

Community / 22 Partner News / 22 Book Beat / 22 Community Bulletin / 25

Survey Says

Oracle Technology Network members weigh in on what they want more of. —Justin Kestelyn

Architect / 26

Up Close / 27 The Translator

Peer-to-Peer / 28

Your success as a software architect depends on your relationships with stakeholders. —Bob Rhubart

Hans Forbrich helps DBAs understand the broader technology stack. —Jeff Erickson

Bob Mycroft, Julian Dontcheff, Sheeri K. Cabral —Blair Campbell

Oracle ADF / 47 Add Advanced List Interaction

PL/SQL 101 / 50

ASK TOM / 57

Use model-driven lists of values in Oracle Application Development Framework to implement smart lists with autosuggest behavior. —Frank Nimphius

Part 2 in a series of articles on understanding and using PL/SQL. —Steven Feuerstein

Our technologist enjoys the rule of external tables and debunks index scan myths. —Tom Kyte

Can You Relate?

On Grids and Groups

Technology / 43 business Intelligence / 43 Upgrade to Oracle Business Intelligence Enterprise Edition 11g

Take advantage of the latest OLAP analysis, mapping, KPI, and scorecard features. —Mark Rittman

Comment / 63 In the Field / 63 Build and Buy

An Oracle technologist looks at the principles for integrating built and bought solutions. —Andy Flower

Analyst’s Corner / 64

Make the Next Storage Move

Organizations reduce costs by matching the right type of storage to their needs. —David Baum

July/August 2011 Oracle.com/oraclemagazine

Controlling the Flow of Execution

On Better Loading and Fact-Checking

5

Publishing Publisher Jeff Spicer  [email protected] Production Director and Associate Publisher Jennifer Hamilton  [email protected]  +1.650.506.3794 Senior Manager, Audience Development and Operations Karin Kinnear  [email protected]  +1.650.506.1985

Editorial Editor in Chief Tom Haunert  [email protected] Senior Managing Editor Caroline Kvitka  [email protected] Contributing Editor and Writer Blair Campbell Editor in Chief, Oracle Technology Network Justin Kestelyn  [email protected] Technology Advisor Tom Kyte Contributors Marta Bright, Jeff Erickson, Fred Sandsmark, Rich Schwerin, Leslie Steere

Advertising sales Associate Publisher Kyle Walkenhorst  [email protected]  +1.323.340.8585 Northwest & Central U.S. Tom Cometa  [email protected]  +1.510.339.2403 Southwest U.S. and LAD Shaun Mehr  [email protected]  +1.949.923.1660 Northeast U.S. and EMEA/APAC Mark Makinney  [email protected]  +1.805.709.4745 Mailing-List Rentals Contact your sales representative.

DESIGN Senior Creative Director Francisco G Delgadillo Design Director Richard Merchán Contributing Designer Jaime Ferrand Production Designer Sheila Brennan Editorial Board Ian Abramson, Karen Cannell, Andrew Clarke, Chris Claterbos, Karthika Devi, Kimberly Floss, Kent Graziano, Taqi Hasan, Tony Jambu, Tony Jedlinski, Ari Kaplan, Val Kavi, John King, Steve Lemme, Carol McGury, Sumit Sengupta, Jonathan Vincenzo, Dan Vlamis Subscription Information Subscriptions are complimentary for qualified individuals who complete the subscription card found in each issue or online at oracle.com/oraclemagazine. For change of address, mail in label with the new address to: Oracle Magazine, P.O. Box 1263, Skokie, IL 60076-8263. Magazine Customer Service [email protected]  Fax +1.847.763.9638  Phone +1.847.763.9635 Privacy Oracle Publishing allows sharing of its mailing list with selected third parties. If you prefer that your mailing address or e-mail address not be included in this program, contact Customer Service at [email protected].

Resources Oracle Products +1.800.367.8674 (U.S./Canada) Oracle Services +1.888.283.0591 Oracle Press Books oraclepressbooks.com Copyright © 2011, Oracle and/or its affiliates. All Rights Reserved. No part of this publication may be reprinted or other­wise reproduced without permission from the editors. ORACLE MAGAZINE IS PROVIDED ON AN “AS IS” BASIS. ORACLE EXPRESSLY DISCLAIMS ALL WARRANTIES, WHETHER EXPRESS OR IMPLIED. IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING FROM YOUR USE OF OR RELIANCE ON ANY INFORMATION PROVIDED HEREIN. The information is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Oracle and Java are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Oracle Magazine (ISSN 1065-3171) is published bimonthly with a free subscription price by: Oracle, 500 Oracle Parkway, MS OPL-3C, Redwood City, CA 94065-1600. Periodicals Postage Paid at Redwood City, CA, and additional mailing offices. • POSTMASTER: Send address changes to: Oracle Magazine, P.O. Box 1263, Skokie, IL 60076-8263.

Printed by Brown Printing Oracle PRODUCT INDEX Product Name

Page Product Name

AutoVue

18 Oracle Database

Hyperion profitability and cost management solutions

13 Oracle Database Firewall

Java

14 Oracle Database Migration Assistant for Unicode

Java Platform, Enterprise Edition

16 Oracle Database, Express Edition

MySQL MySQL Enterprise Edition

13, 14, 16, 24 Oracle E-Business Suite 17, 23 Oracle Enterprise Gateway

NetBeans

17 Oracle Enterprise Manager Grid Control

Oracle Application Development Framework

47 Oracle enterprise performance management solutions

Oracle Application Development Framework Mobile Client

16 Oracle Exadata Database Machine

Oracle Application Express

19 Oracle JDeveloper 18 Oracle Linux 13, 16, 25 Oracle On Demand 23, 24 Oracle On Demand Strong Authentication Service

Page Product Name 22, 23 PeopleSoft Enterprise 47 PeopleSoft Enterprise human capital management solutions 13, 23 PeopleSoft PeopleTools 13 Siebel Customer Relationship Management

Page 24 23 23 23, 24

18 SPARC servers

13

17 Oracle Public Sector

23 StorageTek Automated Cartridge System Library Software

30

22 Oracle Real Application Clusters

23 StorageTek SL8500 modular library system

30

14, 17 Oracle Retail 13, 14, 16, Oracle SOA Suite 36, 64

24 StorageTek T10000

30

63 Sun Blade server

30

14, 36 Oracle Solaris

14 Sun Storage 6000

30

Oracle business intelligence solutions

23 Oracle Fusion Middleware

22, 63 Oracle Solaris Cluster

30 Sun Storage 6780

30

Oracle Business Intelligence Enterprise Edition

43 Oracle GlassFish Server

13, 16 Oracle Tuxedo

14 Sun Storage Archive Manager

30

Oracle Business Intelligence foundation

22, 63 Oracle Exalogic Elastic Cloud

Page Product Name 14, 23, 24, 30, Oracle Hyperion Planning 50, 57, 63

23 Oracle GoldenGate

14 Oracle Validated Configurations

18 Sun x86 Rackmount servers

Oracle Business Process Management

16, 63 Oracle governance, risk, and compliance solutions

14 Oracle VM

13, 22 Sun ZFS Storage Appliance

Oracle CRM On Demand

23, 24 Oracle Health Sciences Clinical Development Center

18 Oracle WebCenter Suite

63

Oracle Data Integrator

13, 63 Oracle Hyperion Data Relationship Management

16 Oracle WebLogic Server

13, 14, 16

30 14, 17, 30

oracle magazine July/August 2011

6

FROM OUR READERS

Your corrections, your opinions, and your requests: Here’s your forum for telling us what’s right and wrong in each issue of Oracle Magazine, and for letting us know what you want to read.

iPAD POTENTIAL AND PROGRESS I downloaded the iPad version of Oracle Magazine with great enthusiasm. I was a bit disappointed to see it is just the magazine content moved onto the iPad with the same format. I’m not comfortable reading with the default size. Anil Shafeeque The editors reply: We have been working to improve the user experience on our iPad application. In the current version of the Oracle Magazine iPad app, you will find a much more readable and interactive experience in portrait view. Not only is the default size larger, but we’ve broken the page elements apart for better readability. You can now zoom in on figures and code listings, too. We’ve also added more interactive elements, including animated technical illustrations. We hope this will improve your experience with Oracle Magazine on the iPad. Let us know what you think. MORE JAVA! I’m an enterprise architect and am well versed with the technology landscape in many industries. Given the depth of Java’s penetration in the industry and its wide adoption by customers, it seems like a good opportunity to have one to two pages in Oracle Magazine dedicated to Java. Ramkumar (Ram) Jeyaraman The editors reply: We agree that Java needs more magazine coverage, but we also believe that one or two more pages per issue in this magazine is not enough. That’s why Oracle is

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

launching Java Magazine in late July 2011. This essential source of knowledge about Java technology, the Java programming language, and Java-based applications is for people who rely on Java in their careers (or who aspire to). The editors of Java Magazine invite you to subscribe to this free digital publication at bit.ly/javamagsub. ARE YOU EXCELLENT? Can a company submit a nomination for the Oracle Magazine Editors Choice Awards? Amanda Stewart

BEGIN INSERT INTO message_table (message_ date, MESSAGE_TEXT) VALUES (SYSDATE, hello_place ('Chicago')); END;

I think that the hello_place procedure should instead be the hello_message function, because I don’t think a procedure will work here. (I get ORA-06550|00904.) So: BEGIN INSERT INTO message_table (message_

The editors reply: The Oracle Magazine Editors Choice Awards have been renamed the Technologist of the Year awards, and they are now part of the Oracle Excellence Awards. You may nominate individuals at your company for the Technologist of the Year awards; nominations are due by June 30, 2011. Please refer to the Oracle Excellence Awards page at bit.ly/kVhJ3Z for more information. BROKEN BLOCKS I think the last block to run in the “Name Those Blocks!” section of Steven Feuerstein’s “Building with Blocks” article (March/April 2011, bit.ly/i9rSxd) has a typo. Steven asks us to create the function hello_message, creates a procedure (hello_place) that uses this function, and then says: I can also call the function from within a SQL statement. In the following block, I insert the message into a database table:

date, MESSAGE_TEXT) VALUES (SYSDATE, hello_message ('Chicago')); END;

Either way, keep up the great articles! Alan Turnbull Steven Feuerstein replies: Alan, many thanks for pointing this out. Yes, you are right; the function must be called, not the procedure.

SEND MAIL TO THE EDITORS Send your opinions about what you read in Oracle Magazine, and suggestions for possible technical articles, to [email protected]. Or click the Write the Editors link on our Website, oracle .com/oraclemagazine. You can also follow our @magazineoracle Twitter feed or join us on Facebook at bit.ly/cpY2Z9. Letters may be edited for length and clarity and may be published in any medium. We consider any communications we receive publishable.

From The Editor

Portable Development for the Mobile Business Oracle software continues to support the latest cloud and mobile hardware.

I

t’s funny to look back at some of the very large PCs of the 1980s that were called portable—and at early mobile phones that were the size of bricks. Both portable computing and mobile communications devices have certainly evolved. Portable business software has also evolved, and it continues to support a variety of platforms—for use on a variety of datacenter, desktop, and mobile devices. And the demand continues for portable business software that can run on each new mobile device. Oracle has been developing portable software for more than 30 years, for a variety of hardware and software platforms that now live in the cloud and the datacenter and do business on the desktop, the laptop, and the newest mobile devices. And today’s mobile devices are just the latest stop for portable Oracle enterprise technology and solutions.

Next Steps LEARN more about Oracle Business Intelligence Enterprise Edition Release 11.1.1.5 oracle.com/us/corporate/press/392802 EXPLORE Oracle and the mobile enterprise oracle.com/us/technologies/mobile-enterprise Read Oracle Magazine content online oracle.com/oraclemagazine Subscribe to Oracle Magazine oracle.com/us/corporate/publishing/subscribe Java Magazine bit.ly/javamagsub Nominate candidates for Oracle Excellence Awards oracle.com/us/corporate/awards

CONNECT:

bit.ly/aVgo69

bit.ly/orclmagfb

Portable Oracle Technology Oracle recently released Oracle Application Development Framework Mobile Client (Oracle ADF Mobile Client). With it, developers can define a device-independent representation of an application. They can build an application with Oracle ADF Mobile Client, deploy it to multiple devices, and extend it to new platforms without redevelopment. (For more information, see “Oracle Releases Oracle ADF Mobile Client,” page 16.) Oracle also recently announced Oracle Business Intelligence Enterprise Edition Release 11.1.1.5, which introduces support for Apple’s iPad and iPhone through the new Oracle Business Intelligence Mobile feature. Oracle Business Intelligence Enterprise Edition 11.1.1.5 content is optimized for use with the iPad and iPhone—no twitter.com/magazineoracle

design changes to existing reports and dashboards are required. Portable Oracle Content The Oracle mobile application for iPhone and iPad collects and delivers the latest information about all things Oracle, including the latest news, Oracle Technology Network content, videos, events, blogs, and investor and press information. It also collects and links to the latest Oracle mobile applications available for that platform, including Oracle Business Intelligence Mobile and Oracle WebCenter Spaces 11g Release 1, as well as Oracle Magazine and Profit: The Executive’s Guide to Oracle Applications. For the record, the printed Oracle Magazine has been portable and mobile since its beginnings in 1986, and it has been available in HTML format since the 1990s. A browser-based digital subscription has been available since 2006, and Oracle Magazine has been packaged as an application for mobile devices since 2010. Portable Notes Registration for Oracle OpenWorld 2011, October 2–6 in San Francisco, California, is now open at oracle.com/openworld. . . . Java Magazine is coming. Subscribe now at bit.ly/ javamagsub. . . . The Oracle Magazine Editors’ Choice Awards program has been renamed the Technologist of the Year awards and is now part of the Oracle Excellence Awards program. Nominations for Technologist of the Year and all other Oracle Excellence Awards are now open at oracle.com/us/corporate/awards. Tom Haunert, Editor in Chief [email protected]

linkd.in/orclmag oracle magazine July/August 2011

7

MashUp

News. Views. Trends. Tools.

It’s tough to predict the weather, but these apps try.

W E AT H E R A P P S

Weather Alert USA

This app gives you weather reports and forecasts by location, but its best feature is push notification of National Oceanographic and Atmospheric Administration watches, warnings, and advisories for the U.S. US$3.99 (iPhone, iPad, iTouch). www.softpeas.com

Weather Channel

Bookmark your favorite locations or use the GPS feature to get a local forecast (hourly, 36-hour, or 10-day). Free (iPhone, Android); Ad-free “Max” version US$3.99 (iPhone); or by subscription (BlackBerry, Treo, Windows Mobile, Motorola Q). weather.com

Fahrenheit

You’ll always know the temperature, because this app puts it on your home screen in real time. Also provides forecasts and radar maps for cities worldwide. Despite its name, it also speaks Celsius. US$0.99 (iPhone, iPad). itwcalculator.com

WeatherPro

Great worldwide coverage with global satellite images and animated radar maps. Available in 12 languages. Cost varies by platform and subscription (iPhone, iPad, Android, BlackBerry, Windows Phone 7). weatherpro.eu

SEEING DOUBLE

“Enchantment causes a voluntary change of hearts, minds, and actions. It transforms situations and relationships, converts hostility into civility, and changes skeptics and cynics into believers.” —Guy Kawasaki, author of Enchantment: The Art of Changing Hearts, Minds, and Actions (Penguin Group, 2011)

Want the interactivity of a tablet with the functionality of a notebook? Check out Acer’s Iconia-6120 Dual-Screen Touchbook, a dual 14-inch LCD touchscreen notebook. At 6.18 pounds, the Windows 7–based twin tablet is loaded with an Intel Core i5 processor, 4 GB of RAM, a 640 GB hard drive, a 1.3-megapixel HD webcam, Wi-Fi, and USB 3.0, HDMI, VGA, and USB ports. It sports AppCards, a set of Iconia touch-based applications including a touch-based browser, a clipping tool, a social network aggregator, and tools for quick access to multimedia files. With the Touchbook you can use your finger for control, type on a virtual keyboard, or use a combination of both. You can also customize what is shown on each screen—watch a video on the top screen, browse your multimedia library on the bottom one, or view a single Web page across the two displays. US$1,199.99. us.acer.com.

HUMILITY IS IN

Employees who perceive themselves as honest and humble receive significantly higher job performance ratings from their supervisors. Source: Baylor University, baylor.edu

MINI MIC

If you record podcasts, video blogs, conference sessions, or business meetings—or talk via Skype—enhance your sound quality with the i-Microphone from GoBiz. This compact microphone is perfect for travel and works with the iPad, MacBook, iPhone, and other smartphones. The i-Microphone features an omnidirectional condenser microphone that can record up to 12 dB louder than the microphones built into most mobile devices. Weighing less than 0.25 ounces, it offers RF shielding that reduces interference and feedback, a windscreen, and a weatherproof brass body. US$25.99. gobizcorp.com.

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

SEEK AND YE SHALL (EVENTUALLY) FIND In a survey of 450 IT professionals and business managers, participants were asked how much time they spend each day filing and maintaining information and searching internal documents and systems for information they need. 5 minutes

7%

10 minutes 13% 20 minutes 21% 30 minutes 19% 45 minutes 11% 1 hour

14%

1.5 hours

6%

2 hours

4%

3 hours

2%

3+ hours

3%

Source: OpenText and AIIM, Capitalizing on Content: A Compelling ROI for Change, aiim.org, opentext.com.

CHRIS STRACH

8

Events

Technology Events

Conferences and sessions to help you stay on the cutting edge

Oracle User Groups Sacramento Java Users Group Meetings July 12 and August 9, Sacramento, California

sacjug.org

Java Metroplex Users Group Meetings July 13 and August 10, Addison, Texas

InSync11

August 16–17, Sydney, Australia This conference program, developed by Oracle customers for their peers, covers Oracle applications and technologies and features the largest number of Oracle users gathered in one place outside of Oracle OpenWorld. More than 140 speakers from Australia, New Zealand, and elsewhere provide updates, presentations, and reports. Get all the details and sign up at bit.ly/hw8QgJ.

Latin American Oracle Technology Network Tour 2011

OAUG Connection Point— Release 12.1

July 8, Quito, Ecuador fecuoug.org With information for DBAs, Oracle applications administrators, architects, developers, and other technology leaders, this daylong conference features a presentation by Oracle’s Tom Kyte as well as information from Oracle representatives and partners.

July 12–13, Chicago, Illinois fbit.ly/fgfXuz Presented by the Oracle Applications Users Group (OAUG), the North Central Oracle Apps User Group (NCOAUG), and the OAUG Upgrade SIG, this conference helps attendees implement, upgrade to, or reimplement Oracle E-Business Suite 12.1. Case studies and panels cover application and database tuning, security, and optimizing the upgrade process.

HCI International 2011

Getty Images

July 9–14, Orlando, Florida fhcii2011.org The 14th International Conference on HumanComputer Interaction is a forum for the dissemination and exchange of scientific information on theoretical, generic, and applied areas of HCI. It opens with three days of tutorials and then shifts to three days of parallel sessions, poster sessions, and an exhibition. Presenters include Michele Snyder and Jeff Sauro, both usability engineers at Oracle.

Über Conf July 12–15, Denver, Colorado fuberconf.com Dubbed “The Ü” by its organizers, this conference offers 150 technically focused Java-related sessions, including 25 hands-on workshops on topics including architecture, cloud, security, enterprise Java, languages on the Java virtual machine, build/test, mobility, and agility.

AGA Professional Development Conference and Exposition

8th International Joint Conference on E-Business and Telecommunications

July 10–13, Atlanta, Georgia fagacgfm.org/pdc2011 This education and networking event for government financial managers and accountability professionals, sponsored by the Association of Government Accountants (AGA), includes technical sessions, keynote presentations, an exhibit hall, and networking opportunities.

July 18–21, Seville, Spain ficete.org This event brings together six different conferences—on topics ranging from e-business to security to multimedia—and includes a worldwide roster of academic keynote speakers, specialized workshops, and dozens of topicspecific sessions.

javamug.org

UKOUG DBMS SIG Meeting July 14, London, England

ukoug.org

Oklahoma City Oracle User Group Meetings

July 19 and August 16, Oklahoma City, Oklahoma

okcity.oracle.ioug.org

Houston Oracle Users Group Meetings July 20 and August 10, Houston, Texas

houg.org

LOSUG-UKOUG Solaris and Open Solaris SIG Meetings

July 20 and August 17, London, England

ukoug.org

Ohio Oracle Users Group Meeting July 21, Dublin, Ohio

ooug.org

Dallas Oracle Users Group Database Forums

July 21 and August 18, Richardson, Texas

doug.org

Southwest Regional Oracle Applications User Group Meeting July 22, Irvine, California

sroaug.org

Central Florida Oracle Users Group Meetings

July 25 and August 22, Orlando, Florida

cfoug.org

Austin Java Users Group Meetings July 26 and August 30, Austin, Texas

austinjug.org

Indiana Oracle Users Group Training Day July 28, Indianapolis area, Indiana

inoug.org

New Zealand Oracle Users Group Committee Meeting August 5, online

nzoug.org

St. Louis Oracle Users Group Meeting August 11, St. Louis, Missouri

sloug.org

Pittsburgh OAUG Golf Outing

August 19, Pittsburgh, Pennsylvania

pittoaug.oaug.org

oracle magazine July/August 2011

11

Events

cryptography, malware analysis, virtualization, vulnerability and exploit deployment, and more. Among the sessions is a two-day hands-on course that covers security problems related to Oracle Database, including unsecure PL/SQL code, patch management, and weak passwords, and how to secure and harden databases using built-in Oracle Database features.

National Forum on Criminal Justice and Public Safety Head to Portland, Oregon, for OSCON, July 25–29.

2011 World Congress in Computer Science, Computer Engineering, and Applied Computing (WORLDCOMP’11) July 18–21, Las Vegas, Nevada fbit.ly/hneBbl This global congress includes 22 different academic conferences, all held at the same time and in the same location. Specific topics include grid computing and applications, internet computing, and software engineering research and practice.

OAUG Connection Point—Enterprise Performance Management July 20–21, Seattle, Washington fbit.ly/hse5Bq This in-depth regional forum covers Oracle’s enterprise performance management (EPM), business intelligence, and Hyperion solutions. Educational content includes customer success stories, partner tips and tricks, and information from Oracle executives across all Oracle EPM applications and tools.

Quest Northeast July 20–21, Uncasville, Connecticut fbit.ly/h9cH9n Quest joins with five Northeast affiliate user groups and the Financial Services Industry User Group for two days of education geared toward Oracle’s PeopleSoft and JD Edwards customers.

REGister for Oracle OpenWorld 2011

and JavaOne 2011 October 2–6, 2011 San Francisco, California oracle.com/openworld oracle.com/javaone

July/August 2011 Oracle.com/oraclemagazine

Campus Technology 2011 July 25–28, Boston, Massachusetts fbit.ly/gtowTF More than 40 concurrent sessions in six tracks and 10 half-day workshops provide information for faculty, administrators, and IT professionals in higher education. Topics include education applications, social software, immersive platforms, the digital campus, instructional design, and leadership.

Open Source Convention (OSCON) July 25–29, Portland, Oregon foscon.com More than a dozen tracks—plus colocated conferences for Java and data management—make O’Reilly’s annual OSCON the ideal place to see and evaluate new open source projects, tools, services, platforms, languages, and more.

Gartner Catalyst Conference July 26–29, San Diego, California fbit.ly/evR2qh This gathering focuses on the technologist’s role in identity and privacy, cloud computing, data management, security and risk management, network and telecommunications, and content and collaboration. The conference includes opportunities to network with analysts, vendors, and peers.

Black Hat USA 2011 July 30–August 2, Las Vegas, Nevada fbit.ly/jfKGAQ This conference for the information security community delivers timely, actionable security information in a vendor-neutral environment. Training topics include auditing and assessment,

July 31–August 2, Jersey City, New Jersey fnational-forum.net This gathering showcases programs and technologies that help criminal justice practitioners and decision-makers in states, local communities, and tribal nations address important public safety issues in an era of tight budgets.

NRFtech: IT Leadership Summit 2011 August 14–16, Lake Tahoe, Nevada fbit.ly/efPtsR The National Retail Federation (NRF) CIO Council sponsors this educational program that provides actionable information on the latest trends in retail technology, business-IT alignment, and IT governance.

Quest West August 16–17, San Diego, California fbit.ly/dLoDib Quest partners with six regional user groups for an educational event focusing on networking and idea-sharing between customers, third-party vendors, and Oracle employees.

Innovation and Technology Management Africa August 24–26, Pretoria, South Africa fciipm.co.za/itm Participants in this event will examine the African experience and best practices in enterprise technology management. Topics include government support and leadership and organizational culture.

Events locator Oracle Events oracle.com/events Locate User Groups oracle.com/technology/community/user_groups

Getty Images

12

Resources

What’s New at Oracle

The latest videos, podcasts, blogs, and more

VIDEOS TechCast Live: Oracle Database, Express Edition Goes to 11! fbit.ly/lpeQdA Chris Rice, senior director for database tools at Oracle, tells you what you can expect from Oracle Database, Express Edition 11g, a free and redistributable version of Oracle Database (now in beta). TechCast: Oracle GlassFish Server and Oracle WebLogic Server Interoperability and Integration fbit.ly/lkYZQW Learn about Oracle’s strategy for creating increasing integration between Oracle GlassFish Server and Oracle WebLogic Server. Avea Improves Performance with Oracle Data Integrator and Oracle Exadata fbit.ly/gpHaag Hear why Avea selected Oracle Data Integrator and Oracle Exadata to implement a real-time data warehousing solution. Oracle’s Software as a Service fbit.ly/iAYRw7 Learn about the software-as-a-service offerings available through Oracle On Demand.

PODCASTS Edward Whalen on Oracle VM fbit.ly/k5xKYr Edward Whalen discusses his new book, Oracle VM Implementation and Administration Guide (Oracle Press, 2011). Rick Greenwald on Oracle Exadata fbit.ly/jt7e2d Rick Greenwald, lead author of Achieving Extreme Performance with Oracle Exadata (Oracle Press, 2011), discusses this new book. Oracle BPM Suite Overview: Social BPM fbit.ly/jP0yX4 Hear how social business process management (BPM) provides process lifecycle participants in

business and IT with expanded tools for simplifying communication and collaboration.

WEBCASTS Cloud Computing Webcast Series fbit.ly/khCExa This four-part series will help you develop a strategy and roadmap for cloud computing. The Value of Linux in Today’s Fast-Changing Computing Environments fbit.ly/mI3Me1 IDC analysts discuss the evolution of Linux as a solution for business-critical deployments and the new ways in which Linux is being delivered.  Securing High-Performance Web Applications fbit.ly/efEZn2 Learn how to optimize your mission-critical Web applications by balancing security and operational performance using Oracle technologies. Why Pella Corporation Deployed Oracle VM and Oracle Linux fbit.ly/kBTEYM Pella’s datacenter managers and IT administrators discuss why they virtualized their Oracle applications with Oracle VM and moved to Oracle Linux. Oracle VM and Cloud Computing: A Reality Show fbit.ly/m9Dzux Learn about the role of Oracle VM server virtualization in the cloud and what you need to know to run an efficient datacenter. Enterprise Storage Innovations fbit.ly/l8l28A Hear how Oracle, Intel, and other leading companies are dealing with skyrocketing amounts of data and other storage challenges. MySQL on Windows Online Forum fbit.ly/l8yZqm Learn how to reduce database TCO by as much

as 90 percent while increasing manageability and flexibility with MySQL on Windows. Boost Utilization, Cut Costs with SPARC Consolidation and Virtualization fbit.ly/gsQ3nB Find out how the built-in virtualization capabilities in Oracle’s SPARC servers can increase system utilization levels to as much as 90 percent. Improve Performance with Enhanced Insight into Profitability and Costs fbit.ly/jgsCMZ Learn how Oracle’s Hyperion profitability and cost management solutions allow you to rapidly identify sources of profitability and increase transparency.

Oracle Direct

For Oracle product information, call the number for your region. Country U.S. and Canada Australia Austria Belgium Brazil China Denmark Finland France Germany Greece Hong Kong India Indonesia Ireland Italy Japan Malaysia Mexico The Netherlands New Zealand Norway Philippines Portugal Singapore South Africa South Korea Spain Sweden Switzerland Taiwan Thailand Turkey U.K.

Phone Number 1.800.367.8674 1800.735.467 0800.29.7626 0800.73280 0800.901.985 800.810.0161 8088.1068 0800.113.573 0800.905.805 0800.1.810.111 00800.353.12020 3002.1246 1600.44.6725 001.800.1.672.253 1850.672253 8008.74720 0120.155.096 1800.80.1837 01800.221.7321 0800.0827 0508.555.215 800.14411 811.5831 800.853.021 1800.6722.531 0800.994.225 080.2194.114 900.952900 020.798798 0800.55.2574 0800.672.253 001800.441.0545 00800.353.90014 0870.5.332200

oracle magazine July/August 2011

13

14

Resources

TRAINING

overHEARD

“Today we have an Oracle Exadata database and we have almost 25 terabytes. Our ETL [extract, transform, and load] batch window decreased by one-third. Our query performance is 10 times better than it was with the old system.” —Hanife Keskin, ETL Supervisor, BI Solutions at Avea, in the Avea Improves Performance with Oracle Data Integration and Oracle Exadata video (bit.ly/gpHaag).

Pull the Plug on Your IBM Mainframe fbit.ly/egF7ek Find out how Oracle Tuxedo and Oracle GoldenGate enable IBM mainframe customers to move to modern, efficient systems with minimized risk and cost. Upgrade Your Custom Java and Forms Applications to Oracle WebLogic Server fbit.ly/kl7cML Hear how upgrading to Oracle WebLogic Server can help you improve your development productivity and flexibility and manage your production applications more efficiently. No Contest: Oracle WebLogic Outperforms IBM WebSphere fbit.ly/jEpAoQ Learn how Oracle WebLogic Server outperforms IBM WebSphere in TCO, performance, and cloud readiness.

E-BOOKS The Most Complete and Integrated Virtualization ftek.io/lbbMNG Learn how a complete and integrated virtualization solution can provide end-to-end visibility and management to lower costs. Storage Tiering for Dummies, Oracle Special Edition fbit.ly/j5uHmi Learn how to implement tiered storage in your organization and find out how it can intelligently improve storage performance, increase capacity, and drive costs down.

WHITE PAPERS Consolidated Storage for Virtualized Server Environments fbit.ly/jaVdKc This report details the results from a recent survey

July/August 2011 Oracle.com/oraclemagazine

of 200 IT professionals about the storage-related challenges caused by server virtualization.   Gaining Business Advantage Through Storage Management Efficiency with Oracle’s Sun ZFS Storage Appliance fbit.ly/iZyXLN Edison Group analysts provide the results of a manageability study comparing Oracle’s Sun ZFS Storage Appliance to a NetApp storage solution.   In Search of the Long-Term Archiving Solution fbit.ly/ldBN7Y Clipper Group analysts reveal why tape is the best solution for long-term storage of digital media.

RESOURCE CENTERS Oracle Optimized Datacenter foracle.com/goto/dc-optimize Explore the Oracle Optimized Datacenter, which features strategies and solutions to help you solve your datacenter challenges.


Self-Study Training for Java foracle.com/education/sscd Self-study training on CD-ROM is a convenient way to learn. New Java self-study courses include Web Component Development with Servlets and JSPs, Java EE 6; and Developing Web Applications using JSF Technologies. Certification Value Packages
 fbit.ly/j4erZy Save up to 20 percent and get the added advantage of a free certification retake when you purchase an all-in-one Certification Value Package, available for Java, Oracle Solaris, Oracle Database 10g, Oracle Database 11g, and MySQL. Oracle Exalogic Elastic Cloud Training fbit.ly/lxCKS3, bit.ly/jYVqp9 Get up to speed on Oracle Exalogic with Oracle University’s Oracle Exalogic Elastic Cloud Overview Seminar and Oracle Exalogic Elastic Cloud Administration course.

TOUR Tour the Oracle Utah Compute Facility f1.800.633.0738 Sign up through your Oracle license sales representative to tour Oracle’s newest datacenter. This datacenter exemplifies how Oracle uses its own technologies to deliver business results, solve IT problems, and drive down costs.

web locator Oracle Database 11g Resource Center foracledatabase11g.com Access the latest Oracle Database 11g tutorials, white papers, customer success stories, and more. Oracle Exadata Resource Kit fbit.ly/kauRce Learn about Oracle Exadata’s extreme performance for data warehousing and online transaction processing applications through videos, demos, analyst reports, and customer stories. Enterprise Performance Management and Governance, Risk, and Compliance Center fbit.ly/iKdahb Learn about Oracle’s enterprise performance management and governance, risk, and compliance (GRC) solutions through videos, demos, white papers, data sheets, and podcasts.

Oracle Blog Center oracle.com/blogs Oracle Consulting oracle.com/consulting Oracle Events and Webcasts oracle.com/events Oracle Podcast Center oracle.com/podcasts Oracle Newsletters oracle.com/newsletters Oracle University oracle.com/education Oracle on Twitter twitter.com/oracle Oracle Support oracle.com/support My Oracle Support myoraclesupport.com My Oracle Support Communities communities.oracle.com

Leaders Run Oracle Storage 10 of the 10 Top Banks Top Telcos Top Pharmas Top Governments

Get Better Results

oracle.com/goto/storage or call 1.800.ORACLE.1

Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates.

Product Resources

DEMOS Oracle Hyperion Data Relationship Management

Learn how Oracle Hyperion Data Relationship Management models business rules and constraints of managing the business to streamline change management. fbit.ly/mgeSmz

Oracle WebLogic Server 10.3.4 Zip Distribution

Find out why Oracle WebLogic Server zip distribution reduces the download size of Oracle WebLogic Server by more than 70 percent and enables faster download-torunning times for users. fbit.ly/lgjVSb

Active Cache for Oracle GlassFish Server (Parts 1 and 2)

This two-part series on the Active Cache feature for Oracle GlassFish Server discusses concepts and basic deployment and setup, reviews the product feature set, and shows you how to configure Active Cache. fbit.ly/ijTaK9 fbit.ly/juSPSW

Oracle Releases Oracle ADF Mobile Client Oracle Application Development Framework Mobile Client (Oracle ADF Mobile Client), a component of Oracle Fusion Middleware that allows developers to create applications for mobile devices, is now available. Oracle ADF Mobile Client is based on the same programming paradigm as JavaServer Faces and uses a single Java user interface framework and tooling for all supported devices. Developers can build an application with Oracle ADF Mobile Client, deploy it to multiple devices, and extend it to new platforms without redevelopment. Developers using Oracle ADF Mobile Client can employ a mix of visual editing tools and Java code to define a deviceindependent representation of an application. Oracle ADF Mobile Client provides design tools

and data synchronization services for offline data synchronization, as well as real-time data links via Web services. Data can also be cached locally for hybrid online/offline mobile applications. Applications created with Oracle ADF Mobile Client can access local device hardware (such as cameras, barcode readers, and GPS devices) through Java code embedded in the application. “The increased adoption of mobile business applications requires Java developers to create and extend mobile applications faster than ever before,” says Ted Farrell, chief architect and senior vice president of tools and middleware at Oracle. “Oracle ADF Mobile Client enables developers to leverage their existing skill sets to efficiently deploy applications across mobile devices and platforms and address rapidly increasing mobility requirements within the enterprise.” bit.ly/gKrPpm

TUTORIALS

Java EE 7 Java Specification Request Approved

Oracle BPM 11g Quick Start Series

In this tutorial series, members of Oracle’s maximum availability architecture team share tips, tricks, and best practices for implementing, managing, and working with Oracle Exadata Database Machine. fbit.ly/kdnTKr

The Java Community Process (JCP) Executive Committee has approved the Java Specification Request (JSR) for JSR 342, the next release of Java Platform, Enterprise Edition (Java EE). Java EE 7 enhances the Java EE platform for cloud environments and will allow Java EE 7–based applications and products to operate more easily in private and public clouds. Features such as multitenancy and elasticity will allow Java EE 7 applications to function as a service. The JCP also approved JSR 340 for Java Servlet 3.1, JSR 341 for Expression Language 3.0, JSR 343 for Java Message Service 2.0,

Respin Your Desktop Application Using Java Web Start

MySQL 5.6 Development Milestone Release Available

This series of four short tutorials covers the essentials of using Oracle Business Process Management 11g to model and simulate, implement, participate in, and monitor a business process. fbit.ly/jQvfRa

Oracle Exadata Database Machine Best Practices Series

Learn how to respin your desktop application by converting it into a Java Web Start application. fbit.ly/kJpKwP

DOWNLOADS New Downloads

Oracle Database, Express Edition 11g fbit.ly/k1G8xB NetBeans IDE 7.0 fnetbeans.org

July/August 2011 Oracle.com/oraclemagazine

Now available, the first development milestone release for MySQL 5.6 delivers increased performance, scalability enhancements, an improved InnoDB storage engine, and enhanced replication capabilities. MySQL 5.6 has an improved optimizer, for better query performance; more partitioning options, for faster access to relevant data; and better performance monitoring and tuning. InnoDB now offers more-predictable optimizer plans; more-efficient purge opera-

and JSR 344 for JavaServer Faces 2.2. These submissions complement JSR 338 for Java Persistence API 2.1 and JSR 339 for Java API for RESTful Web Services. “Java is already the most pervasive serverside development and deployment platform for the cloud,” says Steven G. Harris, senior vice president of Oracle Fusion Middleware product development. “With Java EE 7, enterprise developers and deployers will see additional cloud-specific innovation built into the standard and available across many vendor implementations.” jcp.org/en/jsr/summary?id=342

tions; and comprehensive resources information for tuning and troubleshooting. “With this first MySQL 5.6 development milestone release, we are offering early access to new stable features for testing,” says Tomas Ulin, vice president of engineering at Oracle. “Oracle continues to innovate and enhance MySQL Database, delivering a higher-performing, more scalable, more reliable, and easier-to-use MySQL.” dev.mysql.com

I-Hua Chen

16

BRIEFS

Oracle Enterprise Gateway 11g Secures SOA Deployments To help enterprises secure their SOA deployments, Oracle has unveiled Oracle Enterprise Gateway 11g, which secures, accelerates, integrates, and routes XML, Web services, and other types of data. Oracle Enterprise Gateway offers out-ofthe-box integration with Oracle SOA Suite 11g, Oracle SOA governance, Oracle Identity Management 11g, and Oracle Enterprise Manager. It includes demilitarized-zone-class security for SOA and cloud environments; XML processing that enables SOA and cloud applications to offload resource-intensive operations; an open, standards-based architecture that plugs into third-party platforms

and environments; and XML connectivity and security capabilities that safeguard traffic between heterogeneous environments. “Oracle is the only SOA and identity management vendor to offer comprehensive threat mitigation, identity propagation, secure connectivity, and compliance for SOA environments,” says Amit Jasuja, vice president of development, identity management, and security products at Oracle. “Using Oracle Enterprise Gateway, our customers have enhanced security and flexibility to confidently extend their enterprise datacenter into the cloud.” bit.ly/g1WIHg

Oracle Updates Enterprise Performance Management System Oracle has unveiled release 11.1.2.1 of its Oracle enterprise performance management applications, which includes updates to Oracle Hyperion Disclosure Management and Oracle Hyperion Financial Close Management, as well as enhanced upgrade tools and expanded platform support. The update includes support for inline Extensible Business Reporting Language, enabling compliance with new U.K. financial reporting standards. The new release is also integrated with Oracle’s PeopleSoft Enterprise financial man-

agement applications, enabling organizations to streamline and manage closing activities. “As regulatory bodies continue to mandate new global requirements for financial reporting, organizations need ways to simplify execution, while ensuring compliance,” says Hari Sankar, vice president of product management at Oracle. “Oracle continues to develop and enhance its performance management applications to support the changing needs of global businesses.” bit.ly/k9Yvhh

Oracle Expands Integrations for Sun ZFS Storage Appliance Oracle has announced three new integrations with the Sun ZFS Storage Appliance, its family of high-performance, high-density storage systems. First, the appliance now directly connects to the Oracle Exadata InfiniBand fabric. This simplifies deployments and can accelerate backup and recovery times by more than 50 percent compared to traditional networkattached storage systems. Second, Oracle Virtual Desktop Infrastructure now integrates with the Sun ZFS Storage Appliance using rapid iSCSI provisioning. This automates desktop provisioning and accelerates

virtual desktop deployments. Third, Oracle Secure Backup works with Sun ZFS Storage Appliance backup and recovery in conjunction with Oracle’s StorageTek tape libraries. This combination brings together unified storage backup and tape. “Oracle’s Sun ZFS Storage Appliance delivers compelling value for customers on three major fronts: best-in-class management, with DTrace analytics and hybrid storage pools; a highly scalable architecture using integrated flash combined with the Oracle Solaris operating system; and seamless integration with Oracle applications,” says Scott Tracy, senior director of flash and disk products at Oracle. “Engineered with Oracle software, the Sun ZFS Storage Appliance offers customers a mature storage solution they can trust.” bit.ly/hJGMKK

Oracle Enhances MySQL Enterprise Edition An update to MySQL Enterprise Edition 5.5— a subscription offering of MySQL that includes MySQL Database; monitoring, backup, and design tools; and 24/7 support—is now available. The update adds the Enterprise Backup feature, which enables hot online backup and full, partial, and incremental backups; and MySQL Workbench, which provides data modeling, SQL development, and comprehensive administration tools. MySQL Enterprise Edition 5.5 integrates with My Oracle Support, allowing MySQL customers to access the same support infrastructure as Oracle Database customers. “With the enhancements to Enterprise Monitor and Enterprise Backup, along with streamlined, global support, DBAs and developers can speed their MySQL deployments while reducing total cost of ownership,” says Tomas Ulin, vice president of engineering at Oracle. bit.ly/hVkPjb

NetBeans IDE 7.0 Released Oracle has launched NetBeans 7.0, the newest edition of the NetBeans integrated development environment (IDE). It adds or improves support for several key technologies. In the area of Java Platform, Standard Edition, NetBeans 7.0 includes Java language support for the upcoming Java Development Kit 7 (JDK 7). It also features editor enhancements, support for Maven 3, integration with JUnit 4.8.2, and support for remotely hosted Javadoc. In server technologies, NetBeans 7.0 offers streamlined and faster deployment to Oracle WebLogic Server 11g and supports Oracle GlassFish Server 3.1. For Oracle Database, NetBeans 7.0 supports editing and deployment of stored procedures, simplifies the connection wizard, and offers guided installation to JDBC drivers. “Oracle continues to enhance the NetBeans platform, providing developers with a feature-rich, open source IDE for the creation of Java-based mobile, Web, and desktop applications,” says Ted Farrell, chief architect and senior vice president of tools and middleware at Oracle. bit.ly/iFFUoP

ORACLE MAGAZINE JULY/AUGUST 2011

17

18

BRIEFS

Oracle Delivers Validated Oracle Linux Configurations

Oracle Introduces AutoVue 20.1

Three new Oracle Validated Configurations, based on Oracle Linux and offering pretested, validated architectures, are now available. The configurations include Oracle Linux 5 with the Unbreakable Enterprise Kernel, Oracle Database 11g, Oracle Real Application Clusters, Oracle’s Sun ZFS Storage 7420 appliance, and one of the following servers: Oracle’s Sun Fire X4800 server (pictured), an 8-socket rackmount x86 system; Oracle’s Sun Fire X4470 server, a 4-socket rackmount x86 system; or Oracle’s Sun Fire X4270 M2 server, a 2-socket rackmount x86 system. “The Oracle Validated Configurations are yet another example of Oracle’s commitment to engineering our hardware and soft-

Oracle has unveiled AutoVue 20.1, the latest release of its family of enterprise visualization solutions, which delivers a robust enterprise-class architecture that can serve an organization’s complete document visualization needs within a single platform. The release includes new electronic computer-aided design features that support end-to-end, design-to-manufacture processes for customers in electronics and high technology; improved support for paper-based processes for customers in asset-intensive industries; and new integration capabilities that enable partners to build customized solutions. AutoVue 20.1 also includes performance, reliability, and stability enhancements and new tools to simplify deployment and administration. “The value of enterprise visualization is to synthesize visual and nonvisual information in the context of specific business processes to increase efficiency and improve a company’s bottom line,” says Thierry Bonfante, senior director, AutoVue product strategy/ management at Oracle. “We have implemented a rich hotspotting framework that can associate parts of drawings to any logic contained in enterprise systems and delivered several high-value business process solutions.” bit.ly/eWCwYO

ware together to deliver high-performance application-to-disk solutions,” says Ali Alasti, vice president of hardware engineering at Oracle. “The tight integration between our x86 clustered systems, Sun ZFS Storage Appliance, and Oracle software takes out the guesswork for customers and delivers differentiated value unmatched in the market.” bit.ly/dOgRRL

Oracle Database Migration Assistant for Unicode Unveiled Oracle has released Oracle Database Migration Assistant for Unicode, a tool for migrating databases from legacy encodings to Unicode. Oracle Database Migration Assistant for Unicode simplifies the migration process and lessens the need for character set migration expertise by guiding DBAs through the migration process and automating many tasks. For databases that already use Unicode, Oracle Database Migration Assistant for Unicode has a validation mode that identifies data not correctly encoded and provides a health check on potential issues. Oracle Database Migration Assistant for Unicode is available free from Oracle Technology Network and My Oracle Support. The two download packages are identical, but the first is available under the Oracle

Technology Network Developer License (which allows users to evaluate the tool), while the second is provided under the database support contract (which permits migration of production databases covered by the contract). “This new tool makes it easy for customers to migrate their databases to Unicode, whether they are expanding the global reach of their database applications or consolidating their global databases,” says Andy Mendelsohn, senior vice president of database server technologies at Oracle. “It allows customers to quickly identify and cleanse problematic data to ensure a safe migration to Unicode, while its unique inplace conversion technology significantly reduces the migration downtime.” bit.ly/fjD2t3

Oracle Health Sciences Clinical Development Center 3.1 Introduced Now available, Oracle Health Sciences Clinical Development Center 3.1 is a solution for integrating, managing, and analyzing clinical trial information. It improves integration, increases automation, and expands data standardization to enable accelerated processing of clinical trial data from multiple sources in a variety of formats. The automated data load and new execution triggers in Oracle Health Sciences Clinical Development Center increase the timeliness and accuracy of data analysis. The

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

software can rerun routines and programs automatically when new data arrives. “Oracle Health Sciences Clinical Development Center 3.1 enables organizations to further accelerate data aggregation and reconciliation, streamline preparation for analysis, automate the downstream analysis process, and ensure control and compliance,” says Neil de Crescenzo, senior vice president and general manager of Oracle Health Sciences. bit.ly/ikASyV

Oracle On Demand Adds Strong Authentication Oracle On Demand has added strong authentication to its service portfolio. This addition addresses the growing number of security threats that target enterprises’ internetfacing applications. Powered by Oracle Adaptive Access Manager, Oracle On Demand Strong Authentication Service provides both implementation and managed services, eliminating the need to engage a third-party implementer. The service requires no changes to the protected application. “[The new service] provides a unique value for Oracle cloud customers by combining the feature-rich Oracle Adaptive Access Manager product with the benefits of an Oracle managed service,” says Monika Thakur, product manager for Oracle On Demand. bit.ly/f2GDmt

Interview by Tom Haunert

First Line of Defense

Oracle Database Firewall monitors database traffic and blocks unauthorized activity.

John Blythe

T

om Haunert, Oracle Magazine editor in chief, recently sat down with Steve Moyle, chief technical officer of Oracle Database Firewall, for an in-depth discussion of that product’s cutting-edge security features and functionality. The following is an excerpt from that interview. Download the full podcast at oracle.com/magcasts. Oracle Magazine: Let’s start at the beginning. Our readers are likely familiar with the concept of a firewall, but what’s different about a database firewall? Moyle: A database firewall has some similarities with a traditional network-based firewall, in that it’s about protecting and controlling items that flow across the network. But unlike a network-based firewall that’s predominately focused on ensuring only the right destinations can connect to the right sources, a database firewall is entirely focused on the conversations that are flowing in and out of your databases. Oracle Database Firewall has a complete understanding of the database language— SQL—and it’s this database language that powers the firewall and ensures that only the conversations you want to have going into your database are permitted. All others can be blocked. Oracle Magazine: What are some other key features of Oracle Database Firewall? Moyle: The database firewall is there to monitor database activity and to help prevent unauthorized access to and particular attacks on databases, including SQL injections, privilege or role escalations, and illegal access to sensitive data. Oracle Database Firewall has a highly accurate approach to controlling interactions with the database, based on a SQL grammar analysis. This ensures that there are no costly false alarms triggered by the firewall. The product also provides a very flexible level of enforcement options based on

Oracle Database Firewall reduces the risk profile of a database at the network level. two different security paradigms: a positive security model known as white lists, and a negative security model known as black lists. Customers deploy the firewall using a combination of these two paradigms. Going beyond this, the firewall has a very scalable architecture that provides enterprise-level performance in many different deployment modes. The great news for database administrators and application authors is that it requires no configuration changes at either the application level or the database level to provide a very powerful level of protection in the environment. Oracle Magazine: You mentioned SQL injection, and that’s of course something DBAs

Steve Moyle, Chief Technical Officer of Oracle Database Firewall

and database developers must always be conscious of. How does Oracle Database Firewall address SQL injection and other threats? Moyle: SQL injection is a development-side error. It’s an application-layer error, but it’s not just one single mistake—it can manifest in many different ways. It’s also entirely application specific; each application that has a particular SQL injection vulnerability will appear different to its database than its operator. Since there’s no one-size-fits-all way of looking for SQL injections, the technology for protecting against them needs to understand what is normal for each database and application in the operating environment. However, with this level of understanding comes protection against all manner of risks. So although a SQL injection is often caused by a malicious attacker on the outside, the same sorts of controls work for protecting against a malicious user on the inside or perhaps someone who has connected a piece of software to the network that is outside policy. All of these unauthorized access mechanisms can be protected against very powerfully by Oracle Database Firewall. Oracle Magazine: What are some other key risk areas that Oracle Database Firewall can address to ensure security in the enterprise? Moyle: Oracle Database Firewall reduces the risk profile of a database at the network level by ensuring that only permitted locations can connect through to the database—only permitted applications at permitted times of day with permitted users. For example, high-privilege users may only connect to the database from their workstations at particular times of day, which are their normal hours of business. Should the credentials for a high-privilege user become compromised, it would not be possible to use them from another location within the organization’s network.

oracle magazine July/August 2011

19

20

INTERVIEW

In addition to containing privileged-user access, a database firewall also ensures that only in-policy applications are allowed to connect to the database. It also provides the ability to control and enforce controls around access to sensitive data. Oracle Magazine: With the explosion of tablet computing and smartphones, mobile computing and specifically mobile enterprise computing are a much bigger part of IT than ever before. How does Oracle Database Firewall and its permission strategy address mobile computing? Moyle: The real extra risk with mobile computing is about the increase in the number of different applications that are calling for information from the database. Each of those applications should be restricted to

LISTEN to the podcast oracle.com/magcasts

interacting only with the data in approved methods, and Oracle Database Firewall enables administrators to create and guarantee a restriction so that only permitted database queries are allowed through, regardless of which application they come from. As customers demand more and more functionality from different devices to access their data, the firewall can guarantee that the data access is still limited to only those queries permitted within the organization’s policy. In a sense, although Oracle Database Firewall is not aware of the tablet or the mobile application, it is aware of what a good and safe conversation looks like between the database, the application layers, and the end user. Oracle Magazine: Where does Oracle Database Firewall fit into the mix of Oracle database security solutions? Moyle: Oracle provides many layers of security products and technologies—it’s part of our “defense-in-depth” strategy. On the

outside, the first approach to the database as things travel across the network, we have the blocking and logging capability of Oracle Database Firewall. As we walk closer to the center of the database, there are auditing and monitoring products such as Oracle Audit Vault and Oracle’s configuration management tools. Then, as we get into the database itself, we have access control products: Oracle Label Security and Oracle Database Vault. Finally, for the actual secure storage of the data at risk itself, we have Oracle Advanced Security for encryption and other masking products. Oracle Database Firewall is the first layer of defense in this defense-in-depth architecture of products. t

NEXT STEPS LEARN more about Oracle Database Firewall bit.ly/jW2cMC

ONE SOURCE FOR SIMPLIFIED SOLUTIONS

At Alliance Technology Group, we bring together our in-depth experience and knowledge of enterprise IT infrastructure with the power, performance and versatility of Oracle® applications to develop solutions that will make your data center more productive and efficient.

From initial assessment to integration to service and support, Alliance Technology Group brings proven expertise to every aspect of your technology initiatives. We deliver powerful solutions that add value and lower costs, giving you higher return on your investment. » Oracle SPARC T3 Servers enhance performance of multi-threaded applications. » Oracle’s F5100 Flash Arrays cut transaction times by half, while saving space and power. » Oracle’s ZFS Storage Appliances consolidate and simplify data management across multiple data centers.

Alliance Technology Group and Oracle. We’ve got the solution you’re looking for. Visit

www.alliancespecialized.com © 2011 Alliance Technology Group, Oracle and/or its affiliates. All rights reserved. All company logos and product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

JA11_interview_R2.indd 20

5/31/11 3:47:41 PM

Your Destination for oracle anD Java expertise Written by leading technology professionals, Oracle Press books offer the most definitive, complete, and up-to-date coverage of Oracle products and technologies available.

Managing oracle fusion applications Richard Bingham Best practices for a high-performance enterprise application management strategy

oracle vM implementation and administration Guide Edward Whalen Install, configure, and implement this powerful virtualization solution

oracle o racle Database 11g 11g Building xMl DB applications Jinyu Wang Best practices for using XML in Oracle applications

Java 7: t the he c complete omplete r reference, eference, eighth edition Herbert Schildt A fully updated edition of the definitive guide for Java programmers

Java 7: a Beginner’s Guide, fifth edition Herbert Schildt Essential Java programming skills made easy

Java 7 programming Poornachandra Sarang Learn advanced skills from a Java expert

Available in print and e-book format Join the Oracle Press Community: www.OraclePressBooks.com Follow us @MHComputing

Book Beat Oracle Fusion Middleware 11g Architecture and Management By Reza Shafii, Stephen Lee, and Gangadhar Konduri Oracle Press mhprofessional.com This book provides best practices for deploying, securing, virtualizing, and managing enterprise-grade Oracle Fusion Middleware applications throughout their lifecycles. It is organized into two parts: Part 1 provides an end-to-end description of the architecture of Oracle Fusion Middleware components—Oracle WebLogic Server, Oracle SOA Suite, Oracle WebCenter, Oracle Application Development Framework, and Oracle Identity and Access Management Suite. A separate chapter introduces each component of the Oracle Fusion Middleware 11g stack and includes a sample use case showing how the chapter’s concepts apply to a real-life scenario. Part 2 focuses on provisioning, securing, optimizing, monitoring, and virtualizing applications built on Oracle Fusion Middleware.

Oracle Enterprise Manager Grid Control 11g R1: Business Service Management By Ashwin Kumar Karkala and Govinda Raj Sambamurthy Packt packtpub.com This book is a pragmatic, hands-on guide to modeling and managing datacenters and their corresponding business services using Oracle Enterprise Manager 11g Release 1. It begins with a discussion of business service management, including the evolution of enterprise IT infrastructure and the modeling paradigms to manage it; the various techniques in modeling complex datacenters available in Oracle Enterprise Manager; and ways to model and define various composite targets such as groups, systems, and services. The book also helps readers understand active and passive modeling using beacons, service tests, and system targets, and provides information on monitoring through service levels and dashboards.

Expert Oracle Application Express By John Scott and others Apress apress.com In this book, the authors—some of the top Oracle Application

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

Express practitioners working today— explain how to develop secure applications that can be deployed globally, both inside and outside of large enterprises. From their years of experience, the authors share insights into application development challenges including scalability, globalization, configuration, and lifecycle management. Readers will learn methods for debugging and improving performance, ways to customize an application’s user interface, and techniques for securing applications from intrusion. Ideas are presented in a problem/ solution format, complete with examples that readers can copy and adapt.

Data Architecture: From Zen to Reality By Charles Tupper Elsevier elsevier.com Data architecture is the blueprint for organizing and structuring information for services, service providers, and consumers of data. This book explains the principles underlying data architecture, how data evolves with organizations, and the challenges organizations face in structuring and managing their data. It also discusses the methods and technologies used today for dealing with data. The book uses a holistic approach, covering topics including data modeling and data model management, data quality, data governance, enterprise information management, database design, data warehousing, and data warehouse design.

Getting Started with Oracle Hyperion Planning 11 By Enti Sandeep Reddy Packt packtpub.com This practical guide shows how to design, configure, and implement a robust planning, budgeting, and forecasting solution using Oracle Hyperion Planning. It walks the reader through installation of the product and then shows how to design planning applications and create planning objects. The book goes on to explain concepts related to Oracle Hyperion products such as data forms, task lists, business rules, validation rules, and workflows—all with the help of real-world examples. Finally, the book covers user provisioning, access rights, and budget process management. The book offers step-by-step instructions and detailed screenshots.

Look for other Oracle books at oracle.com/ technology/bookstore.

Xsigo Certifies Oracle VM Oracle Gold Partner Xsigo Systems has certified Oracle VM on its virtual I/O solutions. By using Oracle VM with Xsigo’s virtual I/O technology, organizations can create a flexible, high-performance, converged datacenter infrastructure. Benefits include the ability to guarantee bandwidth to specific virtual machines, resulting in performance equal to traditional hard-wired connections; the ability to move a server or virtual machine from one physical network to another without recabling; and high-speed, low-latency switching that allows servers to move traffic at speeds up to 40 Gb/sec using standard TCP/IP protocols. xsigo.com

Avnet Expands Oracle Relationship in Asia-Pacific Avnet Technology Solutions, an IT solutions distribution company and an operating group of Avnet, Inc., has expanded its relationship with Oracle in the Asia-Pacific region. An Oracle Platinum Partner, Avnet will provide channel partners in Singapore, Vietnam, Australia, New Zealand, and India with a complete range of Oracle hardware and software solutions. Avnet’s expansion in the region is a result of acquisition and business growth. In January 2011, Avnet acquired itX, which distributed Oracle products in Australia; Avnet subsequently launched new franchises in India, New Zealand, and Association of Southeast Asian Nations (ASEAN) countries. Avnet will provide Oracle partners in these countries with channel support, including training, enablement, lead generation, comarketing, technical integration, services, and ongoing support. Avnet will also give partners access to its SolutionsPath practices, which allow partners to gain expertise in industries such as energy, financial, government, healthcare, and retail. Avnet also provides datacenter technology expertise related to mobility, networking, security, storage, and virtualization. avnet.com

I-HUA CHEN

22

Partner News

Syntellect to Integrate Computer Telephony with Oracle E-Business Suite

Quest Introduces Backup Reporting Tool

Oracle Gold Partner Syntellect, the provider of the computer telephony integration platform Syntellect CT Connect, is working with Oracle to build and certify an adapter between the latest releases of Syntellect CT Connect and Oracle E-Business Suite. Currently, Syntellect CT Connect for Oracle E-Business Suite provides integration between Oracle Advanced Inbound Telephony and a variety of telephone switches. It enables agents answering

Oracle Platinum Partner Quest Software has released Quest Backup Reporter for Oracle. This lightweight desktop solution enables DBAs to view all of their Oracle database backups across an entire enterprise from a single screen and then drill down into individual databases for detailed reporting. Quest Backup Reporter for Oracle tracks a variety of reporting metrics, including backup performance, storage consumption and growth rates, and policy reporting. It can also provide customizable alerts and notifications, and it enables DBAs to plan and view scheduled Oracle backups across the entire datacenter. quest.com

calls to quickly understand the context of a call through screen pop-ups containing relevant information about the customer. Additionally, click-to-dial functionality allows agents to dial contacts directly from within business applications. All telephone interactions with customers are automatically logged. syntellect.com

ClearPeaks and Projected Consulting Launch Partnership Oracle partners ClearPeaks and Projected Consulting have joined forces to offer complete Oracle E-Business Suite and Oracle business intelligence (BI) solutions. The two companies, both providers of enterprise performance management and BI solutions, offer organizations the benefits of their combined experience, in-depth knowledge, and expertise. The partnership launched with the

ClearPeaks BI Academy, held alongside the related Oracle E-Business Suite courses already offered by Projected Consulting at its Oracle Approved Education Center. Oracle Gold Partner ClearPeaks is a Specialized partner for Oracle BI solutions and an Oracle BI training partner. Projected Consulting is an Oracle Platinum Partner. clearpeaks.com projectedconsulting.com

I-Hua Chen

SmartDog, Centroid, eVerge Achieve Specializations Three Oracle partners have achieved Oracle PartnerNetwork Specialized status in various disciplines: SmartDog Services has achieved Oracle Platinum Partner status and Oracle PartnerNetwork Specialized status in five separate Oracle disciplines: Oracle E-Business Suite financial management and supply chain management applications, Oracle Linux, Oracle Enterprise Manager, and Oracle Real Application Clusters. SmartDog’s specializations and Platinum status are an acknowledgement of the company’s expertise as measured by solutionspecific education and testing and through the endorsements of companies who have benefited from its services in those areas. SmartDog Services provides consulting services for Oracle products and solutions, ranging from full-scale implementations to ongoing support. Centroid, an Oracle Gold Partner and a provider of technology consulting and outsourcing services, has achieved Oracle

PartnerNetwork Specialized status in Oracle Database 11g. To achieve this status, Centroid met stringent requirements for competency and business results, based on the needs and priorities of Oracle’s customer and partner communities. Centroid has also opened an Oracle Exadata Center of Excellence that offers deployment, implementation, and management services. eVerge Group, an Oracle Platinum Partner, has achieved Specialized status in six categories: Oracle’s Siebel Customer Relationship Management, Oracle CRM On Demand, Oracle Business Intelligence foundation, Oracle’s PeopleSoft Enterprise human capital management solutions, PeopleSoft PeopleTools, and Oracle Public Sector. Since 1993, eVerge Group has completed hundreds of successful projects in these areas and built a broad understanding of how various Oracle solutions work together. smartdogservices.com centroid.com evergegroup.com

Schooner Releases MySQL Tool Oracle Gold Partner Schooner Information Technology has unveiled Schooner Active Cluster for MySQL Enterprise Edition, which enables enterprises using MySQL to provide high-performance synchronous replication and automated failover among nodes in a MySQL cluster. Schooner Active Cluster complements MySQL technologies such as semisynchronous replication and the native MySQL Cluster by guaranteeing consistency of data among master and slaves; reducing downtime with immediate and automatic failover and recovery; and providing guaranteed synchronous replication performed in parallel with executing transactions. It provides full cluster management from a single console, one-click clustering, and integrated backup tools. schoonerinfotech.com

MindStream Analytics Achieves Hyperion Specialization Oracle Gold Partner MindStream Analytics has achieved Oracle PartnerNetwork Specialized status for Oracle Hyperion Planning. The company, a consulting and managed services provider, was recognized for its expertise in delivering Oracle Hyperion Planning services through competency development and business results. mindstreamanalytics.com

oracle magazine July/August 2011

23

PARTNER NEWS

Infogain and OLR to Collaborate on Oracle Retail Solutions Infogain and OLR, both Oracle Gold Partners, have formed a strategic alliance to provide global Oracle Retail consulting and systems integration services. Infogain specializes in point-of-sale and store operations systems, while OLR works in merchandising, planning, and optimization. Together, the two companies will provide end-to-end solutions for all types of retailers. The partnership addresses the need

among retailers for cost-effective crosschannel integration. The two companies will provide a holistic offering based on Oracle Retail products and solutions, while providing additional support that complements existing integrations. Infogain has more than 1,000 professionals serving some 300 clients, including more than 50 of the Fortune 500. OLR is an experienced Oracle Retail delivery partner with operations in more than 25 countries. infogain.com olrretail.com

Fellow Consulting Enhances Oracle CRM On Demand Oracle Gold Partner Fellow Consulting has updated CRM Gadget 2 Go, a client for Oracle CRM On Demand that allows users to access customer relationship management (CRM) information while offline and then synchronize it with Oracle CRM On Demand when back online. With the update, CRM Gadget 2 Go now

supports barcode scanners; in one possible use case, this capability would enable service technicians to scan materials they use at customer sites and incorporate information about customer assets or replacement parts into a service request without performing manual data entry. crmgadget2go.com

Partners Achieve Validated Integrations Three Oracle partners have achieved validated integrations with Oracle solutions: Accept Corporation, an Oracle Platinum Partner, has achieved validated integration of its Accept360 Ideas product with Oracle CRM On Demand 18 and Oracle CRM On Demand for Partner Relationship Management. The integration adds social collaboration–based capabilities to Oracle CRM, enabling users to create internal and external social communities. This gives business leaders visibility into market needs and creates a single repository of ideas and product requirements. Companies using Accept360 Ideas with Oracle CRM can capture and streamline market, customer, and partner feedback in the context of CRM objects. Blue Coat Systems, an Oracle Gold Partner and a provider of Web security and widearea-network (WAN) optimization solutions, has achieved validated integration of its Blue Coat ProxySG version 6 Acceleration Edition appliance for WAN optimization with Oracle E-Business Suite 11.5.10 and Oracle E-Business Suite 12.1, as well as

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

with Oracle’s Siebel Customer Relationship Management 7.8 and Siebel Customer Relationship Management 8.1. Blue Coat WAN Optimization appliances compensate for network bottlenecks and other conditions that cause centralized applications to perform slowly in branch and remote offices. The appliances optimize file access, e-mail, storage, and backup, as well as Web, cloud, and video applications. Solix, an Oracle Gold Partner, has achieved validated integration of Solix Enterprise Data Management Suite (Solix EDMS) with Oracle’s PeopleSoft Enterprise 9.1. Solix EDMS archives transactional business objects for PeopleSoft Enterprise applications using criteria based on business rules, corporate policies, and retention periods, and allows customers to preview which transactions will be archived. The validation testing included Solix EDMS features such as data referential integrity and secure data archiving. acceptsoftware.com bluecoat.com solix.com

WebNMS Framework Integrates with MySQL and Oracle Database Oracle Platinum Partner WebNMS, a division of ZOHO Corporation, has integrated MySQL and Oracle Database 11g in its WebNMS Framework, a network and element management solution. WebNMS needed a relational database solution that would allow its management system to support the wideranging, complex requirements and standards of the communications industry. With the flexibility to integrate either MySQL or Oracle Database 11g in the WebNMS Framework, developers and network operators can accommodate the increasingly stringent demands for availability and performance on network management workloads, including the proliferation of nodes, higher bandwidth, and richer communication services. webnms.com

C3i Opens Asia Language Center in Dalian, China Oracle Gold Partner C3i, a provider of technology support and business process outsourcing (BPO) services for the pharmaceutical industry, has opened a global operations center in Dalian, China. The center will provide support to joint C3i and Oracle customers in Chinese, Korean, and Japanese languages. C3i works with Oracle to provide training, multilingual support, and BPO services to life science and pharmaceutical organizations worldwide. It also supports global pharmaceutical sales forces using Oracle’s Siebel Customer Relationship Management. Oracle and C3i currently have four joint customers in the Asian market and expect to add new customers and help existing ones in other markets expand into Asia. C3i’s Dalian center shares a single technology infrastructure, best practices, and a global management team with C3i’s other locations. The 1,200employee company provides end-to-end support solutions for more than 250,000 clinical users and 50,000 pharmaceutical sales representatives worldwide. c3i-inc.com

I-HUA CHEN

24

Community Bulletin by Justin Kestelyn

Survey Says

Oracle Technology Network community members weigh in on what they do and what they want more of, including discounts and free stuff.

S

ometimes you have to give people what they want; other times, you have to give them what they need. The Oracle Technology Network team has always prided itself on its ability to do both things. (Sometimes, if we’re lucky, wants and needs are actually the same things.) So although we are responsive to feedback from our community, we often also feel the need to lead by pushing our “customers” toward mindsets and approaches they may not have considered if left to their own devices. If you are an Oracle Technology Network regular, perhaps you’ve noticed that during the period between April 2010 and January 2011, an online survey was made available via our newsletters and Website. Although surveys are a routine process, this particular one—which fell on the heels of the Sun Developer Network/Oracle Technology Network merger—was more important than most. With the new blood that this combination brought to the Oracle Technology Network community, we wanted to identify what each respective program (Sun Developer Network and Oracle Technology Network) had done right and wrong, and then focus on the former going forward. We received nearly 400 responses from across the globe (10 percent of respondents were not yet Oracle Technology Network members)—which is not a bad sample, considering that we decided not to offer incentives that would encourage “mercenary” participation. As I often like to say, participation is its own reward. So, here’s some of what we learned. (Yes, these facts apply to the sample only, but as with all other surveys, they are assumed to be somewhat representative): Who you are. More than 90 percent of you are male. (We didn’t need a survey to confirm

that, actually.) More than 63 percent of you, at the time of survey completion, had visited the Oracle Technology Network Website at least once in the past month. No surprise there; historically our repeat-visit rate is high. What you do. This is interesting: although 81 percent of you, unsurprisingly, are involved in “database-related” projects, 37 percent are also involved in Java-related ones, and 25 percent are involved in hardware systems– related (servers and/or storage) ones. This data implies that a good chunk (although certainly not all) of the Sun Developer Network community has moved over to Oracle Technology Network. We like that. What you like. The most-popular services, by a wide margin, are free developer tools and software, discussion forums, newsletters, and sample code (in that order). In my opinion, too few of you have attended a Developer Day or Virtual Developer Day, and the existence of member discounts for books and other things is either underappreciated or not widely known. Also, interestingly, discussion forums are not as routinely used by hardware-oriented folks as other audiences. Whether this is a cultural artifact or lack of awareness is unclear. What you want more of. You’re itching for member discount pricing for events (presumably Oracle OpenWorld and JavaOne; Developer Day is free), contests and challenges (also surprising, since historically these have not been popular), and a rewards program to incentivize member interaction (yes, also surprising—the reward points system in our discussion forums, when introduced, was not warmly welcomed). How we rate. This is my personal favorite data point: 87 percent of you rate Oracle Technology Network as “good” or better. While it’s a nice result, we don’t take that

feedback to mean “don’t change a thing.” Rather, we’ll keep pushing. For example, our prebuilt developer virtual machines (based on Oracle VM VirtualBox) continue to roll out in different flavors and configurations. And Developer Day content is being completely rebaked, with new versions covering enterprise Java development, rich enterprise applications, and Java development with JDK 7. Express Delivery By the time you read this, Oracle Database, Express Edition 11g will be available for download as a production release. Yes, for the first time in years, the current release of Oracle Database is now available in an entry-level, small-footprint edition that is free to use, free to develop on, and free to distribute. Go get it! 

Justin Kestelyn (justin.kestelyn@oracle .com) is senior director, Oracle Technology Network and developer programs, as well as Oracle Technology Network editor in chief.

Web Locator Oracle Database, Express Edition 11g oracle.com/technetwork/database/ express-edition Prebuilt Developer Virtual Machines oracle.com/technetwork/community/ developer-vm Developer Day oracle.com/technetwork/community/ developer-day Member Discounts oracle.com/technetwork/community/join/ member-discounts Discussion Forums forums.oracle.com

oracle magazine July/August 2011

25

26

Architect By Bob Rhubart

Can You Relate?

Your success as a software architect depends on your relationships with stakeholders.

B

eing a software architect is all about relationships. No, forgetting your anniversary or not getting along with your mother-in-law won’t necessarily have any impact on your success as a software architect. But unless an architect’s relationships with project stakeholders are based on clear communication and a thorough understanding of expectations, the project is likely to end in a scene befitting a reality television show, with at least the possibility of flying furniture and crushed dreams. So if you want to succeed as a software architect, establishing a solid relationship with stakeholders and a crystal-clear understanding of their expectations must be job #1 on any project. The first step in that process is knowing whom you’re dealing with. Identifying stakeholders is essential. “You need to know who has the power to call an engagement a success or failure,” says Pat Shepherd, enterprise architect at Oracle. But there’s more to building relationships than memorizing names and roles—you need to get inside stakeholders’ heads. “Make sure you know the stakeholders and what’s driving them,” says Oracle ACE Director Ronald van Luttikhuizen, a solution architect and senior consultant at Approach Alliance, a Netherlands-based information and communications technology consultancy focusing on SOA and business intelligence. Randy Stafford, a software architect with the Oracle Coherence development group, agrees. “The first thing I do is make sure I understand the motivation for the project, because it usually translates directly to measurable success criteria,” Stafford says. Once you know what your stakeholders want, you need to make sure they understand what you bring to the table. “Stakeholders should know you and understand your added value and what you want

July/August 2011 Oracle.com/oraclemagazine

“The critical thing for an architect’s career is to build a good reputation.” —Pat Shepherd, Enterprise Architect, Oracle

to achieve with them in the project,” says van Luttikhuizen. Stakeholders need to smell what you’re cooking and like it, because the project has little chance of succeeding without their complete support. “This may sound a bit trivial,” adds van Luttikhuizen, “but I’ve seen too many ivorytower architects who just throw something out there. Without stakeholder support my architectural activities will not be followed and will just end up gathering dust.” In order to earn that support, Brian Jimerson, a technical architect at Avantia in Cleveland, Ohio, sets up a project kickoff meeting with his internal customers. “I want to ensure that project goals are understood, establish communication channels, identify stakeholders and roles, and discuss the high-level project schedule,” Jimerson says. He also holds similar kickoff meetings with development teams to discuss project goals, milestones, and team structure. Once you and your stakeholders are on the same page, it is time to impress everyone by waving your architecture wand. “In between the first steps and the project completion, a combination of creativity and engineering happens to identify and implement architectures that meet the goals,” says Stafford. But there has to be some substance behind the magic. As the project progresses, it’s important to maintain your relationship with stakeholders by demonstrating your progress. “I try to deliver something useful

as early as possible in the project,” says van Luttikhuizen. “Depending on the type of architecture assignment—whether my role is software architect, solution architect, or enterprise architect—this can be a quick advice document, a working piece of software, or a guideline to help stakeholders.” These project milestones are important not just as progress indicators but also as proof of the value of architecture. In the end, software architects are really only as good as their relationships with stakeholders. “Let’s face it—the critical thing for an architect’s career is to build a good reputation,” says Shepherd. Good reputations don’t spring from dysfunctional relationships with stakeholders. If you want to earn your stripes as a software architect, you can’t relate to stakeholders by flinging furniture at them across the conference room. You need to understand what they want. They need to understand what you can deliver. Bring those two elements together through clear communication and everybody wins. 

Bob Rhubart ([email protected]) is manager of the architect community on Oracle Technology Network, the host of the Oracle Technology Network ArchBeat podcast series, and the author of the ArchBeat blog (blogs.oracle.com/archbeat).

Next Steps LISTEN to ArchBeat podcasts feeds2.feedburner.com/otnarch2arch bit.ly/9X6j2T GET more architect information oracle.com/technetwork/architect

Up Close By Jeff Erickson

The Translator

User group speaker helps DBAs understand the broader technology stack.

F

Curtis Comeau/Getty Images

ace it, says Hans Forbrich: when you’re an Oracle DBA and your company invests in another Oracle product, there’s a good chance the company will throw that product to you too. For the past seven years, Forbrich, an Oracle consultant, Oracle ACE director, and sought-after speaker, has developed user group sessions to help DBAs deal with just this situation. “A database customer will invest in a new technology such as Oracle Enterprise Manager Grid Control, Oracle WebLogic Server, LDAP, or Java EE [Java Platform, Enterprise Edition], and then they will say to the DBA, ‘It says “Oracle” on it—you take it,’” says Forbrich. “That’s where I come in.” Drawing on his work as a DBA and longtime Oracle consultant, Forbrich delivers user group talks that quickly get DBAs up to speed. “I help DBAs understand these other technologies well enough that they can take an Oracle University class or attend a user group session and keep up,” says Forbrich. “Because a session leader will assume a minimum level of knowledge.” Even familiar terms can trip up DBAs as they aim to master a new technology. “DBAs talk about entity and Java programmers talk about entity, but they mean very different things,” Forbrich says. “Because my experience is so broad, I can translate between the tech language they speak and the one the new technology speaks,” he explains. “I can break it down and see the solution the way my audience sees it, from their experience set.” Sharing REal-World Experience Forbrich was part of Oracle Canada from 1996 to 2002, working as a consultant for the communications industry. As a consultant, he had to think on his feet and make sure a customer’s entire stack of Oracle technology worked as planned. Oracle University Canada teachers would often pull him into their

and “Introduction to Middle-Tier Security for DBAs.” Other popular sessions include “How to Upgrade from Your Old Application Environment to a New Fusion Middleware Environment” and—an old favorite—“How Do You Find It in the Documentation?”

Hans Forbrich, Oracle Ace Director, Oracle University Instructor, and Oracle User Group Speaker

classes to share his real-world experience. When he opened his own consulting firm, he began teaching courses as an Oracle partner. In 2010 Forbrich was honored with the Oracle University Instructor of the Year award. When Forbrich hears the same questions over and over again in class, he begins formulating a user group talk on the subject. “It is a great opportunity to get out and explain these things en masse,” says Forbrich. “Even such simple things as ‘How does the Oracle listener really work?’ or ‘Why doesn’t the split block function impact Oracle RMAN?’” Forbrich started out delivering explanations at local “Lunch and Learn” sessions for the Edmonton Oracle Users Group in Canada and was asked to present at the Puget Sound Oracle Users Group in Seattle. From there his list of user group appearances grew to include other regional groups, the annual COLLABORATE conference, and two Oracle Technology Network Latin America tours. Forbrich has delivered talks such as “Introduction to Oracle WebLogic Server for DBAs,” “Introduction to LDAP for DBAs,”

A User Group Tradition Forbrich strives to make his talks fit with what’s best about user group events. “You get straightforward presentations about what people have encountered with the technology, and those are very important,” says Forbrich. “More interesting to me, however, are those sessions that prepare people for the unusual—be it technical, legal, or cultural. I’ve seen people bring pictures and diagrams of a server room to show their fellow DBAs, because many DBAs have never seen one!” Forbrich, who backstops his technology business with a career as a semipro singer with the Edmonton Opera, is an expert at mastering different disciplines. It’s a skill that many DBAs have also had to master. “These days you can even have an Oracle DBA who gets put in charge of hardware or storage because the product says ‘Oracle’ on it,” he says. User groups, he adds, are a big part of how DBAs will meet the challenge. 

Jeff Erickson (jeffrey.x.erickson@ oracle.com) is a senior editor with Oracle Publishing.

Next Steps Read Forbrich’s blog hansforbrich.blogspot.com Watch the interview oracle.com/oramag/upclose

oracle magazine July/August 2011

27

28

Peer-To-Peer by Blair Campbell

On Grids and Groups

Three peers navigate trends in virtualization, database monitoring, and social media.

Bob Mycroft

Julian Dontcheff

Company: Capgemini, an IT services and business consultancy company Job title: Senior Oracle DBA, working on the team overseeing Capgemini’s U.K. Environment Agency account Location: Leeds, England Oracle credentials: Oracle Certified Professional (Oracle8i Database through Oracle Database 10g) and Oracle Certified Expert (Oracle Real Application Clusters 10g), with 15 years of experience using Oracle products

Company: Accenture, a management consulting, technology services, and outsourcing company Job title/description: Head of database management, overseeing a global team of DBAs Location: Helsinki, Finland Oracle credentials: Oracle Certified Master, Oracle Certified Professional (Oracle8i Database through Oracle Database 11g), and Oracle Certified Professional Developer (Oracle Forms 6, Oracle Forms 6i), with 23 years of experience using Oracle products

Which new features of Oracle Database are you currently finding most valuable? I’m playing with the Flashback Data Archive and Flashback Transaction Query features at the moment. With every new version of the database, we’ve been increasingly able to get the system back to an old point in time, or look at data at an old point in time, without the nastiness of doing a point-in-time recovery. Anything that saves us time is good. Any other products or features currently piquing your interest? Oracle VM. It’s really the only truly supported virtualization platform for Oracle products. The ability for us to potentially use Oracle technologies for the whole stack and then to virtualize the hardware itself is seriously attractive, as it means a single vendor for support. If you were going to the International Space Station for six months and could only take one Oracle reference book, what would it be? The Art and Science of Oracle Performance Tuning, by Christopher Lawson [Apress, 2003]. It’s quite an old book now, but I really like the way Lawson approaches the subject.

What’s your favorite tool or technique on the job? Oracle Grid Control—it’s the best tool Oracle has ever created. Oracle Grid Control is a must for DBAs who need to manage a lot of databases. It can drastically reduce database administration time and significantly improve a DBA’s productivity and efficiency. I most like the fact that you are able to monitor the complete database stack from one consolidated view. What advice do you have about how to get into database and application development? Attend an Oracle University [OU] class! It’s the best place to get Oracle knowledge. I’ve taken dozens of OU classes, always in the classroom, and I used them to prepare for my Oracle certification exams. OU is the place where information, study materials, and training are all provided from the very heart of Oracle. And I’ve always admired the enthusiasm and dedication of Oracle trainers. What technology has most changed your life? The TV set and the computer. Both equally—and almost all for the better.

Sheeri K. Cabral

Company: PalominoDB, a remote database management company Job title/description: Community manager and DBA, working in the field to handle all database tasks for PalominoDB clients Location: Boston, Massachusetts Oracle credentials: MySQL 4.0 Core certified and Oracle Certified Professional DBA (MySQL 5.0), with 10 years of experience using Oracle products

How did you get started in IT? In college I wanted to major in neuroscience. Math and computers were interesting, so I was going to minor in computer science just for fun. However, I ended up getting a D– in Introductory Neuroscience in the middle of my junior year, so with three semesters left to go I changed my major to computer science, got all As and Bs, and then went on to get a master’s degree in computer science as well. What’s your favorite tool or technique on the job? Lately I’m very enamored with mk-querydigest and EXPLAIN to do query reviews of the MySQL general logs. As for query reviews themselves, I find they are extremely helpful in finding queries to optimize before they become a problem in production. How are you using social media in your work? I’m on LinkedIn and Twitter. I try to keep Facebook personal, although I do participate in MySQL groups and events on Facebook. I have a blog, and I’m also the cohost of OurSQL: The MySQL Database Community Podcast [listen to the show at dev.mysql .com/podcasts.] 

Learn more about the Oracle ACE program at otn.oracle.com/community/oracle_ace. July/August 2011 Oracle.com/oraclemagazine

Print. Digital. Mobile.

Read Your Favorite Technology Magazines on Your Favorite Device.

3G

Download the most recent issue FREE on Apple iTunes. Or get your FREE print or digital subscription. Go to: oracle.com/us/corporate/publishing/subscribe

9:41 AM

tape

disc

flash

30

July/August 2011 Oracle.com/oraclemagazine

31

Storage from A to ZFS JAIME FERRAND/STAFF

How organizations optimize storage with tiered Sun flash, disk, and tape solutions from Oracle and enable unified storage with the Sun ZFS Storage Appliance Most business users don’t spend time thinking about where their files are stored. They log in to the applications they need, create documents, retrieve information, enter transactions, and periodically hit the Save button. They have been told that if they follow a few simple guidelines, all of their critical information will be saved, backed up, and duplicated behind the scenes. The vast storage infrastructure that automates these activities is largely hidden from view.

BY DAVID BAUM ORACLE MAGAZINE JULY/AUGUST 2011

32 snapshots

Novant Health Storage infrastructures grow as the demand for information grows, but strategic storage solutions, from tape libraries to flash and disk arrays to unified storage solutions, can simplify the essential tasks of storing and managing enterprise data. Two organizations, Novant Health and the Australian Bureau of Meteorology, have created businessready storage systems that exemplify best practices for information management as they efficiently deal with many terabytes of new data every day.

novanthealth.org Industry: Healthcare Employees: 25,000 Revenue: US$3.4 billion Oracle products: Sun Storage 6000 series arrays, Oracle’s Sun x86 Rackmount servers, Oracle Solaris Cluster, StorageTek T10000 tape drive, StorageTek SL8500 modular library system, Sun Storage Archive Manager, Oracle Database

As files are created by Novant’s radiographic equipment and enterprise information systems, one copy is immediately written to a high-speed FC Sun Storage 6000 series array from Oracle, and two copies are written to mirrored tape archives in two different datacenters. “We’re backing up data as we create it and archive it,” explains Dick. “Data is instantly available to the applications, and within 15 Australian Bureau of Meteorology bom.gov.au to 30 minutes, we have two backup copies in Industry: Government identical file systems in secure, remote locaEmployees: 1,700 tions. Thanks to this mirrored data storage Oracle products: Sun technical computing, Keeping Critical Data Online environment, requests can be virtually Sun Storage 6000 series arrays, Sun Storage Novant Health is a nonprofit integrated redirected from one file system to the other 6780 array, StorageTek SL8500 modular library system, StorageTek T10000 tape healthcare system that serves communiwithout the user or the application being drives, Sun Blade server, Sun x86 Rackmount ties and patients from Virginia to South aware of the difference.” servers, Sun Storage Archive Manager, Carolina. Like most of today’s healthcare Primary data resides in the FC storage Oracle Database providers, Novant needs to provide physienvironment for 15 to 30 days, sometimes cians and administrators with rapid access longer, depending on the service-level agreeto clinical and business information, comply with regulations ment determined for each application. After that it is relegated to regarding the security of patient information, and reduce the cost secondary storage on a Sun Storage 6000 series array. Sun Storage of capturing, storing, and delivering an immense volume of strucArchive Manager moves data automatically from tier to tier. “Once tured and unstructured data. you set up your storage policies, Sun Storage Archive Manager autoNovant’s storage odyssey began nearly 10 years ago, when the IT mates the movement of data from drive to drive and from primary to department created a digital archiving system that enabled more archival storage,” Dick says. than a dozen hospitals to capture, store, and retrieve images elecEach of Novant’s two datacenters runs identical archiving tronically. Led by Jim Grossman, manager of system services, and systems, powered by a pair of clustered Sun servers running Robert Dick, storage and UNIX administrator, Novant created a Sun Storage Archive Manager. The servers are set as an active/ radiographic imaging system that stores files for X-ray, ultrasound, passive pair so that if one of them goes down, it will fail over to the nuclear medicine, magnetic resonance imaging (MRI), and comother one. Behind the clustered servers is StorageTek Automated puted tomography (CT) scans. It wasn’t long before Grossman and Cartridge System Library Software that manages data storage and Dick realized that they could broaden this solution for other applicaretrieval in conjunction with StorageTek T10000 tape drives and tions that needed to store and archive patient data. They designed a StorageTek SL8500 modular library system divided between the an extensible storage infrastructure around Sun Storage Archive two datacenters. Manager, which now manages 1.4 petabytes of information and supThanks to a high degree of automation, Dick is able to maintain ports 38 clinical applications with differing data sets. the entire archival storage environment by himself (with part-time “Our storage system was architected around the principle that help from two associate storage managers when he is away). “We an application’s access to data is top priority,” explains Dick. “That bring in, on average, 2.5 terabytes of new data each day,” he says. means not only being able to safeguard the data in more than one “This system is very stable. In general, it’s relatively hands-off.” location, but also being able to ensure that it is accessible and sur“The system is designed to maximize security and mitigate risk,” vivable through any disaster, so authorized users can always access adds Grossman. “We’re not putting tapes on a truck and physically clinical data.” shipping them off to another site.” Sun Storage Archive Manager ingests 2 million files per week. Archive, Access, and Protect with Sun Storage Archive Manager “We’ve been running it for more than 10 years, and to date I have no record of ever having lost a single file,” says Dick. Oracle’s Sun Storage Archive Manager software provides data classification, centralized metadata management, policy-based data placement, protection, migration, long-term retention, and recovery to help organizations effectively manage and utilize data according to business requirements. The software enables users to reduce the cost of storing vast data repositories by providing a powerful, easily managed, cost-effective way to access, retain, and protect business data over its entire lifecycle. This selfprotecting file system offers continuous backup and fast recovery features to help enhance productivity and improve resource utilization.

July/August 2011 Oracle.com/oraclemagazine

Growing Data Volumes Novant’s storage solution improves patient care through faster, more-accurate diagnoses while eliminating the risk of lost data. It provides physicians with extremely fast access to patient imaging studies: a 10 MB file can be retrieved from disk and delivered to a physician or technician in less than three seconds.

33

“Oracle continues to enhance its storage solutions and renew our faith in the solution set we started with.” —Robert Dick, Storage and UNIX Administrator, Novant Health

Phil Saltonstall

Novant Health’s Robert Dick, storage and UNIX administrator (left), and Jim Grossman, manager of system services, rely on an extensible storage infrastructure. “Our storage system was architected around the principle that an application’s access to data is top priority,” says Dick.

According to Dick, the stress on an archiving system is not necessarily due to access performance or a growing volume of total data stored, but rather to the total number of files that the system has to manage. The higher the number of files, the more difficult it is to restore the data in the event of a disaster. That’s a pressing issue for Novant Health as it strives to eliminate hard-copy patient records and securely move more information online. “We have a new document imaging application for scanning patient records that is expected to add 450 million files into the

system over the next four years,” Dick notes, citing just one example. “Because of these stresses, we’re moving to solid-state disks, FC, and SATA [serial ATA] drives in a three-tier configuration.” Novant’s Sun servers will be replaced by newer gear—Novant is strongly considering a pair of Oracle’s SPARC T3-4 servers with 16 processors per core, tied to the existing FC and SATA arrays. “We’re also considering adding Oracle’s Sun Storage F5100 Flash Array,” Dick adds. “Our Oracle storage solution is so flexible that every time we have an archiving issue, we can solve it with the system that’s in

oracle magazine July/August 2011

34

place. Oracle continues to enhance its storage solutions and renew our faith in the solution set that we started with.” Dick believes that while solid-state disk technology is ideal for high-performance data retrieval, it’s not a suitable solution for every part of a storage environment. “If you’re not going to access data for a period of time, it makes much more sense to let it sit at rest in a tape library, where you can get it at a minute’s notice, as opposed to keeping all that data spinning and burning resources—generating heat and using power,” he points out. “Tape technology has not seen its swan song. It is still a very viable technology.” Benjamin Woo, program vice president for worldwide storage systems at IDC, agrees with this assessment. He says an effective storage strategy only places truly active data on expensive FC or solid-state disk drives and moves less-active data to more-costeffective storage media—often tape. “It doesn’t make sense to keep data spinning if you’re not going to be looking at it for a few months,” he says. “It should reside in less-expensive media. Our research shows that less than 20 percent of the data in today’s datacenters is actively used. Thus a very small percentage of information needs to reside on very expensive, very fast storage.” These multilevel storage systems are often called tiered storage environments because they assign different stages of data to different tiers of storage media, with the goal of preserving accessibility and reducing cost. “Once you have a tiered infrastructure, the key is to create policies to automate the movement of data so it requires minimal human intervention,” advises Scott Tracy, senior director of flash and disk products at Oracle. According to Tracy, most tiered storage strategies have three basic components. Primary storage stores new data from missioncritical applications and databases, with access time measured in seconds. Storage media typically includes ultrahigh performance flash or solid-state disk (SSD) drives and FC storage arrays. Secondary storage stores 30- to 90-day-old data that needs to be kept on hand for business continuity as well as fixed content and backup/recovery data. Access time is measured in minutes, and storage media includes unified storage solutions such as the Sun ZFS Storage Appliance and less-expensive SATA disks. Long-term or archival storage stores data older than 90 days, often for historic

“Oracle’s Sun offerings presented a unified, efficient, large-scale storage system that was flexible enough to grow.” —Robert Lovery, CIO, Australian Bureau of Meteorology reasons or for legal compliance. Access time is measured in hours, and storage media includes capacity disks and tape. “Use of tiered storage strategies typically results in around 75 percent reduction in costs compared to single tiers of disk storage,” notes Tracy. A Multitiered Solution for Scientific Data Tiered storage is what the Australian Bureau of Meteorology (BOM) uses to manage multiple petabytes of scientific data. As Australia’s national weather, climate, and water agency, BOM’s expertise and services assist Australians in dealing with the harsh realities of their natural environment, including droughts, floods, fires, storms, tsunamis, and tropical cyclones. Through regular forecasts, warnings, monitoring, and advice spanning the Australian region and Antarctic territory, BOM provides one of the most fundamental and widely used services of government. The agency also supplies specialized forecasting to the aviation industry, oil rigs, fire departments, police forces, and other emergency services, in addition to bulk data uploads for universities and scientific organizations. BOM’s data storage environment was put to the test earlier this year during Cyclone Yasi, the largest tropical storm to strike Australia since Europeans first settled there in the 18th century. With winds of 186 mph and waves on the coast reaching as high as 30 feet, Yasi wrecked more than 10,000 homes and businesses but did not cause any fatalities, thanks in part to BOM’s precise forecasts and reliable information-management infrastructure. BOM’s Oracle-based data storage environment gathers about 40 TB of data each week from 6,000 meteorological devices including warning buoys, oceanographic sensing gear, flood hydrology sta-

Unified Storage with the Sun ZFS Storage Appliance Oracle’s Sun ZFS Storage Appliance product line delivers unified storage for • Consolidation of multiple storage devices • Secondary storage for data protection (when multiple copies of data are required) • Test/development environments in which organizations are optimizing copies of primary data The product line enables the rapid deployment of new revenue-producing applications and lowers expenses by reducing storage complexity and its associated administrative costs. Sun ZFS Storage Appliances consolidate files and block I/O on a single high-capacity, highperformance storage appliance. This consolida-

July/August 2011 Oracle.com/oraclemagazine

tion supports 10 different protocols across three interconnects—Ethernet, FC, and InfiniBand. Sun ZFS Storage Appliances are built on the Oracle Solaris operating system, and they offer data services including deduplication, compression, replication, snapshots, and clones. Key features in the Sun ZFS Storage Appliance include storage analytics and hybrid storage pools. Storage analytics provide real-time comprehensive analysis of storage workloads that assist in capacity planning and provide detailed insight into storage networks. Hybrid storage pools combine DRAM, read-optimized flash devices, write-optimized flash devices, and native

SAS high-density, low-power spindles in a single transparently managed caching hierarchy. Data is automatically migrated from flash to disk based on access patterns and requires no human intervention. This “autotiering” keeps the most-used data in fast-access storage and the least-used data in inexpensive high-density, low-power spindles, reducing overall TCO. The Sun ZFS Storage Appliance product line combines cloud-ready software and hardware, designed to enable customers to start small, deploy applications faster and at a lower cost, and grow into a next-generation cloud-computing infrastructure.

35

series arrays with SATA drives. Tier 4 maintains archival data on a StorageTek SL8500 modular library system in conjunction with a StorageTek T10000 tape drive. A Sun Blade server and Sun x86 Rackmount servers from Oracle process the data under the direction of Supervisor Monitor Scheduler. The output is then passed to Sun Storage Archive Manager, which automatically moves data from tier to tier according to previously defined storage policies. According to Richard Oxbrow, storage manager at BOM, Sun Storage Archive Manager automates the entire information lifecycle, from fast disk to tape. It includes continuous backup and fast recovery features to enhance productivity and improve resource utilization. Thanks to a high degree of automation resident in the Oracle-based systems, two or three people maintain the entire storage infrastructure.

Australian Bureau of Meteorology

Robert Lovery, CIO at the Australian Bureau of Meteorology, says Oracle’s largescale storage offerings provide efficiency and the flexibility to grow with the organization’s rapidly evolving data processing workload.

tions, weather balloons, satellites, and aircraft. After being digitized, cleansed, transformed, and stored in an Oracle database, the data is processed by nearly 300 applications—some of which can predict the path and trajectory of cyclones days in advance. In 2008, Sun systems were selected to provide the computing and storage facilities for this massive operation, including service, support, and regular equipment upgrades over a five-year period. “We wanted a one-stop shop—one vendor that could provide a complete solution,” states Robert Lovery, chief information officer at the Bureau of Meteorology. “We didn’t want to have disparate issues with vendor finger-pointing. Oracle’s Sun offerings presented a unified, efficient, large-scale storage system that was flexible enough to grow with our rapidly evolving data processing workload.” Today BOM’s core meteorological atmospheric modeling prediction services are housed on a Sun technical computing solution and the output is archived on a large-scale data storage system. “About 10 percent of the data is corporate structured data, and 90 percent is unstructured scientific data such as satellite images,” says John de la Lande, the high-performance computing, storage, and facilities manager for BOM’s IT environment. The technical computing system shares resources among many nodes as it processes thousands of inputs from meteorology stations. It stores the output in four tiers. Tier 1, also called main storage, is mission-critical storage, with 99.999 percent availability. For this tier BOM uses a primary storage system from Sun. Tier 2, also called deep storage, utilizes Sun Storage 6000 series arrays with FC disk drives. Tier 3, also called bulk storage, utilizes Sun Storage 6000

Evolving to Unified and Flash Storage BOM’s storage infrastructure is constantly evolving as Lovery and his team add new hardware and software to improve speed and capacity. For example, the Sun Blade server features solid-state drives (SSDs) that deliver I/O performance of up to 400 hard disk drives. BOM recently purchased two of Oracle’s Sun ZFS Storage 7420 appliances, which use hybrid storage pools with flash-based caches to dramatically improve application response times. Oracle’s Tracy says these unified storage solutions are ideal for organizations that wish to consolidate and virtualize their storage infrastructure, as well as for companies adopting cloud computing. Oracle’s unified storage product line, anchored by the Sun ZFS Storage series, can transparently manage where data is placed in a multitiered storage environment with hybrid storage pool technology, holding copies of frequently used data in fast SSDs while storing less frequently used data in less-expensive, highcapacity SAS disks. (For more information on Oracle’s Sun ZFS Storage product line, see “Unified Storage with the Sun ZFS Storage Appliance” on page 34.) As BOM evolves its information systems, Lovery believes the move to engineered systems, such as storage appliances, is right for the organization. “We are extending our key vendor relationships to partnerships,” he says, “because we believe that our services are critical enough and important enough to merit this level of commitment.” 

David Baum ([email protected]) is a freelance business writer based in Santa Barbara, California.

Next Steps LEARN more about Sun storage and tape oracle.com/storage Sun ZFS Storage Appliances bit.ly/9WV4rH Sun Storage Archive Manager bit.ly/kNWeae

oracle magazine July/August 2011

RISE OF THE 36

PHH Corporation lays a foundation for shared services with Oracle

O

racle Exalogic Elastic Cloud is the world’s first engineered system specifically designed for implementing a private cloud computing environment that can host everything from smallscale departmental applications to large-scale enterprise resource planning (ERP) systems. Announced in September 2010 at Oracle OpenWorld, Oracle Exalogic Elastic Cloud is changing BY DAVID BAUM

E MACHINES 37

EXALOGIC Elastic Cloud and Oracle EXADATA Database Machine. datacenter strategy conversations as organizations plan to take advantage of the benefits of this one-of-a-kind middleware machine. Among the companies positioning themselves to transform their information strategy with Oracle Exalogic is PHH Corporation, based in Mount Laurel, New Jersey. PHH delivers business solutions

38

snapshot

PHH Corporation phh.com Industry: Fleet management and mortgage services Employees: 5,000 Revenue: US$2.4 billion Oracle products and services: Oracle Exalogic Elastic Cloud, Oracle Exadata Database Machine, Oracle WebLogic Server, Oracle Real Application Clusters, Oracle SOA Suite, Oracle Coherence, Oracle Business Process Management, Oracle Application Integration Architecture Foundation Packs, Oracle Tuxedo, Oracle Enterprise Content Management Suite, Oracle WebCenter, Oracle Data Integration Suite, Oracle Essbase, Oracle Hyperion forecasting and planning applications, Oracle Insight, Oracle Consulting Enterprise Architecture Services, Oracle Advanced Customer Services startup services

to its clients through two subsidiaries: PHH Mortgage and PHH Arval. PHH Mortgage provides private-label mortgage solutions to clients nationwide including financial institutions, real estate companies, credit unions, corporations, and government agencies. PHH Arval provides fleet management services for corporate clients and government agencies throughout the United States and Canada. Each PHH subsidiary has spent years innovating and expanding its information systems, resulting in separate and diverse IT infrastructures. “Imagine two full platforms with completely distinct solutions up and down the stack,” says Chris Brewer, vice president of platform technologies at PHH. “Both of our divisions have grown organically through acquisitions, resulting in silos of individual solutions.” To consolidate these disparate technology stacks and lay a foundation for new applications, PHH is implementing a shared services environment called the Common Technology Platform—

July/August 2011 Oracle.com/oraclemagazine

Foundation for Scalability and Performance Consolidation on Oracle Exalogic is possible because of the optimizations in the product’s engineered system design. According to Adam Messinger, vice president of development in the Oracle Fusion Middleware group, while Oracle Exalogic Elastic Cloud is optimized for enterprise Java, Oracle Fusion Middleware, and Oracle Fusion Applications, it also runs third-party and custom applications. In addition, each Oracle Exalogic configuration is balanced for compute-intensive workloads. “These Oracle Exalogic engineered systems contain hot-swappable compute nodes; a clustered, highperformance disk storage subsystem; and an InfiniBand switching fabric to connect the internal components as well as to externally connect additional Oracle Exalogic or Oracle Exadata Database Machine racks via high-bandwidth interfaces,” Messinger notes. “It’s all engineered as a cohesive system.” Like Oracle Exadata, Oracle Exalogic is built from industry-

Catherine Gibbons

To consolidate disparate technology stacks and lay a foundation for new applications, PHH is implementing a shared services environment based on Oracle Exalogic Elastic Cloud and Oracle Exadata Database Machine, says Chris Brewer, vice president of platform technologies at PHH.

anchored by a half-rack Oracle Exalogic Elastic Cloud and a half-rack Oracle Exadata Database Machine, which provide extreme performance for both data warehousing and online transaction processing (OLTP) applications. “Having our databases, infrastructure, and applications in the same place will greatly simplify our datacenter topology and hardware footprint,” Brewer explains. “This consolidated Oracle platform will also allow for standards-based implementation and integration between different application tiers.”

39

“Having our databases, infrastructure, and applications in the same place will greatly simplify our datacenter topology.” —Chris Brewer, Vice President of Platform Technologies, PHH

standard server and storage building blocks. In addition, Oracle software technology enables Oracle Exalogic applications to share data efficiently and transparently— extending Oracle’s application grid technology, which enables multiple application server instances to share a dynamically allocated pool of resources. “Oracle customers used to assemble application grids and database grids themselves,” says PHH’s Brewer. “Now, with the Oracle Exalogic and Oracle Exadata systems, Oracle has pre-engineered these solutions in advance, with all of the components integrated, tuned, and tested at the factory.” Organizations can start with a quarter rack, grow to a half rack or full rack, and connect as many as eight full racks of Oracle Exalogic hardware (or any combination of Oracle Exalogic and Oracle Exadata configurations) without any external switches. This extensible architecture provides nearly unlimited horizontal scalability and eliminates the need for an ongoing purchase, configure, test, and deploy cycle for unique configurations of hardware and software. “All of the hardware and software comes from Oracle, so we can make sure that there are no single points of failure and that all of the various scenarios are well tested,” Oracle’s Messinger points out. Architecture Discussions In February 2010, the Oracle Consulting Enterprise Architecture team led several discussions with PHH about how to best realize the company’s goal of a Common Technology Platform. “Oracle helped us develop a reference architecture that became a blueprint for our Common Technology Platform,” says Nathan Smith, director of enterprise architecture and chief architect at PHH. “Oracle took the time to understand our business and technological challenges. They helped us develop

oracle magazine July/August 2011

40

Extreme Startup Companies looking to rapidly and successfully deploy an Oracle Exalogic solution can take advantage of Oracle Exalogic Start-Up Pack, a suite of services and expertise that maximizes the value of an Oracle Exalogic technology investment. The Oracle Exalogic Start-Up Pack includes five core services from Oracle Consulting and Oracle Advanced Customer Services, including • Oracle Exalogic Start-Up Advisory Service • Oracle Exalogic Production Support Readiness • Oracle Exalogic Installation Service • Oracle Exalogic Configuration Service • Oracle Exalogic Quarterly Patch Deployment Service For more information about the Oracle Exalogic Start-Up Pack, contact your local Oracle Consulting or Oracle Advanced Customer Services representative at 1.800.ORACLE1, or via e-mail at [email protected]. To read more about the Oracle Exalogic Start-Up Pack, see bit.ly/iKmP5N.

a roadmap for achieving our transformational goals.” “We couldn’t have made as clear a business case without the Oracle Consulting teams, including the Oracle Enterprise Architecture development program, Oracle Advanced Customer Services, and the Oracle Insight program,” adds Brewer. “They determined what we had and how to reframe it in our new reference architecture.” This reference architecture now serves as a blueprint for developing individual business solutions and deploying new applications, whether packaged or built from scratch. According to Smith, PHH used to be a big advocate of acquiring best-of-breed software from many different vendors, which led to its current predicament. “We always sought to find the best product for each set of business needs, which meant we would have to spend a substantial amount of time and effort to integrate those products into our IT environment,” Smith says. “When we laid out our vision for a Common Technology Platform, we knew that we wanted to strategically align ourselves with a technology provider that could offer us the entire technology stack. Rather than manually piecing together our Common Technology Platform, and then dealing with all of the vendor finger-pointing when something goes wrong, we decided to standardize on Oracle Exalogic, Oracle Exadata, and the entire Oracle technology stack. Oracle leads the industry in multiple categories, so it was a logical move to forge an Oracle partnership.” Rolling Out the Apps PHH took possession of its Oracle Exalogic and Oracle Exadata engineered systems in April 2011 and is currently executing its plan to create a shared services environment. Ultimately, PHH’s new Oracle Exalogic system will host the entire Oracle Fusion Middleware stack including Oracle WebLogic Server, Oracle SOA Suite, Oracle Coherence, Oracle Business Process Management, Oracle Application Integration Architecture Foundation Packs, Oracle Tuxedo, Oracle Enterprise Content Management Suite, Oracle WebCenter, and Oracle Data Integration Suite. PHH also plans to run OLAP analytics against its Oracle Essbase database and to deploy Oracle Hyperion forecasting and planning applications in this new environment. In addition, PHH is migrating data from Sybase and IBM DB2

July/August 2011 Oracle.com/oraclemagazine

databases and standardizing on Oracle Database 11g on Oracle Exadata. Brewer believes that having one consolidated database will improve operational efficiency and deliver more-accurate information to the various functional areas, not only for reporting and forecasting but also for predictive analysis. “We see a great opportunity to use our customer data for cross-selling and up-selling between the two lines of business,” he notes. Although the Oracle Exalogic Elastic Cloud implementation is still in its early stages, Brewer believes that Oracle Exalogic will enable PHH to deliver a more cohesive experience for customers by eliminating demand for high-end desktop systems and offering selfservice capabilities via thin clients, mobile devices, and standardsbased Web portals. For example, for PHH Mortgage customers, the company handles daily reporting on the status of each loan and manages the steps in the loan review and approval process. Brewer’s team is developing mobile applications in conjunction with Oracle WebCenter that will let clients access this information through smartphones and tablets. “Financial advisors often interact with their clients after hours or in the field, and it will be helpful to be able to grab this information from any location,” he notes. For PHH Arval customers, the company is developing analytic applications to predict when it is time for vehicle maintenance and to automatically send alerts to each driver via e-mail or instant messaging. Customers can use Oracle WebCenter Real-Time Collaboration to open channels of communication through instant messaging clients, mobile devices, and standard Web browsers. “One of the tenets of our technology strategy is not only to combine our two divisions but to create a consistent look and feel for users, both internal and external,” Brewer concludes. “Standardizing on Oracle Exalogic and many other Oracle products will enable us to build consistency within the middleware tier, giving us a common look and feel for reporting, ad hoc analysis, scorecards, and many other functions.” 

David Baum ([email protected]) is a freelance business writer based in Santa Barbara, California.

Next Steps LEARN more about Oracle Exalogic Elastic Cloud oracle.com/us/products/middleware/exalogic Oracle Exadata Database Machine oracle.com/us/products/database/exadata Oracle Insight oracle.com/us/corporate/insight Oracle Consulting Enterprise Architecture Services oracle.com/us/products/consulting/enterprise-architecture-services VIEW 3-D demos Oracle Exalogic Elastic Cloud bit.ly/leBTiU Oracle Exadata Database Machine bit.ly/kgOHuQ

Specialized Oracle PartnerNetwork 50+ Product Specializations Recognized by Oracle Preferred by Customers

Differentiate Yourself

partner.oracle.com or call 1.800.323.SELL

Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Great minds don’t think alike. To continue to lead the way in technology and science, we must develop the next generation of innovators. Steps we take today can empower young people to develop and test their own ideas and approach science, technology, engineering, and math not just as fields of study in school but as ways of understanding our world. Join the Lawrence Hall of Science in inspiring and preparing kids from all backgrounds to do science. lawrencehallofscience.org/brilliant

Database Developer

BUSINESS INTELLIGENCE BY MARK RITTMAN

ORACLE BUSINESS INTELLIGENCE ENTERPRISE EDITION 11g

Upgrade to Oracle Business Intelligence Enterprise Edition 11g

Take advantage of the latest OLAP analysis, mapping, KPI, and scorecard features.

O

I-HUA CHEN

racle Business Intelligence Enterprise Edition 11g introduces several new features that will be of interest to organizations using the previous (10g) release of the product. An overhauled and simplified user interface, integration of the Oracle Fusion Middleware MapViewer feature, key performance indicators (KPIs) and scorecards, and the new Action Framework are all new areas of functionality in the new release that I have covered in this column over the past few issues of Oracle Magazine. So you’ve seen the new features, and now you want to upgrade to Oracle Business Intelligence Enterprise Edition 11g—how do you go about it? Luckily, Oracle has thought about the upgrade process right from the development of Oracle Business Intelligence Enterprise Edition 11g, by including in the release the easy-to-use Oracle Fusion Middleware Upgrade Assistant, which makes upgrading your Oracle Business Intelligence Repository, Oracle Business Intelligence Presentation Catalog, and other metadata a simple process. This column details how to upgrade the Sample Sales repository and Web catalog metadata, designed for Oracle Business Intelligence Enterprise Edition 10g, to work with Oracle Business Intelligence Enterprise Edition 11g. You can download this sample data set from Oracle Technology Network at bit.ly/m3Kuw. PLANNING FOR THE UPGRADE As with any other upgrade, you first need to consider the hardware, software, networking, and operating system environments you are upgrading from and to. Your first resources for these considerations should be the

product documentation, in particular Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence 11g Release 1 (11.1.1), which describes the upgrade process in detail, including information about which components are automatically upgraded, and Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence 11g Release 1 (11.1.1), which details the hardware, operating system, and networking requirements for the Oracle Business Intelligence Enterprise Edition 11g release. After you have reviewed the documentation, install the Oracle Business Intelligence Enterprise Edition 11g software. Before you start the install, however, run the Repository Creation Utility to create the supporting database schema for Oracle Business Intelligence

Enterprise Edition 11g, which brings together all the separate tables and schemas used in earlier releases. After you have run the Repository Creation Utility, run the Oracle Business Intelligence Enterprise Edition 11g installer, which copies the required product files onto your server and then configures Oracle WebLogic Server to create your Oracle Business Intelligence domain. UPGRADING YOUR METADATA With Oracle Business Intelligence Enterprise Edition 11g installed, you can now use the Oracle Fusion Middleware Upgrade Assistant to upgrade—and upload—your Oracle Business Intelligence Enterprise Edition 10g metadata to the new environment. The Oracle Fusion Middleware Upgrade Assistant, part

Figure 1: The filled-in Specify Source Details dialog box of the Oracle Fusion Middleware Upgrade Assistant

ORACLE MAGAZINE JULY/AUGUST 2011

43

44

Business Intelligence

of Oracle Fusion Middleware 11g, is located in your [middleware_home]/Oracle_BI1/ bin/directory and is named either ua.bat (for Microsoft Windows installations) or ua.sh (for UNIX or Linux installations). The Oracle Fusion Middleware Upgrade Assistant takes you stepby-step through the process and upgrades the following business intelligence metadata: • Your repository (RPD file) and Web catalog, including users and groups in the repository • The Oracle Business Intelligence scheduler schema • The Oracle Business Intelligence Publisher report repository • The Oracle Business Intelligence Publisher scheduler schema The Oracle Fusion Middleware Upgrade Assistant produces an upgraded, working business intelligence system at the end of this process. Because the Oracle Fusion Middleware Upgrade Assistant places the upgraded repository and Web catalog online and copies any users within your Oracle Business Intelligence Enterprise Edition 10g repository into the Oracle WebLogic Server LDAP server as part of the process, you should test the process in your development and test environment before running it in production. In addition, the system you upgrade must not be clustered, so you should revert to a single node without a cluster controller configured before you begin the upgrade process. Using the Oracle Fusion Middleware Upgrade Assistant In this example, you will upgrade the repository and Web catalog objects provided as part of the Oracle Business Intelligence Enterprise Edition 10g Sample Sales data set. Download bit.ly/m3Kuw, and unzip the sample data to a directory on the server on which Oracle Business Intelligence Enterprise Edition 11g is installed. First, check that your Oracle WebLogic Admin Server and Managed Server are running by using your Web browser to open the Oracle WebLogic Administration Console Web page at localhost:7001/em, and if the page is not available, start these components by using the supplied startup scripts. Now, in your file system, navigate to the directory where the Upgrade Assistant is installed, start the utility, and then follow these steps:

July/August 2011 Oracle.com/oraclemagazine

Figure 2: An upgraded Oracle Business Intelligence dashboard

1. With the Oracle Fusion Middleware Upgrade Assistant displayed, review the Welcome message and click Next to continue. 2. On the Specify Operation screen, select the Upgrade Oracle BI RPD and Presentation Catalog option and click Next to proceed. 3. On the Specify Source Options screen, check the Upgrade Repository (RPD) checkbox and click Browse to navigate to the location in the file system where you saved the Sample Sales repository file (samplesales.rpd). 4. After you have selected the repository file, enter the Administrator User Name and Administrator Password for the existing repository file. For the Sample Sales repository, this is typically Administrator/Administrator. 5. Oracle Business Intelligence Enterprise Edition 11g repositories no longer have hard-coded administrator accounts within them; instead, you will use a repository password to secure access to them. In the Enter a Password which will be used to secure the upgraded RPD section, enter the password (such as welcome1) twice to provide an initial password for the new repository file. 6. To upgrade the Sample Sales Web catalog, check the Upgrade Catalog checkbox and click Browse—next to the Catalog Directory and Catalog Deliveries Directory text boxes—to select the

Sample Sales Web catalog and Deliveries catalog, respectively, as shown in Figure 1. 7. Click Next to proceed to the Specify WebLogic Server screen. Enter a number (typically 7001) for Port, and fill in the Username and Password fields of the Oracle WebLogic Server administration account. Click Next to proceed. 8. On the Examining Components screen, check the Succeeded status and click Next and then Upgrade to start the upgrade process. 9. The upgrade process typically takes less than a minute for the Sample Sales repository and Web catalog but can take longer for more-complex sets of metadata. Once the process is complete, check that the upgrade status is Succeeded, review any warning or error messages, and click Next and then Close to exit the Oracle Fusion Middleware Upgrade Assistant. If you are upgrading your own business intelligence metadata, you can use other options provided by the Oracle Fusion Middleware Upgrade Assistant to upgrade your scheduler metadata, Oracle Business Intelligence Publisher repository, and Oracle Business Intelligence Publisher scheduler. For now, though, let’s take a look at what’s been upgraded and any further postupgrade steps you need to perform to complete the process. Accessing the Upgraded Metadata If you are upgrading your own business intelligence system by using the Oracle Fusion

45

Middleware Upgrade Assistant, you need to ensure that the TNSNAMES file within the [middleware_home]\Oracle_BI1\network\ admin directory on your new server includes entries that correspond with the database connect strings referenced in the upgraded repository. For now, though, let’s first use the Oracle Business Intelligence Administration Tool to view the upgraded repository. 1. From your Microsoft Windows Start menu, select Start -> Programs -> Oracle Business Intelligence -> BI Administration. 2. When the Oracle Business Intelligence Administration Tool opens, select File -> Open -> Online and enter the following details when prompted: Repository Password: User: Password: With the information entered, click Open to open the repository online. You will then see your upgraded business intelligence repository within the Oracle Business Intelligence Administration Tool. 3. The Sample Sales repository and Web catalog you upgraded came with a sample set of data in XML format, for display in dashboards. To make this data available for queries, locate the Sample_ Sales_V1.3_Dataset.zip archive file from the Sample Sales download detailed earlier in this article and unzip it to the following location (you will need to create the directories shown in boldface type). %MIDDLEWARE_HOME\instances\ instance1\bifoundation\ OracleBIServerComponent\ coreapplication_obis1\server\ Sample Then, using the Oracle Business Intelligence Administration Tool, select Manage -> Variables and locate the BIEE_HOME static repository variable. Double-click it to show the Static Repository Variable properties page, and

Figure 3: An Oracle Business Intelligence pivot table

update the variable initializer to reflect the path detailed above, without the additional (new) directories. For example 'C:\Middleware\instances\ instance1\bifoundation\ OracleBIServerComponent\ coreapplication_obis1' Select File -> Save to save your repository, and run the consistency checker. 4. Finally, you need to restart Oracle Business Intelligence Server. To do this, open your Web browser and navigate to Oracle Enterprise Manager Fusion Middleware Control, using the URL http://localhost:7001/em (adjust as required for your installation). When prompted, enter the username and password of the Oracle WebLogic Server administration user, and then select Business Intelligence -> coreapplication from the menu. When the core application screen is displayed, select Capacity Management -> Availability from the tabs and subtabs, click BI Servers, and click Restart Selected. You can now access your upgraded Web catalog. Using your Web browser, navigate to the Oracle Business Intelligence home page (such as http://localhost:9704/analytics), enter the Oracle WebLogic Server administra-

tion username and password, and then select Dashboards -> Sample Sales -> 02 History and Benching. You will see one of the Sample Sales dashboards running within an Oracle Business Intelligence Enterprise Edition 11g environment, as shown in Figure 2. After the Upgrade Although the Oracle Fusion Middleware Upgrade Assistant upgrades your metadata, any configuration changes you applied to your Oracle Business Intelligence Enterprise Edition 10g environment that you want to reapply to your Oracle Business Intelligence Enterprise Edition 11g environment will need to be applied manually with Oracle Enterprise Manager Fusion Middleware Control. For example, you may want to enable or disable query caching or add more instances of Oracle Business Intelligence Server or other system components to scale up or scale out your installation. Also, some configuration changes require you to manually edit the Oracle Business Intelligence Server NQSConfig.INI and Oracle Business Intelligence Presentation Server InstanceConfig.XML configuration files. Check Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence 11g Release 1 (11.1.1) for those business intelligence components that are not upgraded by the Oracle Fusion Middleware Upgrade Assistant, including CSS and other user inter-

oracle magazine July/August 2011

46

BUSINESS INTELLIGENCE

face configuration files. Although Oracle Business Intelligence Enterprise Edition 11g adopts the Oracle Fusion Middleware security model, your existing initialization blocks, LDAP directory connections, and Web groups will still work with Oracle Business Intelligence Enterprise Edition 11g. You may, however, want to consider upgrading these to use the Oracle Fusion Middleware security framework, to take advantage of the authentication, authorization, and directory integration features provided by Oracle WebLogic Server. Similarly, you may now want to revisit your analyses (the new term for requests), dashboards, and other business intelligence objects to take advantage of the new features and capabilities provided by the Oracle Business Intelligence DOWNLOAD Oracle Business Intelligence Enterprise Edition 11g bit.ly/j0rHJu

Enterprise Edition 11g release, such as the pivot table, which uses the new hierarchical columns feature shown in Figure 3. SUMMARY Upgrading an Oracle Business Intelligence Enterprise Edition 10g system to Oracle Business Intelligence Enterprise Edition 11g is a straightforward process when you use the Oracle Fusion Middleware Upgrade Assistant. It automatically upgrades your repository, Web catalog, Oracle Business Intelligence Publisher reports, and scheduler schemas. Once the upgrade is complete, you can take advantage of new Oracle Business Intelligence Enterprise Edition 11g features, including online analytical processing (OLAP) analysis capabilities, mapping, KPIs, scorecards, and the new systems management framework built around Oracle WebLogic Server and Oracle Enterprise Manager. t

Mark Rittman is an Oracle ACE director, a cofounder of Rittman Mead, and an executive board member of the Oracle Development Tools User Group. He maintains a blog at rittmanmead.com. NEXT STEPS READ more about Oracle Business Intelligence Enterprise Edition 11g Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence 11g Release 1 (11.1.1) Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence 11g Release 1 (11.1.1) bit.ly/ihRSZR DOWNLOAD sample data for this column bit.ly/m3Kuw

SAVE TIME, MONEY

& SOMETHING MORE When it comes to your data center, General Microsystems can help you improve efficiency, performance and the environment. With the power of Oracle® applications, GMI can help: » Increase the speed of your multi-threaded applications with Oracle’s SPARC T3 servers. » Reduce space and power consumption, while cutting transaction times in half with Oracle’s Sun Storage F5100 Flash Arrays.

GMI is an experienced, dependable partner, dedicated to providing superior service and intelligent IT solutions that maximize productivity and minimize impact on the environment.

» Consolidate and simplify management of multiple data centers with Oracle’s Sun ZFS Storage Appliance.

Find out how GMI can help you enhance productivity and reduce your bottom line. Visit

www.gmispecialized.com © 2011 General Microsystems. All rights reserved. All company logos and product names mentioned herein may be trademarks and/or registered trademarks of their respective companies. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc.

JULY/AUGUST 2011 ORACLE.COM/ORACLEMAGAZINE

Java Developer

Oracle ADF By Frank Nimphius

Oracle JDeveloper and Oracle Application Development Framework

Add Advanced List Interaction

Use model-driven LOVs in Oracle ADF to implement smart lists with autosuggest behavior.

U

sing lists of values (LOVs) in Oracle Application Development Framework (Oracle ADF) is a great way to prevent end users from entering invalid data. With dependent LOVs, you can further enhance user efficiency by filtering lists on external conditions. This column will show you how the LOV features in Oracle ADF help you build rich, intelligent list and search capabilities without requiring full-blown Java coding. To begin, go to bit.ly/gLhag3, which will automatically download the starter workspace in a zip file called o41adf-359201.zip. Note that it contains two folders, one containing a starter workspace and the other a completed version. Ensure that you’re using the studio edition of the Oracle JDeveloper 11g 11.1.1.4 (or later) release, available as a free download on the Oracle Technology Network at oracle.com/technetwork/ developer-tools/jdev/downloads. The application uses the HR schema that’s available by default in Oracle Database.

Sample Application Overview The biggest challenge in developing LOVs in large enterprise applications is to maintain functional and user interface consistency across pages. Model-driven LOVs help you meet this challenge by enabling you to define an LOV in a single place; any subsequent modifications to the list propagate to all instances of the list. In this example, you’ll see how to use Oracle JDeveloper to create dependent, modeldriven LOVs that provide smart lists— filtered lists with default selection values and autosuggest behavior. Launch Oracle JDeveloper, and browse to the directory where you unzipped the starter workspace. Open

the AdfListOfValues.jws workspace in the AdfListOfValues folder. Before proceeding, adjust the properties of the connection named hrconn in the Application Resources zone of the Application Navigator until you can successfully test a connection to the HR database schema. The sample workspace contains two projects: Model and ViewController. The Model project contains four view objects: DepartmentsView, JobsView, ManagersView, and EmployeesView. You’ll start by defining LOVs for attributes of the

EmployeesView object. The other three view objects provide the data for the lists. The ViewController project contains a single page, SamplePage.jspx, that you’ll use to test the model-driven LOVs in a Web environment. Building LOVs for the EmployeesView Object Double-click the EmployeesView object in the Model project’s adf.oramag.sample .model.vo package to open it in the object editor. Select the Attributes category, and

Figure 1: Adding a view accessor

oracle magazine July/August 2011

47

48

Oracle ADF

then click DepartmentId in the list of attributes. Click the green plus sign (+) icon next to the List of Values: DepartmentId label to open the Create List of Values dialog box. Click the + sign next to the List Data Source field, and select DepartmentsView from the Available View Objects list. Click the right-pointing shuttle button to move the selected view object to the View Accessors list. Figure 1 shows the updated View Accessors list. (The existing JobsView1 view accessor is from an LOV that is predefined in the sample workspace.) Click OK. From the List Attribute list, choose DepartmentId. That selection automatically completes the List Return Values section so that the DepartmentId value is copied to the EmployeesView object’s DepartmentId. On the UI Hints tab, perform the following steps to configure the display for the LOV and the component that renders it: 1. For Default List Type, select Combo Box with List of Values. 2. Under Display Attributes, shuttle DepartmentName, DepartmentId, and LocationId (in that order) from the Available list to the Selected list. 3. For Show in Combo Box, select First. 4. Check the Query List Automatically checkbox. 5. Check the Filter Combo Box Using option to create a smart list that filters the list of departments to include only the departments that have employees. The filter is based on the DepartmentsWithEmployees view criteria defined for the DepartmentsView view object. Click OK to save the configuration and return to the ViewEmployees editor. Figure 2 shows how the LOV you’ve just defined for the DepartmentId attribute will render at runtime. If you want to search in all departments, click Search to launch the Search and Select dialog box. Whenever the DepartmentId attribute is added to any page in the application, the UI will render consistently as shown in Figure 2. The ManagerId LOV you’ll create next is dependent on the DepartmentId value. To synchronize the two attributes, the DepartmentId must issue a partial submit

July/August 2011 Oracle.com/oraclemagazine

upon value change. With the DepartmentId attribute selected, open the Property Inspector from the Oracle JDeveloper main menu (View -> Property Inspector). In the Property Inspector, expand the UI Hints category and set the Auto Submit property to true. Save the project. Building the Dependent LOV for the ManagerId Attribute The ManagerId attribute should also render as an LOV component at runtime. In the EmployeesView view object editor, select the ManagerId attribute and create an LOV definition as before. In the Create List of Values dialog box, create a new view accessor for List Data Source by clicking the green + and shuttling ManagersView from the Available View Objects list to the View Accessors list. Now assign a bind variable value that passes the DepartmentId attribute value to a view criteria that exists on the ManagersView object. Click Edit next to the View Accessors label. In the Edit View Accessor dialog box, enter DepartmentId as the value for the bind parameter, as shown in Figure 3. You’ll allow users to query all employees who are managers via a Search and Select dialog box. You’ll also provide a smart list to help them find the manager for the current department as referenced in the DepartmentId attribute. The smart list is

Figure 3: Adding a bind parameter value

Figure 2: LOV for DepartmentID as displayed at runtime

queried by the value provided for the bind variable. Click OK, and click OK again to close the Edit View Accessor dialog box. Back in the Create List of Values dialog box, select EmployeeId as the value of the List Attribute field. This selection ensures that at runtime the EmployeeId of the selected manager will be assigned as the current employee’s ManagerId value. Click the UI Hints tab to configure the appearance of the LOV component: 1. Choose Combo Box with List of Values for Default List Type. 2. For Display Attributes, shuttle FirstName, LastName, Email, and DepartmentId (in that order) from the Available list to the Selected list. 3. For Show in Combo Box, select First and 2 (indicating that the first two attributes

49

Figure 4: Querying all managers

Figure 5: Autosuggest behavior

will appear in the smart list). 4. Check the Query List Automatically checkbox. 5. Check the Filter Combo Box Using option, which automatically selects the ManagerInDepartment view criteria defined for ManagersView. Click OK to close the dialog box. At runtime, the dependent smart list shows the manager for the selected DepartmentId. To query all managers, users click the Search link, as shown in Figure 4. Congratulations—you’ve created an LOV with a dependent smart list. Building the Oracle ADF Faces Form for Testing Now you’ll build a Web page for testing the application. In the Application Navigator, expand the ViewController project; right-click the SamplePage.jspx file, in the Web Content folder; and choose Open. Open the Data Controls accordion panel in the Application Navigator, and expand AppModuleDataControl. Drag the

allEmployees collection from the Data Controls panel, and drop it as Form -> ADF Form onto the visual page editor. In the Edit Form Fields dialog box, check the checkboxes to create navigation controls and to add a submit button; click OK. In the Application Navigator, right-click the SamplePage.jspx file and choose Run to test the application. Note that at runtime the dependent ManagerId value refreshes if you select a different DepartmentId value. Click Search in the list to show the Search and Select dialog box containing the full ManagerID list. Improving the JobId Field with Autosuggest Behavior The LOV definition for the EmployeesView object’s JobId attribute, which is precreated in the sample, references the JobsView view object. Instead of using Combo Box with List of Values for Default List Type, it uses Input Text with List of Values. So far, the JobId field has LOV behavior only when the user clicks the magnifying glass icon at runtime. Now you’ll add autosuggest behavior to the JobId attribute. Return to Oracle JDeveloper with SamplePage.jspx open in the visual page editor. Open the Component Palette by choosing View -> Component Palette from the main menu. In the Component Palette, expand the Operations accordion, find the Auto Suggest Behavior component, and drag it onto the page’s JobId field. Open the Property Inspector, and enter #{bindings .JobId.suggestedItems} as the value of the Auto Suggest Behavior component’s SuggestedItems property. At runtime, the bindings portion of the expression statement accesses the Oracle ADF binding container. The JobId portion is a list binding that is created for the modeldriven LOV when the JobId attribute is added to the page. One of the properties on the list binding is suggestedItems, which returns an LOV that matches the user input in the JobId field. Still working with the Auto Suggest Behavior component, enter 10 as the value of the MaxSuggestedItems property to restrict the number of display items. Right-click the SamplePage.jspx file, and

choose Run from the context menu to test the application. In the form that appears in your browser, clear the JobId field and type an uppercase A to produce the autosuggested list of job IDs that begin with A. Try it again by entering an uppercase C. Your results should look similar to Figure 5. If you have not successfully completed this exercise, you can examine the finished application by opening the AdfListOfValuesCompleted.jws workspace in the AdfListOfValues-Completed folder, and see it in action by running SamplePage.jspx. Conclusion Using model-driven LOVs in Oracle ADF not only is the most consistent and manageable approach for building LOVs but also spares you from having to code your own solution. To learn more about model-driven LOVs, see section 25.3.2, “How to Create a Model-Driven List,” in Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 1 (11.1.1.4.0). 

Frank Nimphius is a senior principal product manager for Oracle JDeveloper and Oracle Application Development Framework. He coauthored Oracle Fusion Developer Guide: Building Rich Internet Applications with Oracle ADF Business Components and Oracle ADF Faces (McGraw-Hill, 2010).

Next Steps READ more about Oracle ADF bit.ly/hUMKrB Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 1 (11.1.1.4.0) bit.ly/gnQf1I Oracle Fusion Developer Guide: Building Rich Internet Applications with Oracle ADF Business Components and Oracle ADF Faces (McGraw-Hill, 2010) amzn.to/e7SJNw Oracle ADF Insider bit.ly/f71x3w Oracle ADF Code Corner bit.ly/hP3CBU DISCUSS Oracle JDeveloper and Oracle ADF Oracle JDeveloper and Oracle ADF Oracle Technology Network Forum bit.ly/fPiamS

oracle magazine July/August 2011

Database Application Developer | PL/SQL Oracle Database

Controlling the Flow of Execution

Part 2 in a series of articles on understanding and using PL/SQL

T

o help newcomers to PL/SQL make the most of this language, Oracle Magazine has asked me to write a series of articles for PL/SQL beginners, of which this is the second. If you are an experienced PL/SQL developer, you may find these articles a handy refresher on PL/SQL fundamentals. I will assume for this series that although the readers are new to PL/SQL, they have had some programming experience and are familiar with SQL. My approach throughout, in addition, will be to get you productive in PL/SQL as quickly as possible.

PL/SQL: At Your Command There is one way PL/SQL is just like every other programming language you will ever use: it (the PL/SQL runtime engine) does only exactly what you tell it to do. Each block of PL/SQL code you write contains one or more statements implementing complex business rules or processes. When you run that block of code, as either an anonymous block or a script or by calling a stored program unit that contains all the logic, Oracle Database follows the directions you specify in that block. It is therefore critical to know how to specify which statements should be run, under what circumstances, and with what frequency. To do this, Oracle Database offers conditional and iterative constructs. This article introduces you to the IF statement, the CASE statement and expression, and the various types of loops PL/SQL supports. Conditional Branching in Code Almost every piece of code you write will require conditional control, the ability to direct the flow of execution through your program, based on a condition. You do this with IF-THEN-ELSE and CASE statements.

July/August 2011 Oracle.com/oraclemagazine

There are also CASE expressions; although not the same as CASE statements, they can sometimes be used to eliminate the need for an IF or CASE statement altogether. IF. The IF statement enables you to implement conditional branching logic in your programs. With it, you’ll be able to implement requirements such as the following: • If the salary is between $10,000 and $20,000, apply a bonus of $1,500. • If the collection contains more than 100 elements, truncate it. The IF statement comes in three flavors, as shown in Table 1. Let’s take a look at some examples of IF statements. IF-THEN. The following statement compares two numeric values. Note that if one of these two values is NULL, the entire expression will return NULL. In the following example, the bonus is not given when salary is NULL:

not NULL) results. For example, you can use IS NULL to test for the presence of a NULL: IF l_salary > 40000 OR l_salary IS NULL THEN give_bonus (l_employee_id,500); END IF;

In this example, “salary IS NULL” evaluates to TRUE in the event that salary has no value and otherwise to FALSE. Employees whose salaries are missing will now get bonuses too. (As indeed they probably should, considering that their employer was so inconsiderate as to lose track of their pay in the first place.) IF-THEN-ELSE. Here is an example of the IF-THEN-ELSE construct (which builds upon the IF-THEN construct):

IF l_salary > 40000

IF l_salary <= 40000

THEN

THEN

give_bonus (l_employee_id,500);

give_bonus (l_employee_id, 0);

END IF;

ELSE

There are exceptions to the rule that a NULL in a Boolean expression leads to a NULL result. Some operators and functions are specifically designed to deal with NULLs in a way that leads to TRUE and FALSE (and

END IF;

give_bonus (l_employee_id, 500);

In this example, employees with a salary greater than $40,000 will get a bonus of $500, whereas all other employees will get

IF Type

Characteristics

IF THEN END IF;

This is the simplest form of the IF statement. The condition between IF and THEN determines whether the set of statements between THEN and END IF should be executed. If the condition evaluates to FALSE or NULL, the code will not be executed.

IF THEN ELSE END IF;

This combination implements either/or logic: based on the condition between the IF and THEN keywords, execute the code either between THEN and ELSE or between ELSE and END IF. One of these two sections of statements is executed.

IF THEN ELSIF ELSE END IF;

This last and most complex form of the IF statement selects a condition that is TRUE from a series of mutually exclusive conditions and then executes the set of statements associated with that condition. If you’re writing IF statements like this in any Oracle Database release from Oracle9i Database Release 1 onward, you should consider using searched CASE statements instead.

Table 1: IF statement flavors

Lindy Groening

50

PL/SQL 101 By Steven Feuerstein

no bonus at all. Or will they? What happens if the salary, for whatever reason, happens to be NULL for a given employee? In that case, the statements following the ELSE will be executed and the employee in question will get the bonus, which is supposed to go only to highly paid employees. If the salary could be NULL, you can protect yourself against this problem by using the NVL function:

IF l_salary BETWEEN 10000 AND 20000 THEN give_bonus(l_employee_id, 1000);

Here is an example of a simple CASE statement that uses the employee type as a basis for selecting the proper bonus algorithm:

ELSIF l_salary > 20000 THEN give_bonus(l_employee_id, 500); ELSE

CASE l_employee_type WHEN 'S' THEN

give_bonus(l_employee_id, 0); END IF;

award_bonus (l_employee_id); WHEN 'H' THEN

IF NVL(l_salary,0) <= 40000 THEN give_bonus (l_employee_id, 0); ELSE give_bonus (l_employee_id, 500); END IF;

The NVL function will return 0 whenever salary is NULL, ensuring that any employees with a NULL salary will also get no bonus (those poor employees). The important thing to remember is that one of the two sequences of statements will always execute, because IF-THEN-ELSE is an either/or construct. Once the appropriate set of statements has been executed, control passes to the statement immediately following END IF. IF-ELSIF. Now let’s take a look at the use of ELSIF. This last form of the IF statement comes in handy when you have to implement logic that has many alternatives; it is not an either/or situation. The IF-ELSIF formulation provides a way to handle multiple conditions within a single IF statement. In general, you should use ELSIF with mutually exclusive alternatives (that is, only one condition can be TRUE for any execution of the IF statement). Each ELSIF clause must have a THEN after its condition. (Only the ELSE keyword does not need the THEN keyword.) The ELSE clause in the IF-ELSIF is the “otherwise” of the statement. If none of the conditions evaluates to TRUE, the statements in the ELSE clause will be executed. But the ELSE clause is optional. You can code an IF-ELSIF that has only IF and ELSIF clauses. In such a case, if none of the conditions is TRUE, no statements inside the IF block will be executed. Here’s an example of an IF-ELSIF statement that checks for three different conditions and contains an ELSE clause in case none of those conditions evaluates to TRUE.

CASE: A Useful Alternative to IF The CASE statement and expression offer another way to implement conditional branching. By using CASE instead of IF, you can often express conditions more clearly and even simplify your code. There are two forms of the CASE statement: • Simple CASE statement. This one associates each of one or more sequences of PL/SQL statements with a value. The simple CASE statement chooses which sequence of statements to execute, based on an expression that returns one of those values. • Searched CASE statement. This one chooses which of one or more sequences of PL/SQL statements to execute by evaluating a list of Boolean conditions. The sequence of statements associated with the first condition that evaluates to TRUE is executed. In addition to CASE statements, PL/SQL also supports CASE expressions. A CASE expression is similar in form to a CASE statement and enables you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements. Simple CASE statements. A simple CASE statement enables you to choose which of several sequences of PL/SQL statements to execute, based on the results of a single expression. Simple CASE statements take the following form:

award_bonus (l_employee_id); WHEN 'C' THEN award_commissioned_bonus ( l_employee_id); ELSE RAISE invalid_employee_type; END CASE;

This CASE statement has an explicit ELSE clause, but the ELSE is optional. When you do not explicitly specify an ELSE clause of your own, PL/SQL implicitly uses the following: ELSE RAISE CASE_NOT_FOUND;

In other words, if you don’t specify an ELSE clause and none of the results in the WHEN clauses matches the result of the CASE expression, PL/SQL will raise a CASE_ NOT_FOUND error. This behavior is different from that of IF statements. When an IF statement lacks an ELSE clause, nothing happens when the condition is not met. With CASE, the analogous situation leads to an error. Searched CASE Statements. A searched CASE statement evaluates a list of Boolean expressions and, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression. Searched CASE statements have the following form:

CASE expression

CASE

WHEN result1 THEN

WHEN expression1 THEN

statements1 WHEN result2 THEN statements2

statements1 WHEN expression2 THEN statements2

...

...

ELSE

ELSE

statements_else END CASE;

statements_else END CASE;

oracle magazine July/August 2011

51

52

PL/SQL 101

A searched CASE statement is a perfect fit for the problem of implementing my bonus logic, as shown below: CASE WHEN l_salary BETWEEN 10000 AND 20000 THEN give_bonus(l_employee_id, 1500); WHEN salary > 20000 THEN give_bonus(l_employee_id, 1000);

Code Listing 1: A simple loop PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER ,end_year_in IN PLS_INTEGER ) IS l_current_year PLS_INTEGER := start_year_in; BEGIN LOOP EXIT WHEN l_current_year > end_year_in; display_total_sales (l_current_year); l_current_year := l_current_year + 1; END LOOP; END display_multiple_years;

ELSE give_bonus(l_employee_id, 0);

Code Listing 2: A simple loop that iterates through the rows of a cursor

END CASE;

Tip: Because WHEN clauses are evaluated in order, you may be able to squeeze some extra efficiency out of your code by listing the most likely WHEN clauses first. In addition, if you have WHEN clauses with “expensive” expressions (that is, ones requiring lots of CPU cycles and memory), you may want to list those last to minimize the chances that they will be evaluated. Use searched CASE statements when you want to use Boolean expressions as a basis for identifying a set of statements to execute. Use simple CASE statements when you can base that decision on the result of a single expression. Using CASE expressions. A CASE expression returns a single value, the result of whichever result_expression is chosen. Each WHEN clause must be associated with exactly one expression (not statement). Do not use semicolons or END CASE to mark the end of the CASE expression. CASE expressions are terminated by a simple END. A searched CASE expression can be used to simplify the code for applying bonuses. Rather than writing an IF or CASE statement with three different calls to give_bonus, I can call give_bonus just once and use a CASE expression in place of the second argument: give_bonus(l_employee_id, CASE WHEN l_salary BETWEEN 10000 AND 20000 THEN 1500 WHEN l_salary > 40000 THEN 500

July/August 2011 Oracle.com/oraclemagazine

PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER , end_year_in IN PLS_INTEGER) IS CURSOR years_cur IS SELECT * FROM sales_data WHERE year BETWEEN start_year_in AND end_year_in; l_year sales_data%ROWTYPE; BEGIN OPEN years_cur; LOOP FETCH years_cur INTO l_year; EXIT WHEN years_cur%NOTFOUND; display_total_sales (l_year); END LOOP; CLOSE years_cur; END display_multiple_years;

ELSE 0 END);

Unlike with the CASE statement, no error is raised in the event that no WHEN clause is selected in a CASE expression. Instead, if no WHEN condition is met, a CASE expression will simply return NULL. Iterative Processing with Loops Loops in code give you a way to execute the same body of code more than once. Loops are a very common element of programming, because so much of the real-world activity modeled by our programs involves repetitive processing. We might need, for example, to perform an operation for each month of the previous year. To cite a common example

from Oracle’s famous employees table, we might want to update information for all the employees in a given department. PL/SQL provides three kinds of loop constructs: • The FOR loop (numeric and cursor) • The simple (or infinite) loop • The WHILE loop Each type of loop is designed for a specific purpose with its own nuances, rules for use, and guidelines for high-quality construction. To give you a feel for how the different loops solve their problems in different ways, consider the following three loop examples. In each case, the procedure makes a call to display_total_sales for a particular year, for each year between the start and end argument values.

53

The FOR loop. Oracle Database offers both a numeric and a cursor FOR loop. With the numeric FOR loop, you specify the start and end integer values, and PL/SQL does the rest of the work for you, iterating through each integer value between the start and the end and then terminating the loop:

Code Listing 3: A WHILE loop PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER ,end_year_in IN PLS_INTEGER ) IS l_current_year PLS_INTEGER := start_year_in; BEGIN WHILE (l_current_year <= end_year_in) LOOP display_total_sales (l_current_year); l_current_year := l_current_year + 1; END LOOP; END display_multiple_years;

PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER ,end_year_in

IN PLS_INTEGER

) IS BEGIN

Code Listing 4: A FOR loop with a conditional exit

FOR l_current_year IN start_year_in .. end_year_in

PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER , end_year_in IN PLS_INTEGER) IS BEGIN FOR l_current_year IN start_year_in .. end_year_in LOOP display_total_sales (l_current_year);

LOOP display_total_sales (l_current_year); END LOOP; END display_multiple_years;

The cursor FOR loop has the same basic structure, but with it you supply an explicit cursor or SELECT statement in place of the low/high integer range:

EXIT WHEN total_sales (l_current_year) = 0; END LOOP; END display_multiple_years;

Code Listing 5: A WHILE loop with one exit

PROCEDURE display_multiple_years ( PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER , end_year_in IN PLS_INTEGER) IS l_current_year PLS_INTEGER := start_year_in; BEGIN WHILE ( l_current_year <= end_year_in AND total_sales (l_current_year) > 0) LOOP display_total_sales (l_current_year); l_current_year := l_current_year + 1; END LOOP; END display_multiple_years;

start_year_in IN PLS_INTEGER ,end_year_in IN PLS_INTEGER ) IS BEGIN FOR l_current_year IN ( SELECT * FROM sales_data WHERE year BETWEEN start_year_in AND end_year_in) LOOP display_total_sales (l_current_year); END LOOP; END display_multiple_years;

,end_year_in IN PLS_INTEGER ) IS l_current_year INTEGER; /* NOT NEEDED */

In both the numeric and the cursor FOR loop, Oracle Database implicitly declares the iterator (in the examples above, it is l_ current_year) for you, as either an integer or a record. You do not have to (and should not) declare a variable with that same name, as in PROCEDURE display_multiple_years ( start_year_in IN PLS_INTEGER

BEGIN FOR l_current_year IN start_year_in .. end_year_in

In fact, if you do declare such a variable, it will not be used by the FOR loop. It is, more specifically, a different integer variable than that declared implicitly by Oracle Database

and used within the body of the loop. The simple loop. It’s called simple for a reason: it starts simply with the LOOP keyword and ends with the END LOOP statement. The loop will terminate if you execute an EXIT, EXIT WHEN, or RETURN within the body of the loop (or if an exception is raised). Listing 1 presents a simple loop. Listing 2 presents a simple loop that iterates through the rows of a cursor (logically equivalent to the cursor FOR loop of the previous section). Compare the cursor-based simple loop in Listing 2 with the cursor FOR loop. Note that I must explicitly open the cursor, fetch

oracle magazine July/August 2011

54

PL/SQL 101

the next record, determine by using the %NOTFOUND cursor attribute whether or not I am done fetching, and then close the cursor after the loop terminates. The cursor FOR loop requires none of these steps; Oracle Database performs all steps (open, fetch, terminate, close) implicitly. Although a simple loop should not be used to fetch row by row through a cursor’s dataset, it should be used when (1) you may need to conditionally exit from the loop (with an EXIT statement) and (2) you want the body of the loop to execute at least once. The WHILE loop. The WHILE loop is very similar to the simple loop; a critical difference is that it checks the termination condition up front. That is, a WHILE loop may not even execute its body a single time. Listing 3 demonstrates the WHILE loop. The WHILE loop consists of a condition (a Boolean expression) and a loop body. Before each iteration of the body, Oracle Database evaluates the condition. If it evaluates to TRUE, the loop body will execute. If it evaluates to FALSE or NULL, the loop will terminate. You must then make sure to include code in the body of the loop that will affect the evaluation of the condition—and, at some point, cause the loop to stop. In the procedure in Listing 3, that code is l_current_year :=

l_current_year + 1

In other words, move to the next year until the total sales for all specified years are displayed. If your WHILE loop does not somehow change the way the condition is evaluated, that loop will never terminate. One Way In, One Way Out In all the examples in the previous section, the FOR loop clearly requires the smallest amount of code. Generally you want to find the simplest, most readable implementation for your requirements. Does that mean that you should always use a FOR loop? Not at all. Using the FOR loop is the best solution for the scenario described because I needed to run the body of the loop a fixed number of times. In many other situations, the number of times a loop must execute will vary, depending on the state of the data in the application. You may also need to terminate the loop when a

July/August 2011 Oracle.com/oraclemagazine

Code Listing 6: A FOR loop with two instances of RETURN FUNCTION total_sales ( start_year_in IN PLS_INTEGER , end_year_in IN PLS_INTEGER) RETURN PLS_INTEGER IS l_return PLS_INTEGER := 0; BEGIN FOR l_current_year IN start_year_in .. end_year_in LOOP IF total_sales (l_current_year) = 0 THEN RETURN l_return; ELSE l_return := l_return + total_sales (l_current_year); END IF; END LOOP; RETURN l_return; END total_sales;

Code Listing 7: A loop revision with one way out FUNCTION total_sales ( start_year_in IN PLS_INTEGER , end_year_in IN PLS_INTEGER) RETURN PLS_INTEGER IS l_current_year PLS_INTEGER := start_year_in; l_return PLS_INTEGER := 0; BEGIN WHILE (l_current_year <= end_year_in AND total_sales (l_current_year) > 0) LOOP l_return := l_return + total_sales (l_current_year); l_current_year := l_current_year + 1; END LOOP; RETURN l_return; END total_sales;

certain condition has occurred; in this case, a FOR loop is not a good fit. One important and fundamental principle in structured programming is “one way in, one way out”—that is, a program should have a single point of entry and a single point of exit. A single point of entry is not an issue with PL/SQL—no matter what kind of loop you are using, there is always only one entry point into the loop: the first executable statement following the LOOP keyword. It is quite possible, however, to construct loops that have multiple exit paths. Avoid this practice. Having multiple ways of terminating a loop results in code that is much harder to debug and maintain than it would be otherwise. In particular, you should follow these two guidelines for loop termination:

1. Do not use EXIT or EXIT WHEN statements within FOR and WHILE loops. You should use a FOR loop only when you want to iterate through all the values (integer or record) specified in the range. An EXIT inside a FOR loop disrupts this process and subverts the intent of that structure. A WHILE loop, on the other hand, specifies its termination condition in the WHILE statement itself. Listing 4 presents an example of a FOR loop with a conditional exit. I want to display the total sales for each year within the specified range. If I ever encounter a year with zero sales, however, I should stop the loop. There are now two ways “out” of the loop. In this situation, I will rewrite the FOR loop as a WHILE loop, which means I must now also declare the iterator and add to it within

55

the loop, as shown in Listing 5. 2. Do not use RETURN or GOTO statements within a loop—these cause the premature, unstructured termination of the loop. Listing 6 presents an example of a FOR loop with a RETURN in it. The total_sales function returns the total sales across the specified years, but if any year has $0 in sales, the function should terminate the loop and return the current total sales. Note that the loop terminates in one of two ways: either by iterating through all integers between the start and end years or by executing the RETURN inside the loop. In addition and related to that, this function now has two instances of RETURN, which means that there are two ways “out” of the function. This is also not a recommended way to design your functions. You should have just a single RETURN in your executable section, the last line of the function. I can restructure this function so that both

the loop and the function have just “one way out,” as shown in Listing 7. All the logic required to terminate the loop is now in the WHILE condition, and after the loop is finished, the function executes a single RETURN to send back the total sales value. This second implementation is now simpler and easier to understand—and that is for a program that itself is already quite simple. When you work with much more complex algorithms, following the “one way in, one way out” guidelines will have an even greater impact on the readability of your code. In this article, you learned about how to tell the PL/SQL compiler to conditionally and iteratively execute the statements in your block. This control will enable you to write stored program units that mirror the business process flow defined by your users. Spoiler Alert: The next article in this series, Part 3, will discuss working with strings in PL/SQL programs. t

Steven Feuerstein (steven.feuerstein@ quest.com) is Quest Software’s PL/SQL evangelist. He has published 10 books on Oracle PL/SQL (O’Reilly Media) and is an Oracle ACE director. More information is available at stevenfeuerstein.com.

NEXT STEPS READ PL/SQL 101, Part 1 bit.ly/eYIxRq PL/SQL columns bit.ly/fc0uoJ DOWNLOAD Oracle Database 11g oracle.com/software/products/database DISCUSS PL/SQL forums.oracle.com/forums/forum.jspa?forumID=75 TEST your PL/SQL knowledge plsqlchallenge.com

Visit groupwareadvantage.com

EVERY GREAT BUSINESS HAS A HERO.

Introducing the cost-saving, record-breaking, performance-driving, HERO-BUILDING data center solutions from Oracle. Today’s enterprise data centers are evolving faster than a speeding bullet. Give yourself the tools and confidence to keep pace with industry leading Oracle solutions backed by the proven expertise of an Oracle Platinum partner. Count on Groupware to plan, design, support and implement a custom storage solution that can meet today’s business goals and adapt to tomorrow’s changes. Discover how Groupware Database Specialists will help prioritize the applications and processes that will yield the greatest performance improvements and cost savings.

Visit

groupwareadvantage.com

ORACLE OFFERS COMPLETE AND INTEGRATED DATA CENTER SOLUTIONS » Oracle’s Sun Storage F5100 Flash Arrays provide the best and easiest way to accelerate database performance up to 10X and reduce operating costs up to 80% compared to traditional disk drives. » Oracle’s Sun ZFS Storage Appliance dramatically optimizes database and application performance and reduces I/O bottlenecks with no administrator intervention via automatic and intelligent data placement across the storage hierarchy. » Oracle’s SPARC T3-1 server is the platform of choice for web infrastructure, middleware workloads, and application development, delivering the world’s first 16-core processor with unsurpassed throughput in a sleek, compact design. © 2011 Groupware, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

ORACLE MAGAZINE JULY/AUGUST 2011

Exadata Runs 17x Faster 1 Exadata ran 17x faster than 4 large UNIX servers.

oracle.com/goto/BNPparibas BNP Paribas, 3rd largest global bank

Individual results depend on a number of factors. Actual results may vary. Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates.

Database Application Developer and DBA

Ask Tom by Tom Kyte

Oracle Database 11g

On Better Loading and Fact-Checking Our technologist enjoys the rule of external tables and debunks index scan myths.

I

’m working to improve the efficiency of an existing process that starts with staging an input file (generated by a client program) for the database. In some situations, the input file can include 20,000,000 lines or more, but with one-third or so of those lines being of no use as input. At present all the lines are transferred (as rows in a staging table), with unneeded lines pruned at the beginning of processing with a DELETE. The DELETE obviously generates a lot of redo and undo and takes some time. Further, we’re transferring lines we don’t even need to bother with in the first place. I’m trying to modify the SQL*Loader control file to discard these unneeded records to save both the transfer time and the pruning time. However, I’m running up against SQL*Loader’s lack of an OR in its condition processing. I’ve tried structuring the control file to perform multiple tests, so that each successful test would result in a row’s being sent to the database. My results show that nearly all rows are being rejected. My questions: 1. W  hat’s wrong with my control file (see below)? 2. Is there a better way to do this? Here’s the staging table: create table staging ( rectype char(1) not null, id number not null, name varchar2(64) not null, val varchar2(256) ); Here’s my sample input file, inputdata.csv: 4,1,"rn","" 4,2,"rn","nonnull" 4,3,"rd",""

4,4,"rd","nonnull" 4,5,"ac","NO" 4,6,"ac","YES" 4,7,"ie","nonnull" 4,8,"at","nonnull" 4,9,"ms","1.23" 4,10,"mb","1.56" 4,11,"ms",".99" Here’s my control file: LOAD DATA REPLACE INTO TABLE staging WHEN NAME = 'rn' AND VAL <> '' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( RECTYPE, ID, NAME, VAL ) INTO TABLE staging WHEN NAME = 'rd' AND VAL <> '' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( RECTYPE, ID, NAME, VAL ) … other INTO clauses …. Well, I’ll point out two things here. First: SQL*Loader documentation states that SQL*Loader doesn’t “start over” with multiple INTO clauses. That is, the first INTO clause will get the record and start processing it, and the second INTO clause will get the same record and pick up processing where the first INTO clause stopped. The process does not go back to column 1. In short, the documentation (bit.ly/fmauzu) states The important point in this example is that the second empno field is found immediately after the first ename,

although it is in a separate INTO TABLE clause. Field scanning does not start over from the beginning of the record for a new INTO TABLE clause. Instead, scanning continues where it left off. So a quick fix for you would be to use POSITION(1) in your control file for each of the RECTYPE attributes after the first INTO clause. That is, just change RECTYPE in the control file to RECTYPE POSITION(1). Once I made that change, your data loaded right up. The second thing I’d like to point out is that as far as I’m concerned, SQL*Loader is dead—because external tables rule. The SQL*Loader control file syntax is very inflexible compared to simple SQL. Also, in your example, you are loading a staging table, which likely will be further processed and placed into “real” tables. Therefore, you are 1. Taking a flat file and reading it 2. Loading it into a table 3. Reading that table 4. Ultimately loading the table from Steps 2 and 3 into other tables With external tables you can simplify that process to 1. Taking a flat file and reading it (as a table) 2. Ultimately loading the “table” from Step 1 into other tables. You can skip major pieces of your current processing, including loading a table temporarily and rereading all the data. Let’s take your control file and use it to create an external table definition to see how easy this would all be if you used an external table instead of SQL*Loader. If I take your control file and remove all but one of the INTO clauses and get rid of the WHEN clause— LOAD DATA REPLACE

oracle magazine July/August 2011

57

58

Ask Tom

INTO TABLE staging FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS

incorrect. They are common misconceptions but untrue nonetheless. A fast full index scan reads the entire index, unsorted, as it exists on disk. It is basi-

cally using the index as a “skinny” version of the table. The query in question would be accessing only attributes in the index. (We are not using the index as a way to get to the

( RECTYPE, ID, NAME, VAL )

Code Listing 1: CREATE TABLE and a query against it —I can use SQL*Loader to generate a CREATE TABLE statement easily by running the command sqlldr userid=/ control=test.ctl external_table=generate_only

The resulting log file will include the CREATE TABLE statement in Listing 1 for me. Listing 1 also includes a SQL query against the new table. Note that Listing 1 includes the entire set of INTO/WHEN clause logic. Because I am using an external table, I have the entire power of SQL at my disposal. In SQL, OR conditions are not hard; they are quite natural; and best of all, I avoided a lot of work. As you all know, the fastest way to do something is to not do it. Using external tables enables you to not do things without losing anything along the way. Difference Between Full Index Scans and Fast Full Index Scans What is the difference between full index scan and fast full index scan? I’ve read elsewhere that a fast full index scan is an alternative to a full table scan when the index contains all the columns needed for the query and at least one column in the index key has the NOT NULL constraint. A fast full index scan accesses the data in the index itself without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index by using multiblock reads, unlike a full index scan, and can be parallelized. Fast full index scans cannot be performed against bitmap indexes. A fast full index scan is faster than a full index scan, because it can use multiblock I/O and can be parallelized just like a table scan. I am not clear about this. Could you please clarify? I’ll start with a definition and then get into clearing up some of these misconceptions, because many of the stated “facts” are

July/August 2011 Oracle.com/oraclemagazine

CREATE TABLE "SYS_SQLLDR_X_EXT_STAGING" ( "RECTYPE" CHAR(1), "ID" NUMBER, "NAME" VARCHAR2(64), "VAL" VARCHAR2(256) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY EXEC_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'EXEC_DIR':'test.bad' LOGFILE 'test.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "RECTYPE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "NAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "VAL" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ) ) location ( 'test.dat' ) )REJECT LIMIT UNLIMITED /

Now, all I have to do is issue a query such as select from where or or or

* sys_sqlldr_x_ext_staging (name = 'rn' and val is not null) (name = 'rd' and val is not null) (name = 'ac' and val <> 'NO' ) (name not in ( 'ie', 'at', 'rn', 'rd', 'ac' ))

Code Listing 2: Query uses fast full index scan SQL> set autotrace traceonly explain SQL> select count(*) from t where owner = 'SCOTT'; Execution Plan —————————————————————————————————————————————————————————————————————————— Plan hash value: 1058879072 ——————————————————————————————————————————————————————————————————————————————————————————————————————— | Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time | ——————————————————————————————————————————————————————————————————————————————————————————————————————— | 0|SELECT STATEMENT | | 1| 17 | 69 (2)|00:00:01| | 1| SORT AGGREGATE | | 1| 17 | | | |* 2| INDEX FAST FULL SCAN|T_IDX| 12| 204 | 69 (2)|00:00:01| ———————————————————————————————————————————————————————————————————————————————————————————————————————

59

table—we are using the index instead of the table.) We use multiblock I/O and read all the leaf, branch, and root blocks. We ignore the branch and root blocks when executing the query and just process the (unordered) data in the leaf blocks. A full index scan reads the index a block at a time, from start to finish. It reads the root block, navigates down the left-hand side of the index (or the right-hand side for a descending full scan), and then when it hits the leaf block, it reads across the entire bottom of the index—a block at a time—in sorted order. It uses single-block, not multiblock, I/O for this operation. Now, let’s address the misconceptions the questioner “read elsewhere.” It is true that a fast full index scan is an alternative to a full table scan when the index contains all the columns referenced in the query, but “at least one column in the index key has the NOT NULL constraint” is not a requirement. I can prove this by example. Suppose I have the following table: SQL> create table t 2

as

3

select *

4

from all_objects;

SQL> alter table t

on t(status,owner);

Index created. SQL> desc t Null?

———————————————

———————————

OWNER OBJECT_NAME

Type

VARCHAR2(30)

... STATUS

bm_idx on t(owner);

—and then run the simple query in Listing 3.

Code Listing 3: Simple query uses bitmap index for fast full index scan SQL> set autotrace traceonly explain SQL> select distinct owner from t; Execution Plan —————————————————————————————————————————————————————————————————————————— Plan hash value: 4145652518 —————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | 0 |SELECT STATEMENT | |82657| 1372K| 465 (1)|00:00:06| | 1 | HASH UNIQUE | |82657| 1372K| 465 (1)|00:00:06| | 2 | BITMAP INDEX FAST FULL SCAN|BM_IDX|82657| 1372K| 7 (0)|00:00:01| ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————

—————————————————————————————————————————————————————————————————————————————————————————————— | Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time | —————————————————————————————————————————————————————————————————————————————————————————————— | 0 |SELECT STATEMENT| |34755| 746K| 247 (1)|00:00:03| |* 1 | INDEX FULL SCAN|T_IDX|34755| 746K| 247 (1)|00:00:03| ——————————————————————————————————————————————————————————————————————————————————————————————

———————————————— VARCHAR2(30)

NOT NULL

2

Execution Plan —————————————————————————————————————————————————————————————————————————— Plan hash value: 2277287974

SQL> create index t_idx

Name

SQL> create bitmap index

SQL> select status, owner from t 2 where owner = 'SYS' order by status, owner;

modify owner null;

Table altered.

2

clause explicitly says that we are looking for a not-null value of OWNER, I know that every row I need appears in the index. Moving on, you have the statement “Fast full index scans cannot be performed against bitmap indexes.” This is not true, and I can once again prove that by example, using the same table T. I’ll continue by adding a bitmap index—

Code Listing 4: Choosing the right index scan for the right query

Table created.

2

table (the index is acting as a skinny version of the table), and the predicate, where owner = ‘SCOTT’, makes it such that the OWNER column must be NOT NULL to be in the result set. And because any NOT NULL value would appear in the index, we can use the index. If the query were SELECT COUNT(*) FROM T, it is true that we would not be able to use the index with a fast full scan instead of the table, because the table could have rows that are not in the index. Any row in which STATUS was null and OWNER was null would not appear in the index, and we’d miss counting it. But because the WHERE

VARCHAR2(7)

...

As you can see, neither STATUS nor OWNER is defined as NOT NULL. However, if I execute the query in Listing 2, I will observe a fast full index scan in action. That’s because we can use the index in place of the

SQL> select status, owner from t 2 where owner = 'SCOTT' order by status, owner; Execution Plan —————————————————————————————————————————————————————————————————————————— Plan hash value: 3276893591 ——————————————————————————————————————————————————————————————————————————————————————————————————————— | Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time | ——————————————————————————————————————————————————————————————————————————————————————————————————————— | 0 |SELECT STATEMENT | | 8| 176 | 70 (3)|00:00:01| | 1 | SORT ORDER BY | | 8| 176 | 70 (3)|00:00:01| |* 2 | INDEX FAST FULL SCAN|T_IDX| 8| 176 | 69 (2)|00:00:01| ———————————————————————————————————————————————————————————————————————————————————————————————————————

oracle magazine July/August 2011

60

Ask Tom

Code Listing 5: Limited digits cannot accurately represent 1/19 SQL> set numformat 0.999999999999999999999999999999999999999999999 SQL> select 1/19 from dual; 1/19 ———————————————————————————————————————————————————————————————————————— 0.052631578947368421052631578947368421052600000

Code Listing 6: 38 digits of precision are not enough to equal 1 SQL> select 1/19*19 from dual; 1/19*19 ———————————————————————————————————————————————————————————————————————— 0.999999999999999999999999999999999999999400000

Code Listing 7: Reduced precision with floating-point numbers SQL> select 1f/19f from dual; 1F/19F ———————————————————————————————————————————————————————————————————————— 0.052631579300000000000000000000000000000000000

As you can see, we definitely would use a bitmap index to perform a fast full index scan—in fact, we can do it even when OWNER (the only indexed column) is NULLABLE. That is because, unlike the T_IDX B*Tree index, bitmap indexes always index NULL keys. Every row in a table will be pointed to by a bitmap index, regardless of the data values. And last, there is the statement that “a fast full index scan is faster than a full index scan, because it can use multiblock I/O and can be parallelized just like a table scan.” I have a saying that goes like this: “Never say never and never say always, I always say.” When you see any statement such as “X is faster than Y,” be suspicious. If X were always faster than Y, technology Y would not have been implemented or invented in the first place! There must be some times when the converse is true. So, from a performance perspective, that statement is something that may or may not be true. The answer to whether that statement is true is, “It depends.” Again using the table T example, if I run the query select status, owner from t where owner = 'some user'

July/August 2011 Oracle.com/oraclemagazine

order by status, owner;

the optimizer will sometimes pick a fast full index scan on T_IDX and sometimes pick a full index scan on T_IDX. It all depends on the estimated cardinalities involved. If this query is going to retrieve a very small set of rows, the cost of sorting them will be very low, and we’ll probably use a fast full index scan to reduce the I/O cost. On the other hand, if the query is going to retrieve a lot of rows, the cost of sorting them begins to go way up, and we might choose a full index scan, with a higher I/O cost but zero cost for sorting, instead. This is borne out by the example in Listing 4. As Listing 4 demonstrates, when we are going to retrieve about 35,000 rows, we use a full index scan to avoid the sort. When we go after about 8 rows, we use the fast full index scan and just sort the results. Rollback Segment for SELECT Does Oracle Database need a rollback segment for queries? I thought it used rollback segments only for data manipulation language (DML) operations, but it recently failed, resulting in the error message “Unable to extend rollback segment” for a SELECT statement.

We added some space to that segment, and then the SELECT statement worked fine. Does the database use rollback segments for SELECT statements? First, let’s refine the question. Instead of “does the database use rollback segments for SELECT statements,” I’ll answer the question “does a SELECT statement generate or read rollback segments?” The verb use is just not specific enough. Every SELECT statement has the potential to “use” undo (rollback) data. Oracle Database uses undo naturally when processing all queries to produce readconsistent result sets. (See bit.ly/elhCUS for more details on this topic.) However, this use of undo would not cause an “unable to extend” error message. It could quite easily cause the dreaded “ORA-1555 Snapshot too old” error message, but it would not by itself generate an ORA1650 or ORA-1651 error message, “Unable to extend rollback/undo segment. . . .” To generate the ORA-1650/1651 error message, the SELECT statement would have to be generating undo, possible causes of which would be • The SELECT statement was an UPDATE in disguise—the SELECT statement contained the FOR UPDATE clause. • Auditing was enabled, which is what raised the ORA-1650/1651 error message. • The SELECT statement was invoking some sort of autonomous transaction function that wrote to the database, and that is what caused the ORA-1650/1651 error message. I would suspect that the first reason was the likely cause of this “Unable to extend rollback/undo segment . . .” error message. When you issue a SELECT FOR UPDATE, Oracle Database will lock all the rows for you before you get the first row back. Locking a row in Oracle Database involves modifying the database block to place the lock there. And anytime you modify a database block, you generate undo (and redo) for that operation. You can see this easily: SQL> create table t 2

as

3

select *

4

from all_objects;

Table created.

61

Code Listing 8: Reduced precision accidentally helps result SQL> select 0.0526315793f * 19f from dual; 0.0526315793F*19F ———————————————————————————————————————————————————————————————————————— 1.000000000000000000000000000000000000000000000

Code Listing 9: More-precise numbers create larger error SQL> select 0.0526315793 * 19 from dual; 0.0526315793*19 ———————————————————————————————————————————————————————————————————————— 1.000000006700000000000000000000000000000000000

SQL> select used_ublk 2

from v$transaction;

no rows selected SQL> begin 2

for x in

3

(select *

4

from t

5

for update)

6

loop

7

null;

8

end loop;

9 10

end; /

PL/SQL procedure successfully completed. SQL> select used_ublk 2

from v$transaction;

USED_UBLK ————————————— 629

Processing that SELECT statement generated 629 blocks of undo (I was in a “single user” database, so I know that row in V$TRANSACTION is mine). What Is 1/19? Oracle Database is under the impression that 1/19 * 19 – 1 = –6 × 10–40. You can replace 19 with 19 multiplied by any positive power of DOWNLOAD Oracle Database 11g Release 2 bit.ly/epBiUG

100, and the problem remains. When you multiply 19 by an odd power of 10, the answer is 0. For example, 1/1900 * 1900 – 1 = –6 × 10–40, but 1/190 * 190 – 1 = 0. I understand that it has to do with the way numbers are stored internally, but is there a simple way around it? That happens with many numbers. It happens with all man-made representations of numbers, and it happens all the time in computers (especially with floating-point number datatypes—they are much worse in general than a NUMBER datatype). First, it is very important to realize that this isn’t specific to Oracle Database; spreadsheets and all other computer programs I’ve ever seen are subject to this somewhere. The problem arises anytime in computer programs when you start doing things with really small and really big numbers. The precision of the intermediate result gets maxed out and cannot hold all the digits. The problem starts with 1/19. Limited digits cannot represent that number “perfectly,” as shown in Listing 5. You run out of places to hold the decimal, so that representation is not exactly 1/19, but it is close. And when you multiply by 19, you run into another issue. You have 38 digits of precision, but the numbers being multiplied are so far apart that you’d need more than 38 to do it precisely, as shown in Listing 6. And now you see the issue. If you were to do this in floating-point numbers, it would work (with these numbers) by accident, as shown in Listing 7. You see the massively reduced precision with floating-point number datatypes.

You get an answer that is much farther away from 1/19 than when you use Oracle NUMBER datatypes. Later, when you multiply, as shown in Listing 8, it appears to work, by accident, but only because of the reduced precision. Using more-precise numbers, in Listing 9, the result isn’t really 1, and the error is actually much larger, but you “lost” it because you had a number with 10 digits to the right of the decimal point and a number with 2 digits to the left. The reduced-precision arithmetic lost the error (by accident). The only way I know to get around it is the old programming technique of using a tolerance rather than an equal sign (=). You cannot use another type of number (such as a float or a double), because it will produce errors as well when working with different numbers. If you are mixing really small, precise numbers with much larger numbers, you might need to use “if (abs(x-0) < tolerance) then” instead of “if (x=0) then” in your code. 

Tom Kyte is a database evangelist in Oracle’s Server Technologies division and has worked for Oracle since 1993. He is the author of Expert Oracle Database Architecture (Apress, 2005, 2010) and Effective Oracle by Design (Oracle Press, 2003), among other books.

Next Steps ASK Tom Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column. asktom.oracle.com READ more Tom Oracle Database Concepts 11g Release 2 (11.2) bit.ly/aonqPP Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition amzn.to/ckGXaR READ more about SQL*Loader bit.ly/fmauzu index scans bit.ly/gvZF5v read consistency bit.ly/elhCUS

oracle magazine July/August 2011

Subscribe

Scan Scan to Subscribe Today 1. 2. 3. 4. 5. 6.

Download the reader to your Web-enabled phone Open the browser on your phone Navigate to get.neoreader.com Install the NeoReader 2D barcode reader as instructed Open the NeoReader application on your phone Scan the barcode shown here

In the Field By Andy Flower

Build and Buy

An Oracle technologist looks at the principles for integrating built and bought solutions.

T

oday, it is uncommon for organizations to completely build their own enterprise applications. There are better options for acquiring packaged enterprise solutions with some level of customization that serve a particular need in our organizations. Inevitably, there will be parts of our organizations whose needs are not met by the enterprise solutions, which will lead to departmental efforts to buy or build applications that satisfy those needs. These applications often undercut our ability to consistently and cost-effectively manage our software and technology ecosystem. As a result, many IT organizations face three basic questions: 1. What do we need to build, customize, enhance, or extend to fill the gaps in our enterprise applications and still maintain consistent use of information resources? 2. What do we need to do to support consistent integration of departmental applications built with nonstandard technologies and toolsets with our enterprise applications? 3. Finally, what do we need to build or buy to support our disparate enterprise and departmental customers and environments? Customize, Enhance, Extend We need to be prepared to make changes that extend the enterprise software’s capabilities. To get in front of departments that are frustrated by lack of functionality or the slow pace of change, we as technologists need to provide a common platform and toolset so that we can provide consistent support. For customizations of the core system, the obvious solution is to leverage the development tools and flexible data structures provided by the platform to enhance the existing functionality. Customizations and extensions are not always seamless, however, and that leads

us to the next question: how can organizations support consistent integration? Integration Consistent use of both function and data is one of the largest challenges our organizations face, which often drives the desire for an enterprise solution. However, as described above, finding the perfect fit is elusive. Inevitably, we will need to integrate custom or third-party functionality and data with our enterprise applications. There are four basic types of integration: service, information, process, and technology.1 Any time we integrate solutions, we need to be cognizant of all four aspects. Services, or the basic functionality of the processes we integrate, must be complementary. Information, or the data we need to share across services, must be consistently managed and shared. Processes must be flexible to orchestrate the sequence in which services are executed and information exchanged. Technology, or the platforms that enable service, information, and process integration, must also be a manageable, consistent stack. As technology professionals, we typically focus on the technology platform. There are tools within Oracle Database that support the development of both services and information integration capabilities, including PL/SQL, Oracle Application Express, and Oracle Warehouse Builder. From a middleware perspective, Oracle Fusion Middleware products and technologies—including Oracle WebCenter Suite, Oracle SOA Suite, Oracle Data Integrator, Oracle Business Intelligence, and Oracle Business Process Management—also support more-complex service and information integration. Supporting Our Environment Regardless of whether your applications and technology platforms include only Oracle

products or a mix of other products, the key to success in build-versus-buy scenarios is having a consistent technology platform and development tools. What we can control through consistent use of platforms and tools is the time to market and overall cost to support the integration of departmental customizations and third-party extensions. Where to Get Answers If you have questions about buildversus-buy decisions or any other issues around the use of Oracle software and hardware, there’s good news: communities of Oracle users and customers have been helping solve problems and sharing solutions for decades. And although each group has its own focus, the different groups are increasingly working together because their members are increasingly working together. (See “Next Steps” for links to the largest Oracle user group communities.) 

Andy Flower ([email protected]) is president of the Independent Oracle Users Group (IOUG) and has been an active volunteer with the organization since 1998. In his day job, he is an information management and business intelligence consultant.

Next Steps LEARN more about IOUG ioug.org the Oracle Development Tools User Group (ODTUG) odtug.com the Oracle Applications Users Group (OAUG) oaug.org Quest International Users Group questdirect.org

1 Enterprise Integration: The Essential Guide to Integration Solutions, by Beth Gold-Bernstein (Addison-Wesley, 2004)

oracle magazine July/August 2011

63

64

Analyst’s Corner by DAVID BAUM

Make the Next Storage Move Organizations reduce costs by matching the right type of storage to their needs.

O

racle Magazine spoke with Benjamin S. Woo, program vice president for worldwide storage systems at International Data Corporation (IDC), about today’s evolving storage technology and how to create an effective strategy for delivering data to the enterprise. Oracle Magazine: How can businesses create storage strategies that keep them ahead of data growth? Woo: To create an effective storage strategy that accommodates growing data volume and maximizes your gigabyte per dollar, you have to balance access time against application requirements. There is a place for high-performance solid-state drives [SSDs]; slower, high-capacity disk drives; and tape drives. Matching application attributes with data at each stage of the data lifecycle—from initial processing to final archiving—will enable you to correlate your needs with the right solution set. Oracle Magazine: How is disk storage technology evolving in terms of technology and use? Woo: We will continue to see increases at both ends of the spectrum—in capacity and performance. Performance will increase through the penetration of SSD technology, while capacity will increase through the proliferation of multiterabyte SATA [serial ATA] drives. We now have drives that hold 3 terabytes per unit in the open market. These highcapacity drives will lower the power envelope and also increase the amount of data that can be stored in one place. We will also see a gradual movement toward flash technology for some high-performance applications. Oracle Magazine: What is the optimal use of flash technology in a storage environment? Woo: Flash is a performance technology, so it is not designed to replace all storage. It’s not suitable if you are looking to maximize your gigabyte per dollar of capacity. Most people

July/August 2011 Oracle.com/oraclemagazine

Tape is still the most cost-effective way to store offsite, offline data. equate performance with capacity, but these are two very separate measures. IDC’s research indicates that flash technology will represent from 2 to 5 percent of total storage within larger enterprises. Oracle Magazine: How should organizations measure storage performance? Woo: For starters, don’t just read the spec sheet! Sheer performance is just one measure of utility, and not all types of drives are appropriate for all purposes. The best way to evaluate your particular storage needs is to look at the number of I/Os or storage transactions that represent your peak load, and then address those performance needs with the appropriate type of storage media. Oracle Magazine: What are the leading advancements in storage controllers, and how do these devices help IT departments? Woo: Storage controllers are starting to adopt multicore, multi-CPU technology. This will become very valuable as much of today’s single-threaded storage software is rewritten to take advantage of multicore processors. For example, you could have one core doing all the RAID calculations, another core doing NFS, a couple of cores handling compression and deduplication, and so forth. Multithreading will give customers a lot more bang for their buck. It will also help IT departments manage multiple storage functions in a unified way. Oracle Magazine: Oracle Exadata technology moves certain aspects of database processing into the storage layer. What are the database and storage benefits of this technology?

Woo: Systems like Oracle Exadata are what we call object-based storage systems because they provide a more direct link between where data objects are stored and the leveraging of those data objects for value-creation purposes. Interjecting database technology at this level not only dramatically improves the performance of database queries and transactions but also lets you store user-definable metadata, such as the ability to tag a file as “personal,” “public,” or “enterprise,” to identify its purpose and relevance. Oracle Magazine: What is the future of tape storage technology? Woo: Tape is not going away. It is still the most cost-effective way to store offsite, offline data. As alternatives, people have talked about “spin-down drives” and other technologies that minimize energy consumption and maximize equipment life. But if you don’t need data for a lengthy period, it is still more efficient to offload it to tape and place it “at rest” in a library. Tape is a great long-term archival technology, and it is the most reliable and cost-effective technology for achieving persistence. 

David Baum ([email protected]) is a freelance business writer based in Santa Barbara, California. IDC (idc.com) is a global provider of market intelligence, advisory services, and events for the information technology, telecommunications, and consumer technology markets.

Next Steps LEARN about Sun ZFS Storage Appliances Sun flash products StorageTek tape storage products oracle.com/storage Oracle Exadata Storage Server oracle.com/us/products/database/exadata

Leaders Run SPARC Solaris 10 of the 10 Top Banks Top Telcos Top Pharmas Top Governments

Get Better Results

oracle.com/goto/solaris or call 1.800.ORACLE.1

Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates.