Oracle Magazine September 2011

SEPTEMBER/OCTOBER 2011

Storing the Information Explosion How Oracle’s flash, disk, and tape storage technologies help IT shops handle today’s storage challenges /23 All Aboard Build train models for navigation in bounded task flows /55 Distinctive Database Development Use Entity Framework with Oracle Data Provider for .NET /59 Working with Strings Part 3 in a series of articles on understanding and using PL/SQL /65 On Caching and Evangelizing SQL Our technologist caches scalar subqueries, votes for SQL, and recommends technology and community /74

SERVERS ON THE CUTTING EDGE

Organizations deploy x86 blade servers from Oracle to reduce costs and sharpen business services

Bridging the Integration Gap

Keep information in sync with Oracle data integration solutions

Engineered for Innovation

Oracle OpenWorld Returns to San Francisco October 2–6

Moving Java Forward

JavaOne Hits San Francisco October 2–6

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.

2

FEATURED CONTENT VOLUME XXV - ISSUE 5

CONTENTS

SERVERS ON THE CUTTING EDGE /40

Once deployed only as Web servers and in less-critical deployment scenarios, x86-based servers are now successfully running business-critical applications. x86 chips power flexible rackmount and blade servers in datacenters and cloud computing environments—and are the processing foundation of cutting-edge hardware, including database and storage appliances such as Oracle Exadata. Find out how the Department of Health Care Policy at Harvard Medical School and CBOE Holdings are reducing costs and sharpening business services with x86 servers from Oracle. —By David A. Kelly

Bridging the Integration Gap / 48 With organizations moving to cloud computing, facing massive data volumes, and needing realtime data and reporting, it’s time to take a step beyond viewing data integration as a utility and see it as something more. Learn how IQNavigator and Avea are eliminating data bottlenecks and keeping information in sync with strategic data integration solutions from Oracle. —By David A. Kelly

SEPTEMBER/OCTOBER 2011 ORACLE.COM/ORACLEMAGAZINE

Engineered for Innovation / 8 Find out what’s in store at Oracle OpenWorld, October 2-6.

Moving Java Forward / 10 See what’s on the agenda at JavaOne, October 2-6.

Learn more about Oracle technology with Safari Books Online

Use your QR code reader to access information about a group trial or visit safaribooksonline.com/oracletrial

SEARCH LESS. DEVELOP MORE. With the intelligent library in the cloud.

Visit our booth at Oracle OpenWorld in October

4

Departments Volume XXV - Issue 5

Up Front / 6

Community / 26

From the Editor / 6

Partner News / 26 Book Beat / 26 Community Bulletin / 29

What’s Wrong and What’s Missing

—Tom Haunert

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

Worthy of Membership

Oracle Technology Network redefines what it means to be a member of the club. —Justin Kestelyn

Architect / 30 Getting Schooled

At Oracle / 12 Events / 12 Find out about upcoming technology and industry events.

Education, training, and experience are stepping stones to a career as a software architect. —Bob Rhubart

Up Close / 32 Global Explorer

Your guide to Oracle Webcasts, podcasts, white papers, and more

OAUG President Mark Clark wants to expand the organization’s reach beyond North America. —Caroline Kvitka

Briefs / 18

Peer-to-Peer / 34

Resources / 15

The latest product news

Interview / 23

Storing the Information Explosion

In with the New

Julien Gabel, Fahd Mirza, Frits Hoogland —Blair Campbell

Scott Tracy, senior director of storage software development at Oracle, talks about Oracle’s storage technologies. —Tom Haunert

Technology / 55

Comment / 84

Oracle Adf / 55

In the Field / 84

Build train models for navigation in bounded task flows. —Frank Nimphius

Stay current, and stay employed: five easy ways for technologists to update their skill sets. —Mike Riley

All Aboard

Oracle data Provider for .NET / 59

Distinctive Database Development

Use Entity Framework with Oracle Data Provider for .NET. —Christian Shay

PL/SQL 101 / 65

Working with Strings

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

Support / 71 Troubleshooting Internal Errors A guide to assessing and resolving ORA-600 and ORA-7445 errors —Tamzin Oscroft

ASK TOM / 74

On Caching and Evangelizing SQL

Our technologist caches scalar subqueries, votes for SQL, and recommends technology and community. —Tom Kyte

SQL 101 / 79 Get Your Information in Order Part 1 in a series on the basics of the relational database and SQL —Melanie Caffrey

September/October 2011 Oracle.com/oraclemagazine

Education Insurance

5

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 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].

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 Advertising sales Associate Publisher Kyle Walkenhorst [email protected] +1.323.340.8585 Northwest and 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. 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 2D Document Print Service

15 Oracle Data Integrator

Java Platform, Enterprise Edition

18 Oracle Data Provider for .NET

Java Platform, Micro Edition

18 Oracle Database

Page Product Name

Page

18, 48 Oracle Real Application Clusters

26, 27

59 Oracle Service Bus 26, 27, 28, Oracle SOA Suite 40, 48, 65, 71, 74, 79

Java Platform, Standard Edition 7

15 Oracle E-Business Suite

JavaFX

18 Oracle Enterprise Manager

15 Oracle Virtual Desktop Client App for iPad

20

JD Edwards EnterpriseOne solutions

28 Oracle Entitlements Server

19 Oracle VM

26

MySQL Cluster

16 Oracle Environmental Accounting and Reporting

21 Oracle VM Server for SPARC

18

MySQL Database

15 Oracle Essbase

18 Oracle VM VirtualBox

84

MySQL Enterprise Edition Oracle Application Development Framework Oracle Application Express

16 Oracle Exadata 18, 26, 55 Oracle Fusion Middleware 15 Oracle GoldenGate

Oracle Argus Safety

16 Oracle Identity Management

Oracle Berkeley DB

18 Oracle JDeveloper

15, 27 Oracle Solaris

16 16, 26, 27

16, 18, 26, 27, Oracle WebLogic Server 28, 48

15, 16, 40

16

19 Oracle WebLogic Suite

15

48 SPARC Enterprise M-Series

26

28 Sun Ray Software 18, 55 Sun Storage F5100 Flash Array

Oracle BPEL Process Manager

16 Oracle On Demand Single Sign-On Service

19 Sun x86 servers

Oracle Business Intelligence Enterprise Edition

18 Oracle Optimized Solution for Enterprise Cloud Infrastructure

20 Sun ZFS Storage Appliance

19 24 40 16, 24

oracle magazine September/October 2011

6

From The Editor

What’s Wrong and What’s Missing

Send us your corrections and your ideas—beginner, advanced, and more.

W

Next Steps LEARN more about Oracle technology Oracle University oracle.com/education Oracle Technology Network oracle.com/technetwork Oracle documentation oracle.com/documentation Oracle Learning Library bit.ly/nlsSWj Oracle OpenWorld oracle.com/openworld JavaOne oracle.com/javaone

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 @oraclemagazine 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.

e run an occasional letters page in Oracle Magazine called From Our Readers. The content is correspondence (e-mails) from readers, along with our responses when appropriate. And while we really don’t mind getting letters that say something positive about a recent article or column, the letters we like most are the ones that point out errors—so we can fix them— as well as the ones that ask for different types of technology content in future issues. Getting the information right and offering the right information in each issue of Oracle Magazine is our goal, and direct reader input is one of our best resources for correcting and planning content.

Beginning and Advancing Beyond asking Oracle Magazine to include content on different technologies, readers also ask for different levels of content. A common letter theme over the years has been the request for more “beginner” content, in all technology areas. Although Oracle Magazine has provided a lot of what we consider beginner technology content, we’ve recently made the presentation of some beginner content a bit more formal. Enter official content for beginners: the 101 series, inspired by the classic numbering of college and university introduction courses. This issue of Oracle Magazine includes Part 3 of Steven Feuerstein’s PL/SQL 101 series (“Working with Strings,” page 65), designed specifically for the beginning PL/SQL developer. This issue also features Part 1 of Melanie Caffrey’s new SQL 101 series (“Get Your Information in Order,” page 79), designed for the beginning database developer or any developer or adminisCONNECT:

September/October 2011 Oracle.com/oraclemagazine

bit.ly/aVgo69

bit.ly/orclmagfb

trator beginning to work with the relational database. We’re working on some other 101 series ideas as well, but if you have an idea for a series, let us know. Of course, not everyone is looking for beginner content. We recently received a letter from a reader of Feuerstein’s PL/SQL 101 series who was looking for more-advanced PL/SQL content. We’re currently exploring ideas for additional advanced technology content, and that letter and others like it are definitely contributing to the conversation. If you have any suggestions for advanced, beginner, or any other level of content, as well as suggestions for any specific technology coverage, send us an e-mail at [email protected]. Other Beginnings, Advancements, and Conferences And finally, the biggest all-around education, training, networking, and community experiences for Oracle technologists are right around the corner. Oracle OpenWorld 2011 comes to San Francisco, California, October 2 to 6, 2011, with information and learning opportunities for all expertise levels, all job roles, and all things Oracle. (See “Engineered for Innovation,” page 8, for details.) JavaOne 2011 comes to San Francisco, California,October 2 to 6, 2011, with information and learning opportunities for all expertise levels, all job roles, and all things Java. (See “Moving Java Forward,” page 10, for details.)

Tom Haunert, Editor in Chief [email protected] twitter.com/oraclemagazine

linkd.in/orclmag

MashUp

News. Views. Trends. Tools.

7

PHOTO APPS

Expand the power of the native camera app that came with your smartphone.

Instagram

Apply any of this app’s custom filters and effects to transform the color, mood, border, and tonality of your photos. Then share the results on Flickr, Facebook, and Twitter. Free (iPhone). instagram.com

Photosynth

“Invention and discovery emanate from being able to try seemingly wild possibilities and work in the unknown; to be comfortable being wrong before being right.”

Don’t just e-mail a picture the next time you travel; send a real postcard, including a personalized message, via snail mail. Free; postcards cost US$0.99 to send anywhere in the world (Android, iPhone). postagramapp.com

Smartphones can tempt us to do dumb things, like texting or e-mailing while driving. The SuperTooth HD, a Bluetooth speakerphone, can help you resist those temptations. It adds voice dialing and other voice commands to your Bluetooth 3.0–equipped phone and lets Android and BlackBerry users send and receive e-mails and texts via speech recognition and synthesis, the artificial production of human speech. (Users install a free app and upload contacts to the SuperTooth service.) SuperTooth HD can also post Facebook updates and tweets via speech recognition, and it has a high-quality speaker for regular old voice calls. Available in Australia, Canada, South Africa, Spain, the U.K., and the U.S., the device “speaks” and understands English, French, and Spanish. US$129, with additional subscription costs after six months’ free speech-to-e-mail service. supertooth.net

INTERVIEW

DON’Ts

13 percent of hiring managers have had an interviewee answer a phone call or reply to a text message during an interview.1

Using a stylus instead of your finger on an iPad or smartphone isn’t some kind of retro affectation for people who miss their old-school PDAs. Styluses help when you need to point, write, or draw with precision, or when you want to use a touchscreen while wearing gloves. (Ever gotten a phone call on a ski slope?) Wacom—a company known for tablets and styluses—has created the Bamboo Stylus, a substantial, stylish, and well-balanced aluminum device with a compact capacitive rubber tip (which duplicates a fingertip) and pocket clip. The company claims the Bamboo’s tip is 25 percent smaller than those of its competitors, for better accuracy, and when paired with an app (such as Wacom’s Bamboo Paper) it makes a great combination for note-taking, presentations, or sketching. US$29.99. wacom.com

Crop, flip, and straighten; correct and enhance colors; add borders and effects; and smooth out grainy photos. Free; paid upgrade unlocks more features (Android, iPhone). phtshp.us/ pDDKam

Auto Text

—Peter Sims, author of Little Bets: How Breakthrough Ideas Emerge from Small Discoveries (Free Press, 2011)

Tipping Point

Photoshop Express Postagram

Make and share panoramic photos on your iPhone. Create a panorama from left to right or from up to down to capture a full “sphere.” Share the panoramic experience on Facebook or publish to Bing Maps. Free. photosynth.net

TRUSTING IN x86

The percentage of mission-critical applications running on x86 servers is growing. In 2010, nearly 200 datacenter workers were asked, “How many of your x86 workloads are mission critical?” Note that some change from 2009 may be because the 2010 sample contained more midsize enterprises. 2009

2010

50%

44%

40%

38%

30%

22%

20% 10% 0%

1%

25%

36% 20%

12%

6%

None of them

25%

25% or less

25% to 50%

50% to 75%

75% or more

Source: Gabriel Consulting Group, gabrielconsultinggroup.com/recent-research/ doc_download/26-survey-surge-in-mission-critical-x86.html

1 Source: Maris Interiors, bit.ly/o4B3mM

ORACLE MAGAZINE SEPTEMBER/OCTOBER 2011

8

Oracle OpenWorld Preview

Engineered for Innovation

Oracle OpenWorld returns to San Francisco, October 2–6.

all is the time when many species begin their annual migrations. For tens of thousands of information technologists, business executives, and developers, the annual migration is to San Francisco, California, for Oracle OpenWorld. To them, Oracle OpenWorld is hands down the most important educational and networking event of the year. It is the only conference that goes deep and wide with Oracle products, with sessions and demos geared toward helping companies get better results with the technology they have—and plan strategically for the technology they will need to keep ahead of the competition in the years to come. “Whether you’re an executive or a developer, you need to be at this conference,” says Oracle President Mark Hurd. “Oracle OpenWorld has become the place to be for advances in engineered systems, cloud computing, and next-generation business applications.” This year Oracle OpenWorld is engineered for innovation, with thousands of technical and birds-of-a-feather sessions, hands-on labs, and more than 400 demos created by and for Oracle business and

technology users. Keynotes from industry leaders including Dell Chairman of the Board and CEO Michael Dell; Infosys CEO Designate SD Shibulal; EMC Chairman of the Board, President, and CEO Joe Tucci; Cisco Chairman and CEO John Chambers; and Oracle CEO Larry Ellison will give you insights into how to drive your business forward. Oracle OpenWorld is your opportunity to learn the latest techniques for optimizing enterprise networks, hear about technology breakthroughs, and meet with the strategists and developers who can help you achieve your business and IT goals. Here is a roundup of the highlights of Oracle OpenWorld 2011. Education Start the week off with a deep dive into Oracle technology with Oracle University, Sunday at 9 a.m. at the Hotel Nikko. Or head to Sunday’s User Group Forum, a full-day program of user-driven content and technology interest group meetings, including sessions run by the MySQL and Java user group communities. Then choose from more than 2,000 technical sessions over the course of the week.

Register now for Oracle OpenWorld oracle.com/openworld/register/packages

Thousands of Oracle developers, technologists, and partners will converge in San Francisco, October 2–6.

September/October 2011 Oracle.com/oraclemagazine

The sessions are broken into six streams that cover the full breadth of Oracle’s products and services: Applications, Database, Industries, Middleware, Oracle Develop, and Server and Storage Systems. Exhibition Halls Explore more than 450 partner and customer exhibits and hundreds of Oracle demos in the Oracle OpenWorld Exhibition Halls, located in Moscone South and Moscone West. The two exhibit halls feature specific themes, and each houses Oracle DEMOgrounds where you can talk to the experts behind Oracle products. Pavilions. The exhibition halls also house a number of themed pavilions that showcase best-in-class solutions and services, cuttingedge technology, and product demos. Pavilion themes include cloud, Linux, customer relationship management, Oracle’s Hyperion and JD Edwards solutions, mobility, project portfolio management, and Oracle Solaris partners. The lighter side. The exhibition halls offer plenty of opportunities to relax between sessions. Play your favorite video games in the Game Zone, or take a swing at the Golf Experience, sponsored by Fujitsu. Drop by the Juice Bar, sponsored by Dell, for an energizing smoothie, or the Tap and Brew, sponsored by Dun and Bradstreet, for a morning espresso or afternoon beer. Network and Unwind It wouldn’t be Oracle OpenWorld without plenty of opportunities to network and unwind. With the opening-day Welcome Reception, Oracle Technology Network’s ever-popular OTN Night, and the final day’s It’s a Wrap!, you’ll have plenty of opportunities for fun. And this year’s attendee-favorite Oracle Appreciation Event won’t disappoint as two music legends take the stage: Sting, and Tom Petty and the Heartbreakers. 

HartmanN Studios

F

10

JavaOne Preview

Moving Java Forward

JavaOne hits the streets of San Francisco, October 2–6.

hen thousands of Java developers and enthusiasts converge on San Francisco, California, from October 2 to 6, you know it can only be for the iconic JavaOne conference. Now in its 16th year in the city, JavaOne is a week of intense learning, networking, and community committed to all things Java. As always, JavaOne offers something for every member of the Java community—from Java newbies to seasoned Java developers. Conferencegoers can expand their skills and gather tips and tricks from experts by participating in technical sessions, hands-on labs, and demos, as well as informal birds-of-afeather sessions. “Oracle continues to demonstrate its commitment to moving Java forward,” says Hasan Rizvi, senior vice president of product development at Oracle. “The 2011 JavaOne conference offers attendees a unique opportunity to hear from Java technology experts— from both inside and outside of Oracle—on topics ranging from the core Java platform to mobile, JavaFX, and on to the Java frontier.” Education and Keynotes Whether developers want to build the latest client-side applications or create sophisticated Java cloud-based applications, they can find out how at JavaOne. JavaOne learning opportunities include classes at Java University on Sunday, October 2, with fullday and half-day session options. The deep dive continues on Monday as the conference gets into full swing with more than 400 sessions across seven tracks, including Core Java Platform; Emerging Language, Tools, and Techniques; Enterprise Service Architectures and the Cloud; Java EE Web Profile and Platform Technologies; Java ME, Mobile, Embedded, and Devices; Java SE, Client Side Technologies, and Rich User Experiences; and the Java Frontier. In addition to participating in sessions,

Register now for JavaOne oracle.com/javaone

JavaOne heats things up in San Francisco, October 2–6.

conferencegoers can attend hands-on labs throughout each day and into the evenings, and birds-of-a-feather sessions on Monday and Tuesday nights. JavaOne offers many opportunities to hear from Java luminaries about what’s new and what’s next for Java. Not to be missed are the JavaOne Oracle and Intel keynotes on October 3, the JavaOne technical and Juniper keynotes on October 4, and the IBM and Java Frontier keynotes on October 6. Java Exhibition Hall The crowds will head to the Java Exhibition Hall for partner and Oracle hands-on demos and discussions of the latest Java software technologies. Exhibitors will showcase their products and services, from developer tools and frameworks to management utilities, applications, and services developed with and for Java. At the Java DEMOgrounds, attendees can talk with the experts behind Java products and technologies. Building Networks and Communities Sometimes it’s not what you know, but who you know. JavaOne can help attendees build their own Java community, with

September/October 2011 Oracle.com/oraclemagazine

plenty of opportunities for networking. For user group veterans as well as those new to JavaOne, things start off with the Oracle Users Forum on October 2 and continue that day with the first official celebration, the JavaOne Open House. Much of the networking activity will take place at the Mason Street Café in the heart of the Zone, which also includes the Hilton San Francisco Union Square, Hotel Nikko San Francisco, and Parc 55 Wyndham. The Café is a block-long playground and community space where developers can refuel between sessions; recharge themselves and their electronic devices; and renew acquaintances with Java user group leaders, Java Champions, and fellow developers. On Wednesday, the action moves to San Francisco’s Treasure Island for the Oracle Appreciation Event, with performances by Grammy Award–winning musicians Sting, and Tom Petty and the Heartbreakers. This attendee-favorite event offers hands-on entertainment, unlimited refreshments, and world-class music. And for one last hurrah, conference attendees can head to Yerba Buena Gardens on Thursday for the final event of JavaOne: the It’s a Wrap! celebration. 

HartmanN Studios/Ron Sellers

W

12

Events

Technology Events

Conferences and sessions to help you stay on the cutting edge

Oracle User Groups Paris Java User Group Meetings

September 13 and October 11, Paris, France

parisjug.org

UKOUG Security Special Event

September 13, Bletchley Park, England

Oracle OpenWorld

October 2–6, San Francisco, California Oracle OpenWorld presents the world’s largest, most in-depth selection of sessions by Oracle experts, customers, and partners. This year’s streams include Applications, Database, Industries, Middleware, Server and Storage Systems, and Oracle Develop, with tracks that span solution and product areas. Get details and register at oracle.com/openworld.

www.ukoug.org

Java Metroplex Users Group Meetings

September 14 and October 12, Dallas, Texas

javamug.org

Kansas City Java User Group Meetings September 14 and October 12, Leawood, Kansas

kcjava.org

Belgium Java User Group Seminar on Data Grids September 15, Leuven, Belgium

bejug.org

Integrasjonsdagene (Integration Week) 2011 September 1–2, Halden, Norway fbit.ly/ivhm8b The largest event in Scandinavia focused on middleware integration solutions features realworld case studies and tracks for IT managers and Oracle Fusion Middleware and Java professionals. It covers new-platform introduction, development methods, SOA, and security.

JCertif 2011 September 3-4, Brazzaville, Congo fjcertif2011.eventbrite.com JCertif is the biggest Java community event in central Africa. It includes presentations on Java technology and mobile applications, with topics ranging from server-side Java and open source to rich-client and cloud technology.

JavaZone 2011 September 7–8, Oslo, Norway fjz11.java.no For software developers in Scandinavia, this independent, volunteer-run conference attracts more than 2,000 attendees and more than 30 corporate partners, who participate in more than 100 presentations totaling some 200,000 hours of information sharing.

AIIM Social Business Virtual Conference September 8, online faiim.org/events/soe-conference Sponsored by the Association for Information and

Image Management (AIIM), this one-day virtual conference focuses on how organizations can use social technologies to engage staff and customers while maintaining appropriate controls.

QCon September 10–11, São Paulo, Brazil fqconsp.com For team leads, architects, and project managers, this international software development conference is organized by the community, for the community.

International Conference on Infocomm Technologies in Competitive Strategies September 12–13, Singapore fict-conference.org Colocated with GreenIT 2011, this conference provides academics, researchers, technology professionals, and businesspeople with an opportunity to share their research. Conference topics include science, technologies, regulations, and industry initiatives on algorithmic efficiency, resource allocation, product longevity, virtualization, power management, materials recycling, and telecommuting.

Hacktivity 2011 September 17–18, Budapest, Hungary fhacktivity.com Topics at the largest hacker conference and festival in central and eastern Europe include Oracle forensics, SAP security, database security, mobile hacking, lock picking, and WiFi

September/October 2011 Oracle.com/oraclemagazine

Dallas Oracle Users Group Meetings September 15, September 16, and October 20, Dallas, Texas

doug.org

Northeast Ohio Oracle Users Group Meeting

September 16, Independence, Ohio

neooug.org

Suncoast Oracle User Group Meetings

September 22 and October 27, Tampa, Florida

soug.org

Austin Java Users Group Meetings

September 27 and October 25, Austin, Texas

austinjug.org

St. Louis Oracle Users Group Meeting October 13, St. Louis, Missouri

sloug.org

Greater Cincinnati Oracle User Group Meeting October 19, Blue Ash, Ohio

gcoug.org

Heartland Oracle Users Group Fall Conference October 20, Omaha, Nebraska

heartlandoug.wordpress.com Ohio Oracle Users Group Meeting October 20, Dublin, Ohio

ooug.org

FORTUNE Oracle Technology and Applications Forum by Quest Asia October 24, Hong Kong

questdirect.org/events/2398182 Southern New England Regional User Group Meeting October 28, Farmington, Connecticut

questdirect.org/events/2372063

The MBA

that goes where

you go...

Complete your MBA degree Online from anywhere in the world ■ Ranked “Top 10” Best Part-Time MBA Program by BusinessWeek ■ 37 credit program ■ No on-campus visits required ■ 24 x 7 access to course materials ■ Start Now - take 2 courses before applying ■ Courses taught by Graduate Faculty ■ AACSB Accredited ■ Quality education at an affordable price

MBA

isenberg.umass.edu/mba

14

Events

JavaOne

October 2–6, San Francisco, California Java experts and enthusiasts convene for a week of learning and networking. Java-specific content tracks include Core Java Platform; Emerging Languages, Tools, and Techniques; Enterprise Service Architectures and the Cloud; Java EE Web Profile and Platform Technologies; Java ME, Mobile, Embedded, and Devices; Java SE, Client Side Technologies, and Rich User Experiences; and the Java Frontier. Get all the details and register at oracle.com/javaone.

worms. The target audience is security, IT, and networking professionals including chief information security officers, chief security officers, and CIOs.

offers a thorough approach to master data management (MDM) and the various ways to implement it. Discussions include recent MDMrelated acquisitions by Oracle.

Sibos

Oracle Health Sciences User Group Annual Conference

September 19–23, Toronto, Ontario, Canada fsibos.com Leaders from financial institutions, market infrastructures, multinational corporations, and their technology partners gather at this annual conference. Topics include regulation revisited; technology; changing landscape, new expectations; and global and local perspectives.

Business Analysis Conference Europe September 26–28, London, England firmuk.co.uk/ba2011 Intended for business analysts from across Europe, this event provides an interactive forum to discuss and debate the competencies needed to face today’s business analysis challenges.

Economic Times Asia ITEX October 7–9, Mumbai, India fasiaitex.com Asia’s largest IT exhibition attracts more than 35,000 visitors and 300 exhibitors from around the globe. Attendees learn about new suppliers, products, and services; keep abreast of changes in the industry; and meet key decision-makers in government, manufacturing, and services.

Seminar on Master Data Management Fundamentals and Best Practices October 11, Hilversum, Netherlands fbit.ly/mGRORS Highlighting business problems that result from poorly managed data, this seminar

October 16–19, Toronto, Ontario, Canada fbit.ly/lJ97Y8 This conference features presentations on the technical and functional aspects of Oracle Health Sciences applications. Clinical, pharmaceutical, biotechnology, medical device, and other companies will present and participate in focus groups, and Oracle representatives will update attendees on current and planned features.

Gartner Symposium ITxpo October 16–20, Orlando, Florida fbit.ly/qm8wp4 With the theme “e-imagine IT: Leading from the front,” this event has three goals: to be personally relevant for attendees, to stimulate their thinking, and to leave them with an action plan for the coming year.

Croatian Oracle User Group Conference October 18–22, Rovinj, Croatia fwww.hroug.hr This regional conference includes information for users of Oracle technology and applications. More than 500 participants will exchange best practices and share their experiences with a variety of Oracle business solutions.

Oracle Applications Strategy Day October 20, Madrid, Spain fibevents.com/oracle Learn how entrepreneurial companies use Oracle applications to generate business opportunities,

September/October 2011 Oracle.com/oraclemagazine

cultivate loyalty among their customers, manage talent, make decisions efficiently, and optimize business performance.

Project Management Institute Global Congress October 22–25, Dallas/Fort Worth, Texas fpmi.org This gathering offers professional development, networking, and information sharing for project management practitioners around the world. Presentation topics include project management issues and challenges.

Property Casualty Insurers Association of America Annual Meeting October 23–26, New Orleans, Louisiana fregistration123.com/pci/11annual This annual event for property casualty insurance professionals in North America provides perspective on issues facing their industry.

GITA’s GIS for Oil and Gas Pipeline Conference October 24–27, Houston, Texas fbit.ly/iRnfO2 For geospatial professionals from leading oil and gas pipeline industry organizations, this conference includes in-depth seminars, technical presentations, discussion forums, and a product exposition, all designed to help geographic information system users access information and increase efficiency.

New Zealand Oracle Users Group (NZOUG) Conference October 31–November 1, Auckland, New Zealand fbit.ly/p14bLC The NZOUG Conference is an Oracle event held in New Zealand for New Zealanders. Attendees network with their peers, discuss their current work projects with others in their specific fields, and acquire helpful information and contacts.

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

Resources

What’s New at Oracle

The latest videos, podcasts, blogs, and more

VIDEOS Moving Java Forward: A Video Discussion about Java 7 fbit.ly/ny86XP Java development team members Joe Darcy, Adam Messinger, Mark Reinhold, and John Rose discuss key ways that Java is moving forward with the release of Java 7. TechCast Live: Oracle Solaris Optimizations for x86 Hardware fbit.ly/nvtafo Chris Baker, principal product manager for Oracle Solaris, explains the optimizations for x86 hardware provided by Oracle Solaris, and how developers and sysadmins can take advantage of them. Belgium’s Federal Government CTO Talks Oracle WebLogic Suite fbit.ly/rig2r4 Hear from Peter Strickx, Belgium’s federal government CTO, about how Oracle WebLogic Suite serves as the mission-critical platform for all Java applications, including Belgium’s federal portal and critical police operations. Oracle WebLogic: Vital for Obopay’s Mission-Critical Infrastructure fbit.ly/n3otky John Tumminaro, cofounder and CTO of mobile payments company Obopay, discusses how Oracle WebLogic and Oracle JRockit provide resilience, performance, and scalability in a developerfriendly environment.

PODCASTS Rob Shapiro on Collaboration fbit.ly/nkkvwH Rob Shapiro, senior director for business social media at Oracle Proactive Support, explains how collaboration increases productivity and gets answers quickly. AutoVue AppCast fbit.ly/qsQ28B Hear how Oracle partner Estuate has integrated

Oracle’s AutoVue 2D Document Print Service and Oracle E-Business Suite’s Oracle Enterprise Asset Management to deliver robust printing. Three Authors on Oracle Application Express 4 fbit.ly/nQA6dc Patrick Cimolini, Martin D’Souza, and Doug Gault talk about their book for developers new to Oracle Application Express, Beginning Application Express 4 (Apress, 2011). Oracle Solaris Security Benefits fbit.ly/om1ZtE Oracle Solaris experts discuss security in today’s IT environment and the challenges in balancing security with ease of use.

WEBCASTS Discover the Cloud Advantage for Databases Webcast Series fbit.ly/jYKNI9 Learn how to achieve maximum performance and value from databases in private clouds. Topics in this series include security, storage management, data integration, database consolidation, database management, and availability. Oracle Enterprise Storage Webcast Series fbit.ly/qrDZbg In this Webcast series, see how Oracle’s portfolio of storage products and business-ready storage solutions optimize performance, maximize data protection, and reduce the total cost of ownership. Mastering Information Architecture fbit.ly/nqzUxt Real-life case studies show how to modernize business practices and incorporate data quality, security, and transparency into the design of sustainable information architectures. Extreme Solaris Virtualization Performance, with Features that Can’t Be Matched fbitly.com/Xtremev12n Learn about Oracle Solaris’ built-in virtualization

capabilities and how to consolidate your existing environment for greater efficiency. MySQL Essentials Part 8: MySQL Server and SQL Performance Tuning fbit.ly/oI94DW Learn how to design and optimize a MySQL database server for maximum performance and scalability, and explore performance optimization possibilities and practices. Oracle Enterprise Manager 11g Webcast Series fbit.ly/pFvHSN This Webcast series covers preventing configuration drift, improving database performance,

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 SEPTEMBER/OCTOBER 2011

15

16

Resources

overHEARD

“By using Oracle WebLogic Suite, we reduced the install times [and] the deployment times from five days to approximately four hours, and it’s become very easy to add more nodes in the cluster and to scale out.” —Peter Strickx, CTO of Belgium’s Federal Government, in the Belgium’s Federal Government CTO Talks Oracle WebLogic Suite video (bit.ly/rig2r4)

maximizing ROI, and other issues that Oracle Enterprise Manager addresses. MySQL Enterprise Hot Backup for Your Products fbit.ly/q6GE8s Learn about the tools the MySQL Enterprise Backup feature of MySQL Enterprise Edition provides to safely and rapidly back up and restore MySQL embedded databases. Five Reasons to Upgrade to Oracle WebLogic Server 11g fbit.ly/pumfbJ Mike Lehmann, senior director of product management for Oracle WebLogic Server, shares best practices and tips for a fast, low-risk upgrade to Oracle WebLogic Server 11g. No Contest: Oracle WebLogic Outperforms IBM WebSphere fbit.ly/nmfI0x See how Oracle WebLogic outperforms IBM WebSphere, including in TCO and performance. Oracle Argus Safety fbit.ly/qFqtC6 Oracle Health Sciences and BioPharm Systems present a Webcast on Oracle Argus Safety for IT professionals and safety managers in the pharmaceutical industry.

WHITE PAPERS

survey reveals how today’s DBAs are managing a surge in database growth and provides recommendations to help deal with that growth. A Comparison of Application Performance Management Suites from CA, HP and Oracle fbit.ly/rjkuaD This Crimson Consulting Group study compares three application performance management suites and finds that Oracle’s offering provides advantages in traditional performance monitoring, availability monitoring, and businessdriven IT management.

E-BOOKS Unified Storage For Dummies, Oracle Special Edition fbit.ly/r67y81 Conquer data storage challenges and learn more about Oracle’s Sun ZFS Storage Appliance, a cost-effective, easily managed storage solution.

TRAINING Training for Oracle Solaris 11 foracle.com/education/solaris11 Oracle University training on Oracle Solaris 11 gives you early access to new features and capabilities to build, deploy, and maintain cloud systems. This training can help you run enterprise IT environments efficiently, with greater availability and higher security.

Unlocking New Value from Web Session Management fbit.ly/pw2NnT This white paper features code samples that show the challenges and solutions related to session management with MySQL Cluster.

Oracle University at Oracle OpenWorld 2011 fbit.ly/mUhikq Maximize your Oracle OpenWorld experience with Oracle University’s one-day training event on Sunday, October 2, 2011, in San Francisco, California. Eight deep-dive training sessions cover popular Oracle technology topics.

IOUG Survey on Database Manageability fbit.ly/q3A93Q This Independent Oracle Users Group (IOUG)

Java University at JavaOne 2011 fbit.ly/oLhWL8 Java University is a one-day training event held

SEPTEMBER/OCTOBER 2011 Oracle.com/oraclemagazine

on Sunday, October 2, 2011, as a kickoff to the JavaOne conference in San Francisco, California. In-depth sessions taught by industry experts cover hot developer topics.

RESOURCE CENTER SOA Resource Center fbit.ly/oddrjn Visit this resource center for white papers, data sheets, and podcasts on Oracle SOA Suite, Oracle Service Bus, Oracle BPEL Process Manager, and other tools that deliver SOA solutions.

REFERENCES AND CALCULATORS Oracle Database 11g Interactive Quick Reference fbit.ly/pxretV Learn to write custom data dictionary scripts, locate views pertinent to a specific database component, and more. Sun ZFS Storage Appliance Savings Calculator fbit.ly/pziIO7 Find out how to increase storage capacity, boost efficiency, and reduce costs with Oracle’s Sun ZFS Storage Appliance savings calculator. Customers Discuss Oracle Exadata fbit.ly/mZSGbf Read about the results that 20 organizations have achieved with Oracle Exadata.

web locator 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/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.

18

Product Resources

Tutorials Oracle Real-World Performance Video Series: Migrate a 1 TB Data Warehouse in 20 Minutes

In this four-part video series, learn how to migrate a 1 TB data warehouse to an Oracle Exadata Database Machine. fbit.ly/oECEJY

Getting Started with the Oracle JDeveloper IDE

Learn how to build a basic application using the major components of Oracle JDeveloper. fbit.ly/nrNMnR

Developing Ajax-Based User Interfaces with Oracle ADF Faces Rich Client Components

Use Oracle Application Development Framework JavaServer Faces Rich Client components to create Ajax-based Web applications. fbit.ly/ridjDi

Integrating Oracle Essbase 11.1.2.1 with Oracle Business Intelligence Enterprise Edition 11g

Learn how to integrate Oracle Essbase with Oracle Business Intelligence Enterprise Edition, including connecting to the Administration Tool, importing the Essbase BISAMPLE cube into the physical layer of the repository, and building the business model and the subject area. fbit.ly/oWapQI

Oracle Data Integration 11g: New Features

This tutorial introduces you to the new and enhanced features of Oracle Data Integrator 11g, and covers the business and technical benefits of these features. fbit.ly/qm1hVS

Getting Started with Java ME

This three-part video series helps you get started using the Java Platform, Micro Edition Software Development Kit (SDK), from downloading and installing to creating your own applications. fbit.ly/nli3jV

DOWNLOADS New Downloads

Oracle JDeveloper 11g (11.1.2.0.0) fbit.ly/r4CYAb JavaFX 2.0 Beta SDK fjavafx.com/downloads/all.jsp Oracle Berkeley DB fbit.ly/nyFSEn

Oracle Upgrades Oracle VM Server for SPARC Oracle has released Oracle VM Server for SPARC 2.1, a server virtualization solution that now allows as many as 128 virtual machines on one system. It comes preinstalled on Oracle’s SPARC T-Series servers, including SPARC T3, UltraSPARC T2+, and UltraSPARC T2–based servers. Oracle VM Server for SPARC 2.1 adds point-and-click live migration capabilities, so companies using Oracle Enterprise Manager Ops Center can migrate running virtual machines from one physical server to another. The new release also includes enhancements in virtual device service validation, dynamic reconfiguration of cryptographic units, and the management information base. “Oracle VM Server for SPARC demonstrates the tremendous benefits of hardware

and software engineered to work together,” says Bill Nesheim, vice president of Oracle Solaris engineering. “This release offers customers a new level of flexibility in efficiently deploying enterprise workloads on the SPARC T-Series family of systems and demonstrates Oracle’s ongoing commitment to innovation in SPARC and Oracle Solaris.” bit.ly/qHLzQN

Java EE Development Tools Get Updates Oracle has extended its tools for Java Platform, Enterprise Edition (Java EE), SOA, and Web 2.0 application development with updates to Oracle JDeveloper 11g and Oracle Application Development Framework 11g (Oracle ADF 11g). Oracle JDeveloper 11g Release 2 features an enhanced integrated development environment. Notably, the extension framework has been upgraded to use OSGi, which allows for lazy loading of extensions and results in faster startup time and improved overall performance. Oracle JDeveloper 11g Release 2 also supports new technologies and standards, including Maven 2, JavaServer Faces 2.0, RESTful Web services, and Hudson. The update to Oracle ADF 11g includes support

for new JavaServer Faces 2.0 and Facelets standards, updated Oracle ADF JavaServer Faces Rich Client components, support for interactive thematic maps, and infrastructure changes to facilitate OSGi tooling. “Web application and Java developers continue to seek the latest advancements in standards and easy-to-use tools,” says Ted Farrell, chief architect and senior vice president at Oracle. “Oracle JDeveloper 11g Release 2 delivers state-of-the-art capabilities that allow developers to achieve an immense productivity boost and are essential to enable the creation of the mostimmersive and -vibrant Web applications.” bit.ly/qqGsSH bit.ly/n62aJf

Oracle Buys Pillar Data Systems Oracle has added to its storage portfolio by acquiring Pillar Data Systems, a provider of scalable storage area network (SAN) block I/O storage systems. The addition of Pillar’s products is expected to help Oracle deliver a complete line of storage products. Among Pillar’s products is the ApplicationAware Pillar Axiom 600 storage system, which combines high performance with the ability for administrators to assign storage resources based on business requirements.

September/October 2011 Oracle.com/oraclemagazine

Founded in 2001, Pillar Data Systems has nearly 600 customers in 24 countries. “The acquisition provides Oracle with a compelling SAN storage architecture that complements our core strengths,” says John Fowler, executive vice president of systems at Oracle. “Customers can optimize the value of their Oracle applications, database, middleware, and operating system software by running on Oracle’s storage solutions.” oracle.com/pillar

BRIEFS

Oracle Entitlements Server 11g Now Available Oracle has released Oracle Entitlements Server 11g, a component of Oracle Fusion Middleware 11g that delivers a scalable, externalized authorization management solution for applications, middleware, and databases. Oracle Entitlements Server 11g provides developers with shared services for fine-grained authorization, which can deliver quicker compliance and better business agility because authorization policies can be adapted as market, regulatory, and business requirements change. Oracle Entitlements Server 11g is integrated with Oracle Platform Security Services, the security foundation for Oracle Fusion Middleware and Oracle Fusion Applications. It supports security standards including eXtensible Access Control Markup Language (XACML), National Institute of Standards

and Technology Role-Based Access Control (NIST RBAC), and Java Authentication and Authorization Service (JAAS). Other highlights include real-time external authorization, rapid application integration, rich policy modeling, an autonomous security module, and scalable policy stores. “Externalizing authorization and application entitlements to create a declarative security model is the next wave of innovation that will help customers reduce risk and enable sustainable security compliance,” says Amit Jasuja, vice president of Oracle Identity Management. “We worked closely with the Oracle Applications teams and large customers to provide high transaction performance and standards flexibility with Oracle Entitlements Server 11g.” bit.ly/qkmjMk

Oracle On Demand Adds SSO to Its Identity Management Services Oracle On Demand has added a single-signon (SSO) service to its identity management services portfolio. Powered by Oracle Access Manager, Oracle On Demand Single Sign-On Service provides Web-based SSO to applications hosted by Oracle On Demand. The service can be combined with Oracle On Demand Strong Authentication Service to provide additional security.

“Oracle On Demand Single Sign-On Service improves productivity by making it easier for users to log in, reduces help desk costs, and provides a platform for additional security services such as strong authentication,” says Marc-Oliver Fiedler, vice president of business strategy and planning for Oracle On Demand. bit.ly/qRDV3w

Oracle Enhances Virtual Desktops with New Sun Ray Software Oracle has unveiled Sun Ray Software 5.2, a new release of its server-hosted desktop virtualization offering. The new release enables easier installation and configuration and features improved multimedia, networking, and smart card support. Sun Ray Software’s improved integrated installer allows the software to be downloaded and deployed in one easy-to-install package. The software enables users to access virtual Windows, Linux, and Oracle Solaris desktop environments through any combination of Sun Ray clients, PCs, or Macs and interoperates with other virtualization technologies and

desktop brokers. Its improved audio and video support includes an optimized Windows Media Player, USB headset support for specific devices, and Appliance Link Protocol for optimized audio. The new release can also use Oracle Solaris IP multipathing for network fault tolerance and load-spreading and has extended virtual private network options. “With Sun Ray Software, IT can centralize the management and maintenance of thousands of thin clients—reducing cost and complexity, while enhancing security,” says Wim Coekaerts, senior vice president of Linux and virtualization engineering at Oracle. “Innovations in Sun Ray Software 5.2 make it even easier to install and update, while delivering an improved enduser audio and video experience.” bit.ly/nKp5c5

Oracle Introduces Changes to the Java Community Process Oracle has submitted the first of two Java Specification Requests (JSRs) to update and revitalize the Java Community Process (JCP), the group of more than 1,200 individuals and corporations who together define Java standards and drive the Java language and platforms. The new JSRs define the next iteration of the JCP, called JCP.next, and will broaden transparency of Java technology development, improve procedures, increase agility, and encourage community participation. The proposed JSRs would modify the JCP’s Process Document and Java Specification Participation Agreement (JSPA) and would apply to all new JSRs and to future maintenance releases of existing JSRs for all Java platforms. The first submission, JCP.next JSR 1, or JSR-348, will produce a new version of the JCP Process Document and a formalized executive committee members’ guide and is expected to be completed and approved within approximately six months. The changes include requiring all expert group operations to be carried out in public forums, increasing the transparency of the recruiting process for expert group members, exploring ways to enable all JCP members to participate in executive committee activities, and determining how to disclose Technology Compatibility Kit testing results. JCP.next JSR 2, which Oracle will submit to the JCP in the near future, will involve modifications to the JSPA. JCP.next JSR 2 will go through the same JSR development process as Java technologies and will be voted on by the JCP Executive Committee in a process that is expected to take 12 to 18 months. “In the past few months, Oracle and other partners in the Java Community Process have driven a revitalization of Java technology and standards with the approval of the JSRs for Java Platform, Standard Edition 7 and 8, as well as Java Platform, Enterprise Edition 7,” says Patrick Curran, chair of the JCP and director of the JCP’s Program Management Office. “Now with the submission of the first JSR for JCP.next, we are ready to begin the evolution of the Java Community Process itself, making it easier for both individuals and organizations to participate in the process.” jcp.org

ORACLE MAGAZINE SEPTEMBER/OCTOBER 2011

19

20

BRIEFS

Oracle Proposes Placing Hudson Under Eclipse Foundation Oracle has submitted a proposal to make Hudson an Eclipse Foundation project. Under the proposal, Oracle would contribute the Hudson core code to the project and transfer the Hudson trademark and the hudson-ci.org domain name to the Eclipse Foundation. Oracle would be the project lead, with Sonatype, Tasktop, and VMware as initial contributors and other companies as project supporters. Hudson is an industry-leading open source continuous integration server. It coordinates and monitors execution of repeated jobs, making it easier for developers to integrate changes to projects and

for users to obtain fresh builds. To ensure that Oracle’s proposal is in the best interests of the developer and open source communities, Oracle will solicit feedback from the Hudson community before the proposal is finalized. “Hudson has become an important tool for many software developers and is widely used within the Eclipse community,” says Mike Milinkovich, executive director of the Eclipse Foundation. “We believe Oracle’s proposal to make Hudson an Eclipse project is an important next step in expanding the adoption and usage of Hudson.” bit.ly/q04V0L

Oracle Creates Solution for Enterprise Clouds Oracle has unveiled the Oracle Optimized Solution for Enterprise Cloud Infrastructure, an integrated and tested solution for enterprise infrastructure-as-a-service (IaaS) cloud deployments. The new solution combines Oracle’s Sun Blade servers, Oracle Solaris or Oracle Linux, Oracle’s Sun ZFS Storage Appliance (pictured), and Oracle VM, and can be managed and supported as a single system using Oracle Enterprise Manager 11g.

Oracle Acquires FatWire Software Oracle has acquired FatWire Software, a provider of Web experience management solutions. FatWire enables organizations to build and deploy a targeted and interactive online presence that optimizes interactions with customers. FatWire helps organizations to deliver relevant customer content, build community engagement, and drive site stickiness and loyalty. FatWire solutions complement Oracle Fusion Middleware and Oracle’s business intelligence, enterprise content management, and portal technologies, as well as Oracle’s customer relationship management and Oracle ATG Web Commerce applica-

tions. The acquisition of FatWire will support Oracle customers that seek to build a unified customer experience across Web, mobile, and social channels. “FatWire solutions help organizations drive customer engagement and loyalty by offering a targeted and interactive online experience across Web and mobile channels,” says Yogesh Gupta, president and CEO of FatWire Software. “This acquisition is expected to provide FatWire customers with dramatically expanded products and services to help them achieve their online customer experience goals.” oracle.com/fatwire

Oracle Unveils Virtual Desktop Client App for iPad Oracle Virtual Desktop Client App for iPad is now available on Apple’s App Store. This app enables users to securely access virtual desktops managed by Oracle’s Sun Ray Software and Oracle Virtual Desktop Infrastructure. The app offers simple, intuitive, and accurate gesture-based and multitouch operation with rich virtual keyboard support. Oracle Virtual Desktop Client App for iPad supports a broad range of enterprise applications, including network-intensive applications and those requiring full desktop browsers. For example,the app provides a high-

performance mobile computing environment for users of Oracle’s Siebel Customer Relationship Management. “With the introduction of Oracle Virtual Desktop Client App for iPad, we continue to offer customers innovative ways to access a broad range of virtual desktop environments and enterprise applications,” says Wim Coekaerts, senior vice president of Linux and virtualization engineering at Oracle. “When used in conjunction with Oracle’s Sun Ray Software and Oracle Virtual Desktop Infrastructure, customers benefit from enhanced security, centralized administration, lower cost, and better access to their corporate data from iPad.” itunes.com/appstore

SEPTEMBER/OCTOBER 2011 ORACLE.COM/ORACLEMAGAZINE

Oracle Consulting provides assessment, architecture, and implementation services for the new IaaS solution, and Oracle Advanced Customer Support Services provides production readiness, installation, mission-critical support, and preventive monitoring services. The Oracle Optimized Solution for Enterprise Cloud Infrastructure is endorsed by the Intel Cloud Builders program, a cross-industry initiative aimed at making it easier to build, enhance, and operate cloud infrastructures. “Oracle is radically simplifying cloud deployment with a pretested, single-vendor solution for enterprise cloud infrastructure,” says Ali Alasti, vice president of hardware development at Oracle. “By engineering our hardware and software together, the Oracle Optimized Solution for Enterprise Cloud Infrastructure cuts deployment time from weeks to hours and helps customers get virtualized infrastructure up and running faster.” bit.ly/pwdkJ2

21

Oracle Environmental Accounting and Reporting Debuts

Oracle Buys Datanomic

Oracle has released Oracle Environmental Accounting and Reporting, an extension to Oracle Financials and Oracle’s JD Edwards EnterpriseOne Financials that enables organizations to track greenhouse gas emissions and other environmental data in order to improve environmental and financial performance. Oracle Environmental Accounting and Reporting facilitates reporting in both voluntary and legislated emissionsreporting situations. In addition to working with existing enterprise resource planning applications, Oracle Environmental Accounting and Reporting uses Oracle business intelligence solutions to provide immediate insight into environmental data. Because Oracle Environmental Accounting and Reporting integrates with existing financial and business processes and solutions, it can increase efficiency and reliability in environmental data collection and reporting. Its support

Oracle has acquired Datanomic, a provider of enterprise customer data quality software and related applications for watch list compliance screening. Datanomic’s products support data quality and compliance requirements of large corporations in the financial services, retail, insurance, telecom, and utility industries. Datanomic’s products will become part of Oracle’s data integration and master data management solution. “Integrated data quality management has become essential to delivering successful information management initiatives,” says Hasan Rizvi, senior vice president of Oracle Fusion Middleware product development. “With the addition of Datanomic, Oracle will provide an integrated approach to enterprisewide data quality including purposebuilt capabilities for the major datatypes of customer, supplier, and product data as well as for watch list screening.” oracle.com/datanomic

for multiple reporting standards, shorter reporting cycle times, internal key performance indicator tracking, and flexible ad hoc reporting allows organizations to improve both environmental and financial performance. In addition to Oracle Environmental Accounting and Reporting and other Oracle risk and performance management products, Oracle provides a comprehensive set of solutions to help manage IT infrastructure and business operations from an environmental and cost perspective. “Besides increasing your environmental data collection efficiency and reliability, Oracle Environmental Accounting and Reporting enables you to comply with global greenhouse gas regulations and provides actionable business and environmental insights,” says Jon Chorley, vice president of product strategy at Oracle. bit.ly/qQ3Gb6 oracle.com/green

Every

69 SECONDS a woman DIES of breast cancer.

Susan G. Komen for the Cure® is working to change this. Last year alone we funded more than 600,000 breast screenings. We helped 100,000 people financially through treatment. We educated 4 million about breast cancer. We invested more than $60 million in breast cancer research. And we did it in more than 50 countries around the world. Komen for the Cure is the only organization fighting breast cancer on every front.

Don’t wait another 69 seconds to save a life. We’re making progress, but there’s much more to do, and we need your help.

Learn how to help today. Visit komen.org. This space provided as a public service. ©2011 Susan G. Komen for the Cure ® The Running Ribbon is a registered trademark of Susan G. Komen for the Cure.

Komen-69Sec-Oracle-HalfPg.indd 1

Find us 6/23/11 4:18 PM

oracle magazine September/October 2011

6 O

2-

SC

R

AN

CI

BE FR

TO N

OC

SA

REGISTER NOW

Save $200 by Sept. 30th 2,000 Sessions | 450 Exhibitors | 400 Demos

Register at oracle.com/openworld

Marquee Sponsor

Diamond Sponsors

Premier Sponsors

Elite Sponsor

Grande Sponsors

Copyright © 2011, 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.

Interview by Tom Haunert

Storing the Information Explosion

How Oracle’s flash, disk, and tape storage technologies help IT shops handle today’s unique storage challenges

Bob Adler

T

om Haunert, Oracle Magazine editor in chief, recently sat down with Scott Tracy, senior director of storage software development at Oracle, for an in-depth discussion about Oracle’s flash, disk, tiered storage, and unified storage technologies. The following is an excerpt from that interview. Download the full podcast at oracle.com/magcasts. Oracle Magazine: Beyond the basic demands of storage, what are the main challenges organizations are facing today with the information explosion and the storage required to manage it? Tracy: There are a number of things IT shops are facing. Certainly, data management and migration activities: as platforms get older, they get slower, or they run out of service life. The act of replacing that storage takes a substantial amount of time and activity. Then there are the cost constraints people are under today: the number of personnel required to actually manage the IT shops, and obviously the cost of the storage devices themselves, as well as servers for those devices. Problem-resolution time is a big deal too. As you get more and more storage in your datacenter—more and more machines— problems become more complicated. They take longer to figure out, and IT shops are looking for ways to get around that. Then there’s data protection and recovery—just making sure that you’ve got adequate copies and backups of data, and that those copies and backups can be restored in case of disaster, in a recovery time that makes sense for your business. Finally, there’s security. Every day or every week, it seems like some business has been breached. And so, whether it’s

The Sun Storage F5100 Flash Array . . . performs the same as 3,000 disks, but uses 0.15 percent of the space. security in the form of encryption on devices so that nobody can peek in to see the data, or it’s just physical security where people just can’t get to the backbone of your datacenter, everybody has some implementation of security. We need to keep that in mind as we’re designing new storage. Oracle Magazine: What are some of the key strategies in dealing with these storage challenges?

Scott Tracy, Senior Director of Storage Software Development, Oracle

Tracy: Certainly consolidation and reduction —the act of taking data from multiple older storage devices and putting it into singular newer storage devices is a hardware solution. From the software perspective, there are newer technologies like compression, where you can squeeze out a 2-to-1 reduction of data, and deduplication, where you can get up to a 10-to-1 reduction of data. Another interesting technology is flash. It represents a new tier of storage, and my thought as an engineer had been that everybody managing their datacenters would say, “Hooray, this is great—another tier that’s faster!” In fact, people in the IT shops are saying, “Yes, great. It’s a faster piece of storage, but now I have to go manage yet another tier.” So the idea of autotiering between flash, disk, and tape is something that IT managers are looking for. If you’re trying to take data and manually place it in either flash or disk, that’s very hard to do, and you’ll ultimately get it wrong. You need software to help manage the transition. The reason this is important is, of course, that flash offers higher performance. It’s also a little bit higher in cost. Disk is somewhere in the middle, both in terms of cost and performance. There are shades of gray here where high-performance disks perform better and are higher cost; higher-capacity disks are a little bit lower in cost but a little bit slower in performance; and then tape, which is the lowest in terms of cost but also the lowest in terms of performance. To manage your datacenter effectively across the cost bandwidth, you really have to use all these tiers. Oracle Magazine: What do organizations need to consider when including flash

oracle magazine September/October 2011

23

24

Interview

storage in their tiered storage strategies? Tracy: The introduction of flash as a separate tier of storage is really there to speed up response time for both reads and writes. The strategy of flash on the read side is to make sure the data needed is already cached. There’s a bunch of algorithms inside many Oracle storage boxes, namely the Sun ZFS Storage Appliance, that actually try to understand data patterns and prefetch information from disk and store it in flash. So when you ask for it, it’s already cached, and you get a very quick read back to your application. The strategy of flash on writes is to temporarily store data, return that status, and then subsequently write that data permanently to disk to store the information. So you get the quick response time of, “Hey, I’ve written this data.” It looks like you’ve written it to disk, with a background process that actually does the writing. The thing about flash—I mean it even sounds fast, flash—is it’s an order of magnitude faster than disk in this response time. Newer innovations that use a faster storage type—called DRAM [dynamic random access memory]—actually reduce response time by a factor of 10 again. These strategies are starting to come into play today. They also lower the total cost of ownership. From an electricity standpoint, they use significantly less power. The footprint is much smaller, and they have the storage power of many, many disks. The Sun Storage F5100 Flash Array, for example, is a 2U-form factor that supports 2 terabytes of flash and performs the same as 3,000 disks, but uses 0.15 percent of the space and about 0.55 percent of the power—a significant reduction. Oracle Magazine: Tell us about the hardware and software that make up the Sun ZFS Storage Appliance and unified storage. Tracy: The Sun ZFS Storage Appliance is made up of a bunch of disk drives. Obviously we have both single and redundant controllers. The controllers have DRAM. They also have a read cache in them.

listen to the podcast oracle.com/magcasts

Hybrid storage pools are continuously optimizing the storage system for performance and efficiency, and they manage the system as a single storage pool through a simple, transparent single hierarchy. Then, out in the storage fabric, we have what we call write flash. One of the technologies we have in the Sun ZFS Storage Appliance is autotiering, which I’ve mentioned. We actually have our own term for it called hybrid storage pools. And really, again, the data is intelligently and automatically migrated between memory, flash, and disk. Hybrid storage pools are continuously optimizing the storage system for performance and efficiency, and they manage the system as a single storage pool through a simple, transparent single hierarchy. That describes what’s in the storage appliance, but that’s not unified storage. Unified storage is defined by the software that enables customers to use it, and it falls into three different buckets. One of those buckets is data protocols. If you look at our storage box, we support both file and block protocols, 10 of them in one box. In the next bucket, we also support data services that customers will require, both for data protection as well as compression of data, and so on. And in the last bucket, from a management perspective, we have very unique analytics, which include dynamic real-time visualization of application- and storagerelated workloads with very simple but sophisticated instrumentation that provides real-time, comprehensive analysis. This allows you to troubleshoot and know what’s going on inside your storage box, as well as outside on a server fabric. Oracle Magazine: Looking forward, what do you see as the next set of challenges and strategies for storage? Tracy: We started out talking about the explosion of data and the requirements on storage. To keep up with that explosion, storage boxes are going to have to get bigger, faster, and easier to manage, and they’re going to have to start cooperating together.

SEPTEMBER/OCTOBER 2011 Oracle.com/oraclemagazine

One thing everybody in the industry is attacking is the idea of data protection. As the data and hard-disk drives get bigger, when a failure occurs, the ability to replicate and give that data back becomes harder. For example, you can use a technology called mirroring the data, where you write to two disks. That’s actually a pretty good technology solution as disk drives get bigger and bigger. You simply remove the disk that’s failed, put in a new one, and it will copy the data from one side to the other. While you’re exposed to a single point of failure for a period of time, you still have access to the data. The only issue with that solution is that you have to buy two times the amount of disk drives, and that becomes cost prohibitive. RAID-5 and RAID-6 don’t require two times the disk drives. The big issue there is rebuilding those disks as the disk drives get bigger and bigger. There are strategies and technologies for how to do that rebuilding process faster, and we’re developing those today. Finally, there’s manageability. As you’re going to have more and more data, you’ve got to have better management because you’re not getting increases in budget to increase your staff. In fact, you’re getting staff reductions. So we as storage vendors have to figure out better and better ways to manage those storage boxes. 

Next Steps LEARN more about Oracle’s storage solutions oracle.com/storage Oracle’s Enterprise Storage Webcast Series bit.ly/q4duop “Storage from A to ZFS” bit.ly/oOLJOJ CALCULATE how much you can save with tiered storage bit.ly/ol3uz9

26

Book Beat Oracle 11g R1/R2 Real Application Clusters Essentials By Ben Prusinski and Syed Jaffer Hussain Packt packtpub.com Written for Oracle DBAs who want to administer Oracle Real Application Clusters (Oracle RAC), this book presents a complete method for the configuration, installation, design, and administration of Oracle RAC 11g. Covering Oracle RAC 11g Release 1 and including bonus information on Release 2, the book is packed with realworld examples, expert tips, and troubleshooting advice. It begins by introducing the concept of Oracle RAC and high availability and then covers Oracle RAC 11g architecture and installation, Oracle Automatic Storage Management, troubleshooting, and workload management. Readers will also learn about Oracle Clusterware and when and how to upgrade an Oracle RAC environment.

Oracle Database 11g R2 Performance Tuning Cookbook By Ciro Fiorillo Packt packtpub.com This cookbook was written to help DBAs and developers understand the many aspects of Oracle Database that affect performance. Topics include application design and development; optimization of SQL and PL/SQL code; ways to speed up sort operations; strategies for hacking the optimizer and the data loading process; and useful methods to diagnose and tune memory and I/O and address contention issues. Every step-by-step recipe in the cookbook is based on a real-world script or procedure and illustrated with screenshots; theoretical concepts that explain why one solution performs better than another are also explained in the context of the recipes.

Oracle ADF Enterprise Application Development—Made Simple By Sten E. Vesterli Packt packtpub.com Developers need to know more than Oracle Application Development Framework (Oracle ADF) to build a successful enterprise application, so this book explains how to use Oracle ADF technology to take an application from proof of concept through estimating and structuring, coding, and completion. Readers are introduced to support tools for source

control and issue tracking and are taught how to integrate those tools into a development environment and use them productively. Out-of-the-box functionalities such as skinning, customization, and internationalization are discussed at length. The book is written for Web developers and developers who are experienced with classic 4GL tools.

Expert Oracle Exadata By Kerry Osborne, Randy Johnson, and Tanel Põder Apress apress.com Oracle Exadata embodies a fundamental change in how DBAs and developers think about and manage relational databases. The key difference lies in Oracle Exadata’s ability to offload SQL processing to the storage layer, which the authors of Expert Oracle Exadata believe is a game changer. Their book opens the “black box” to explain how Oracle Exadata’s combination of hardware and software works and teaches readers how to configure Oracle Exadata from the ground up. Specific topics include how to optimize Oracle Exadata for mixed online transaction processing/data warehousing workloads, how to migrate large data sets from existing systems, how to connect Oracle Exadata to external systems, and how to configure highavailability features such as Oracle Real Application Clusters and Oracle Automatic Storage Management.

Oracle VM Implementation and Administration Guide By Edward Whalen Oracle Press oraclepressbooks.com Oracle VM Implementation and Administration Guide shows you how to install, configure, and utilize Oracle VM, an Oracle virtualization solution. It covers the relationship between Oracle VM and the Oracle Linux operating system; contains complete details on installing and administering Oracle VM; includes tips, techniques, and tools for optimizing Oracle products on Oracle VM; and offers advice on choosing the best hardware for a virtualized environment. It includes practical information on how to size servers for Oracle VM, how to create server pools, how to create virtual machines manually and with templates, and how to configure network and storage for Oracle VM. The book concludes with a chapter on best practices.

Look for other Oracle books at oracle.com/ technetwork/community/bookstore.

SEPTEMBER/OCTOBER 2011 ORACLE.COM/ORACLEMAGAZINE

European Partners Gain SOA Specialization Five members of Oracle PartnerNetwork— four in the Netherlands and one in the U.K.— have achieved Specialized status in Oracle service-oriented architecture (SOA). This specialization acknowledges Oracle partners that meet stringent requirements and demonstrate expertise in delivering services related to Oracle SOA Suite 11g. AMIS Services, an IT services provider based in Nieuwegein, the Netherlands, that focuses on Oracle and Java technology and is an Oracle Gold Partner, has been working with Oracle SOA Suite for more than six years. The company employs nearly 20 SOA consultants. CIBER Nederland, an Oracle Platinum Partner based in Eindhoven, the Netherlands, has completed several reference projects related to SOA. It recently formed a dedicated Oracle practice team with its Oracle consultants. Whitehorses, an Oracle Gold Partner based in Nieuwegein, the Netherlands, was founded in 2001. The company uses its expertise in Oracle technologies and Java to integrate systems, processes, and data. It addresses infrastructure problems directly in small, manageable steps while maintaining a focus on achieving visible results. Yenlo, an Oracle Gold Partner based in Hazerswoude-Rijndijk, the Netherlands, employs senior specialists in Oracle and Java applications, middleware, and database platforms. Its services include consulting, architecture, custom applications, and 24/7 hosting and management of IT environments that include Oracle Fusion Middleware. Griffiths Waite Limited is a Birmingham, England–based Oracle Gold Partner specializing in Oracle SOA solutions. The company employs methodologies such as Lean-Six Sigma and the Open Group Architecture Framework, which allow the company to capture, leverage, and reuse architecture framework guidelines and employ operational and governance best practices. amis.nl ciber.nl whitehorses.nl yenlo.nl griffiths-waite.co.uk

Partner News

Oracle PartnerNetwork Hosts Forum at Oracle OpenWorld The Oracle PartnerNetwork Forum, a day of exclusive meetings and networking events for Oracle partners, will be held in San Francisco, California, on October 2, 2011, in conjunction with the opening day of Oracle OpenWorld. The forum begins at 9 a.m. with partner special interest meetings where select Oracle product, industry, and channel teams will

share information intended to help partners boost sales. After a networking lunch, Oracle executives will share Oracle’s vision for the partner community in fiscal year 2012 and brief attendees on Oracle resources that can strengthen market opportunities. Later in the afternoon, partners will break into groups by geography to discuss opportunities

and issues with regional Oracle Alliances and Channels teams. Following the Oracle OpenWorld welcome keynote, Oracle PartnerNetwork members will reconvene for the annual global partner awards ceremony and reception. This event is open to attendees of JavaOne, which starts on October 2. bit.ly/kKVv7r

European Partners Earn Hardware-Related Specializations Oracle Platinum Partner LC SystemsEngineering AG of Basel, Switzerland, has achieved Oracle PartnerNetwork Specialized status across six Oracle product areas, including two hardware-related specializations. The company, a datacenter service provider, earned specializations in Oracle Database, Oracle Linux, Oracle Database performance tuning, Oracle’s entry-level and midrange SPARC Enterprise M-Series servers (pictured), SPARC T-Series servers, and Oracle Solaris. LC

Systems-Engineering has provided datacenter solutions for more than 20 years and offers a range of services in cooperation with many solution and technology specialists. Oracle Gold Partner dignum GmbH has become one of the first organizations in Germany to achieve Specialized status in Oracle’s entry-level and midrange SPARC Enterprise M-Series servers, as well as specialization in Oracle Solaris. Based in Seefeld, Germany, dignum has experience in server and storage solutions, high availability, backup and recovery systems, and managed services. lcsys.ch dignum.de

Dell Services Introduces Oracle Accelerate Solutions Oracle Platinum Partner Dell Services has introduced new solutions to help midsize organizations rapidly and cost effectively deploy enterprise software. This new service offering comes in conjunction with Dell Services achieving Oracle Business Accelerators certification and being recognized as an Oracle Accelerate for Midsize Companies solution provider. As an Oracle Accelerate solution provider, Dell Services helps midsize organizations with limited budgets to design and deploy integrated, end-to-end solutions that rival those of their larger competitors. Oracle Accelerate solutions leverage software such as enterprise resource planning, product lifecycle management, supply chain management, and business intelligence applications.

Dell Services offers rapid implementation of Oracle E-Business Suite 12.1, together with services including project design and planning, as well as configuration and installation. Additionally, the company helps midsize organizations adopt industry-standard best practices and processes to help enhance productivity and competitiveness. In related news, Dell Services unveiled an Oracle Accelerate solution for the consumer packaged goods industry that leverages Oracle E-Business Suite 12.1. The Dell Services Oracle practice provides consulting services, strategy, business process re-engineering, application configuration and customization, organizational change management, testing, and maintenance services. dell.com/oracle

Sage Expands ERP Integration Oracle Gold Partner Sage has deepened the integration of Oracle Database 11g with Sage ERP X3, its global enterprise resource planning (ERP) system. The integration improvements, found in Sage ERP X3 version 6.2, simplify the implementation of Sage ERP by integrating Oracle Database as a native component of the product; increasing the application’s performance by taking advantage of new capabilities and enhancements in Oracle Database 11g; permitting “oneclick” installation of Oracle Database 11g with Sage ERP X3; and improving operational functions such as backups. In addition, Sage has made Sage ERP available on the Oracle Exadata platform for improved performance and scalability. sageerpx3.com

Version 1 Racks Up Specializations Version 1, a leading IT consulting and managed services company in Ireland , has retained its Platinum Partner status in the Oracle PartnerNetwork and has achieved Specialized status in Oracle Database 11g, Oracle Database 11g performance tuning, Oracle Real Application Clusters, Oracle SOA, and Oracle Business Intelligence foundation. Version 1’s Platinum and Specialized status recognizes its expertise in delivering solutions, consulting, and support services across the full Oracle stack. version1.com

Enkitec University to Offer Rapid Oracle Exadata Training Oracle Platinum Partner Enkitec, a consulting firm that provides Oracle Database development and support, is offering a three-day Oracle Exadata Database Machine training program. The program, called Enkitec University, uses a combination of focused lectures and problem-solving exercises. Interactive training and workshops give participants hands-on experience with Oracle Exadata hardware and software. Enkitec University is offered at Enkitec’s Las Colinas, Texas, headquarters but can also be presented at customer sites. enkitec.com/university

oracle magazine September/October 2011

27

Partner News

Syntax Earns Oracle Accelerate for Midsize Companies Specialization

Oracle Exastack Launches

Oracle Platinum Partner Syntax has achieved Oracle PartnerNetwork Specialized status for Oracle Accelerate for Midsize Companies. The specialization acknowledges Syntax’ attainment of sales training certifications and relevant customer references. It also recognizes development of the SyntaxExpress Accelerate Methodology, which incorporates industry-specific Oracle

The processing and performance benefits of Oracle Exadata Database Machine and Oracle Exalogic Elastic Cloud are now available to independent software vendors (ISVs) via the Oracle Exastack program. Through the Oracle Exastack program, Oracle is working with ISVs to optimize their applications on these engineered systems. With Oracle Exastack, ISVs can write applications once and have the flexibility to support both on-premises and cloud-based deployments. ISVs can now develop applications more quickly and focus R&D investments on their core functionality. oracle.com/partners/goto/exastack

Business Accelerators. Syntax, based in Montreal, Quebec, Canada, and with offices in Canada and the U.S., also holds specializations in Oracle’s JD Edwards EnterpriseOne distribution and configurable network computing solutions and JD Edwards EnterpriseOne financial management applications. syntax.net

Centric IT Solutions Earns Platinum Partner Level Centric IT Solutions GmbH has achieved Platinum Partner status in Oracle PartnerNetwork and has achieved Specialized status for Oracle’s JD Edwards EnterpriseOne financial management applications. Centric is a leading IT company

in the Netherlands and has branches in Belgium, Germany, Norway, Romania, Sweden, and Switzerland. Centric IT Solutions is part of the 9,100-employee Centric group. centric.eu

Temenos Leverages Oracle Exadata Oracle Gold Partner Temenos has made TEMENOS T24 (T24), its open architecture advanced banking system, available for Oracle Exadata Database Machine in a preconfigured core banking solution. Temenos will offer banks embarking on a legacy renewal program Oracle hardware, including Oracle

Exadata, preconfigured to support T24 implementations. The combination of T24 and Oracle Exadata will enable medium and large banks to take advantage of T24’s prepackaged functionality and the efficiency, security, and fault tolerance of Oracle Exadata. temenos.com

PasswordBank Joins Oracle Extended Identity Management Ecosystem PasswordBank Technologies has joined the Oracle Extended Identity Management Ecosystem program. PasswordBank’s identity and access management and single-sign-on solutions are integrated with the framework of the Oracle Extended Identity Management Ecosystem. The PasswordBank platform provides secure access to applications in heterogeneous environments by extending single-sign-on func-

tionality to non-Windows desktops. The PasswordBank solution works with Webbased, software-as-a-service, client/server, and legacy applications. Its features include a password manager, virtual provisioning, user activity audit, Web-based administration, workstation events monitoring and support, a password self-service portal, multifactor authentication, a time and attendance markup system, privileged identity management, and privileged user management. passwordbank.com

Strong Authentication Provider Authasas Goes Gold Authasas, a provider of strong authentication solutions for virtualized desktop infrastructures, is now an Oracle Gold Partner. Authasas provides authentication solutions for Oracle Identity Management. Specifically, Authasas Advanced

Authentication software provides several forms of strong authentication, including biometric fingerprint, contactless smartcards, and security questions, to Oracle Enterprise Single Sign-On Logon Manager. authasas.com

September/October 2011 Oracle.com/oraclemagazine

SYSTIME Achieves JD Edwards Advanced Specializations Oracle Platinum Partner SYSTIME has achieved Oracle PartnerNetwork Advanced Specialized status for Oracle’s JD Edwards EnterpriseOne distribution and configurable network computing solutions. The specializations recognize SYSTIME’s capabilities in assessing and deploying JD Edwards solutions. To qualify for Advanced Specialized status, a company must employ 50 or more certified specialists in the relevant product area and meet the criteria for Specialized status. systime.net

MICROS Systems and Subsidiary Earn Six Specializations Oracle Platinum Partner MICROS Systems and its subsidiary MICROS-Fidelio International, which provide IT solutions for the hospitality and retail industries, have achieved Specialized status in six areas: Oracle Database, Oracle Database performance tuning, Oracle Database security options, Oracle Database Real Application Clusters, Oracle Enterprise Manager, and Oracle Business Intelligence foundation. micros.com

Lindy Groening

28

Community Bulletin by Justin Kestelyn

Worthy of Membership

Oracle Technology Network redefines what it means to be a member of the club.

G

roucho Marx famously said, “I don’t want to belong to any club that will accept people like me as a member.” 1 Replace club with community, and you have an interesting basis for discussion. There are various competing theories of community out there, ranging from the passive to the active. (In the context of this discussion, a community is either partially or completely virtual—hence geographically neutral—in nature. Once upon a time, nearly all communities were built on the premise of shared geography.) On the passive end of the spectrum lies the community of interest, in which any people with a shared interest are implicitly members—Chicago Bears fans, for example. On the active end, where the open source world lives, it’s been said that community membership is defined only by contributions (of code, money, and/or time). And in the middle, at the top of the bell curve, is the virtual community, where according to Howard Rheingold in his book The Virtual Community (The MIT Press, 2000), “people carry on public discussions long enough, with sufficient human feeling, to form webs of personal relationships.” Since its inception, the Oracle Technology Network (OTN) program has tilted toward the passive end of this spectrum. Historically, the bonds of shared interest— as expressed by ticking a checkbox in one’s oracle.com account preferences—have been sufficient for helping the OTN team define what a community member is. But this action of self-identifying as an Oracle Technology Network member had the undesirable side effect of dividing the community between the active and the passive, because a community of interest does not necessarily imply that all its members are actively participating and engaged—even if they consider themselves part of the community. And for the health of the program, counting

Oracle Technology Network Turbocharges Oracle OpenWorld If you’re an Oracle OpenWorld attendee this year, prepare yourself for the usual excitement infusion from the Oracle Technology Network (OTN) program. Highlights include • Numerous technical sessions led by Oracle ACEs • The massive OTN Night party on Monday night (check the conference agenda for location) • Morning coffee/afternoon beer; daily raffles; community meetups; live tech chats; and free software, T-shirts, and other swag at the OTN Lounge (check agenda for location) Please stop by these sessions, events, and the OTN Lounge, and see the team! Oracle OpenWorld Registration oracle.com/openworld

active members is important—for reasons I will describe in a moment. Given these factors, the OTN team has decided to make a break with the past. Going forward, our model of community membership will be based on activity, not self-identification. In other words, we will consider you a member based on what you do, not what you call yourself. If you are a technical newsletter subscriber, we will consider you an Oracle Technology Network member. If you have logged in for a download or to post a discussion forum message over a certain time period, we will consider you a member. If you are a subscriber to Oracle Magazine or Java Magazine, we will consider you a member. And so on. (Case in point: nearly half of our newsletter subscribers today do not in fact self-identify as Oracle Technology Network members.) Please keep in mind, this process is completely transparent to you—you won’t notice any change in the benefits of membership; we are simply expanding the definition of what constitutes a member. Why is that important? Because the course direction of the program is charted according to the needs and interests of its members, and without knowing who those members are, the program is adrift. Circling back to the idea of “what you do,” I have a request. As you read this, you have possibly already received an invitation to take our five-question, one-minute survey.

I implore you to participate, because the OTN team truly needs to know more about you before we can create and prioritize new objectives for the program. If you have not received the invitation, you’ll find it at oracle .com/technetwork/goto/survey. By the way, the team could have gone another direction: requiring self-identification as a gateway to benefits. But this would have added an unnecessary layer of complexity and raised the bar for membership overall. Groucho would not have approved. 

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 Technology Network Home oracle.com/technetwork Become a Member oracle.com/technetwork/community/join/ overview Database Admins and Developers Home Page oracle.com/technetwork/dbadev System Admins and Developers Home Page oracle.com/technetwork/systems Architect Home Page oracle.com/technetwork/architect

1 Source: en.wikiquote.org/wiki/Groucho_Marx

oracle magazine September/October 2011

29

30

Architect By Bob Rhubart

Getting Schooled

Education, training, and experience are stepping stones to a career as a software architect.

T

here is more to being a successful software architect than learning the secret handshake and showing up at the meetings. You need education. You need training and experience. The specifics of the education, training, and experience depend on which branch of the profession you plan to enter. And given the variety of roles under the software architecture umbrella, it should come as no surprise that there is more than one path to an architecture career. In order to gain some insight into the possible paths, let’s take a look at the experience of three very different architects. Our first architect, Helen Sun, has a PhD in educational technology and information systems from the University of Toledo. She started out as an educator, but a fascination with technology pulled her in a different direction, a shift that ultimately led to her current role as an Oracle enterprise architect. “Although an advanced degree is not a prerequisite to being an architect,” Dr. Sun says, “the critical thinking I acquired in my PhD study and the habit of turning over every stone are definitely beneficial to what I do.” And she’s done a lot—database and Web programming, database administration, data warehousing, and business intelligence—as she moved through various leadership positions. That diverse background is a real boon in her role as an enterprise architect. But experience alone will only get you so far. Sun bolstered her technical experience with training and certification in the Open Group Architecture Framework (TOGAF). She also completed Oracle’s internal enterprise architecture master class and earned Oracle enterprise architect certification. Our second architect, Karine Ishkhanova, who holds a PhD from Moscow State University, says she grew into her role as a system architect with Toronto, Ontario,

“What works for me is the analytics basics I got through my computer science degree.” —Ronald van Luttikhuizen Canada­–based TFA Wise Applications. “My original university education was in economics. When I immigrated to Canada in 1996, I faced the task of integrating my skills into the Canadian professional market. I opted to complete an intensive programming course at the Institute for Computer Studies.” Ishkhanova’s experience includes working on the Y2K conversion for a global accounting firm, developing an automated processing interface for credit card chargebacks, and creating a custom invoiceprocessing module. “That project was the first step that pushed me to concentrate my professional growth toward becoming a fully qualified technical lead and system architect,” she says. Already a Sun Certified Java Programmer and a Sun Certified Java Developer, she is now studying to earn the Oracle Certified Master, Java EE Enterprise Architect certification. “IT is a very fast-paced industry, so one always has to look forward and anticipate what skills to master to stay in demand,” Ishkhanova adds. Our third architect, solution architect and Oracle ACE Director Ronald van Luttikhuizen, focuses on SOA and middleware in his role as managing partner at Vennster. His path to that role included earning a master’s degree in computer science from the University of Utrecht in the Netherlands, where he specialized in algorithmic design. He also earned Java certification and has had training in

September/October 2011 Oracle.com/oraclemagazine

TOGAF, Oracle technologies, and objectoriented programming. “What works for me is the analytics basics I got through my computer science degree, and doing lots of hands-on projects for lots of customers,” says van Luttikhuizen. He credits his success over the last 10 years to a combination of “a solid education and onthe-job experience.” The experience of three architects in three different roles certainly doesn’t represent the entire profession. But the common thread in each of their stories offers guidance to those interested in building a career as an architect in the IT realm. It turns out that the real secret handshake necessary to gain entrance into the club is a willingness to hit the books, to do the work, and to never stop learning. 

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 EXPLORE education and training resources The Open Group opengroup.org Arcitura arcitura.com IASA iasaglobal.org Oracle University courses for architects education.oracle.com

Oracle OpenWorld 2011 • October 2–6 • San Francisco Visit Oracle Press in the Oracle OpenWorld bookstore for free gifts, prize drawings, book signings, sneak previews, and more.

Oracle BPM Suite 11g Handbook Manoj Das, Manas Deb, and Mark Wilkins Learn best practices for successfully implementing business process management projects

Oracle Hyperion Financial Management Tips and Techniques Peter Fugere The definitive guide for working with and optimizing Oracle Hyperion Financial Management

Java: The Complete Reference, Eighth Edition Herb Schildt The definitive guide for Java programmers—fully updated for Java SE 7

Oracle Database 11g and MySQL 5.5 Developer Handbook Michael McLaughlin Work seamlessly in a mixed-database environment

Oracle Database 11g Real Application Clusters Handbook, Second Edition K Gopalakrishnan Architect a scalable, highly available database environment

Oracle WebLogic Server 11g Administration Handbook Sam R. Alapati Install, configure, manage, and secure Oracle WebLogic Server 11g

E-BOOKS: Go to OraclePressBooks.com for Adobe Digital Editions (PDF) or Amazon.com for Kindle Editions. Join the Oracle Press Community: www.OraclePressBooks.com Follow us @OraclePress

32

Up Close By Caroline Kvitka

Global Explorer

OAUG President Mark Clark wants to expand the organization’s reach beyond North America.

ark Clark isn’t afraid to go exploring. In fact, when I talked with him during his recent visit to Oracle headquarters, he had just returned from the mountains— where he had a run-in with a mother bear and her cubs. Now out of harm’s way, he says he’s ready for another adventure. This time, Clark will be undertaking new explorations as Oracle Applications Users Group (OAUG) president, a role in which he hopes to extend the user group’s activities to other regions of the world. “I’d like to see OAUG expand geographically,” says Clark, a 20-year veteran of OAUG who has served in a variety of positions with the organization, including membership chairman and executive committee member. “Traditionally, OAUG has been very strong in North America. A lot of our members are making their first trips globally, pushing forward to Brazil or Asia, and they want information from their peers on the pros and cons, the challenges, and the pitfalls.” Global exploration, says Clark, will include building on events such as OAUG Connection Point—Dubai, a two-day education and networking event. The third annual OAUG Connection Point—Dubai event in March attracted more than 300 attendees from all over the Middle East and featured Cliff Godwin, senior vice president of applications development at Oracle, who provided an update on the Oracle E-Business Suite product line. Conference topics included new features in Oracle E-Business Suite 12, upgrade best practices, and enterprise performance management. OAUG has also expanded its horizons by partnering with the German Oracle Users Group (DOAG) on the DOAG Applications Conference in May. At this three-day event, the spotlight was on Oracle Fusion Applications, with Oracle representatives presenting the next generation of Oracle Applications.

Networking Still Rules OAUG’s global reach with education and networking events makes sense, given Oracle’s growth in the applications area. “When OAUG first started, Oracle was just expanding from database into applications,” Clark says. “There were very few sources for

OAUG President Mark Clark

information about the applications. Peer-topeer was probably one of the best ways to get information.” Now that Oracle’s applications offerings are broader, OAUG members need to find information that is relevant to their specific needs. But with so much information available online about Oracle Applications, from a variety of sources, Clark says that networking is more important than ever. “Finding that person you can network with and communicate with is key,” he says. Education and networking, in fact, top the OAUG member wish list. “We survey our members every year to find out what’s important. The first is educational events, the second is networking opportunities, and the third is for OAUG to have a good interactive relationship with Oracle,” Clark says.

September/October 2011 Oracle.com/oraclemagazine

Oracle OpenWorld presents an optimal opportunity for education, networking, and interacting with Oracle developers and product managers. At the upcoming conference (October 2 to 6, 2011) OAUG is offering 50 sessions at User Group Sunday (October 2) geared toward both geographic and special interest groups. OAUG will also offer 30 sessions over the course of the week on topics including master data management and Oracle Fusion Applications. Give Back As we wrapped up our interview, Clark promised to limit his wildlife adventures to his family’s new pet—a retired Greyhound. He also shared his advice for new OAUG members, likening new membership to working with a new Oracle application. “From an implementation standpoint, we always try to do a sandbox instance, and we encourage people to roll up their sleeves and really interact with Oracle,” Clark explains. “I would talk to new members the same way. I’d encourage them to join the user group, get involved, attend a special interest group or local geographic group meeting. And then think about giving a presentation on their own—or evangelizing the OAUG message throughout their organization.” 

Caroline Kvitka (caroline.kvitka@oracle .com) is senior managing editor of Oracle Magazine and Java Magazine.

Next Steps LEARN more about OAUG oaug.com WATCH the interview oracle.com/oramag/upclose

Bob Adler

M

Now, manage both your UPS and your energy proactively. CO2

Energy usage and energy cost reporting: Save energy and money by tracking energy usage and costs over time.

CO2 emissions monitoring: Reduce environmental impact through increased understanding of CO2 emissions.

Risk assessment: Identify and proactively manage threats to availability (e.g., aging batteries).

Only APC Smart-UPS saves money and energy without sacrificing availability. Today’s more sophisticated server and networking technologies require higher availability. That means you need more sophisticated power protection to keep your business up and running at all times. But that’s not all. In today’s economy, your UPS must safeguard both your uptime and your bottom line. Only APC by Schneider Electric™ helps you meet both of these pressing needs. Specifically, the APC Smart-UPS™ family now boasts models with advanced management capabilities, including the ability to manage your energy in server rooms, retail stores, branch offices, network closets, and other distributed environments.

Intelligent UPS management software PowerChute™ Business Edition, which comes standard with Smart-UPS 5 kVA and below, enables energy usage and energy cost reporting so you can save energy and money by tracking energy usage and costs over time; CO2 emissions monitoring to reduce environmental impact through increased understanding; and risk assessment reporting so you can identify and proactively manage threats to availability (e.g., aging batteries).

Best-in-class UPS Our intelligent, interactive, energy-saving APC Smart-UPS represents the combination of more than 25 years of Legendary Reliability™ with the latest in UPS technology including an easy-to-read, interactive, alphanumeric LCD display to keep you informed of important status, configuration, and diagnostic information. It also has a unique battery life expectancy predictor, and energy-saving design features, like a patent-pending “green” mode. Now, more than ever, every cost matters and performance is critical. That’s whyyou should insist on the more intelligent, more intuitive APC Smart-UPS.

Why Smart-UPS is a smarter solution Intuitive alphanumeric display Get detailed UPS and power quality information at a glance – including status, about, and diagnostic log menus in up to five languages.

Configurable interface

Set up and control key UPS parameters and functions using the intuitive navigation keys. On rack/tower convertible models, the display rotates 90 degrees for easy viewing.

Energy savings

A patent-pending “green” mode achieves online efficiencies greater than 97 percent, reducing heat loss and utility costs.

Download White Paper #24 “Effect of UPS on System Availability,” and register to win an APC Smart-UPS 1500VA rack/tower LCD 120V, a $779 value! Visit www.apc.com/promo Key Code e646v Call 888-289-APCC x6291

©2011 Schneider Electric. All Rights Reserved. Schneider Electric, APC, Smart-UPS, PowerChute, and Legendary Reliability are trademarks owned by Schneider Electric Industries SAS or its affiliated companies. email: [email protected] • 132 Fairgrounds Road, West Kingston, RI 02892 USA • 998-4144_US

34

Peer-To-Peer by Blair Campbell

In with the New

Rave reviews for virtualization, Oracle Exadata, and the ‘Twittersphere’

Julien Gabel

Company: Silca, the IT production group of France’s Crédit Agricole banking group Job title/description: Multiplatform UNIX systems consultant and administrator, working in mutualized and virtualized environments deploying Oracle Solaris and UNIX Location: Paris, France Length of time using Oracle products: More than 10 years What is your favorite tool on the job? I’m interested in pinpointing the root cause of performance problems, and I’ve found DTrace—introduced in Oracle Solaris 10—to be an invaluable tool for this. It has enhanced understanding of how things work at the operating system level, as well as providing a better understanding of the metrics reported by more-classic tools. What about a favorite strategy? I encourage a focus on virtualization, both for myself and among my team members—particularly when the project is based on zones and ZFS, which completely revolutionized and extended the possibilities of virtualized configurations. Nowadays, one virtualization technology or another can be the solution for most workloads I see. If you were going to the International Space Station for six months and could only take one Oracle reference book, what would it be? Without hesitation, it’s Solaris Performance and Tools: DTrace and MDB Techniques for Solaris 10 and OpenSolaris [Prentice Hall, 2006]. This book is a must-have, covering both tools and methodologies for performance observation and debugging.

Fahd Mirza

Company: Pythian, a firm specializing in remote DBA services and consulting Job title/description: DBA consultant, managing configurations based on Oracle solutions Location: Wah Cantt, Pakistan Oracle credentials: Oracle Certified Professional (Oracle Database 10g, Oracle Database 11g), with 23 years of experience using Oracle products What technology has most changed your life? The Oracle Exadata Database Machine. It’s changed the game and has set very high standards of performance, scalability, reliability, and support unification. Managing mixed OLTP [online transaction processing] /data warehouse workloads on a single view of the system has made things easier and seems so natural. With our clients who are using Oracle Exadata, I’ve seen gigantic improvement in ETL [extract, transform, and load] processes, analytics, and reporting, and the notion of nightly batch jobs when interactive users are sleeping is now obsolete. You can run anything at any time. How are you currently using cloud computing in your work? My whole job is on the cloud. With only my browser and a remote SSH [Secure Shell] session, I manage Oracle databases around the globe without ever touching, seeing, or knowing the physical location of their servers. How do you use mobile computing? I’ve configured my mobile devices to access Pythian’s custom monitoring tool and made those devices Oracle Grid Control–aware. Now I can’t see how I survived without this setup.

Frits Hoogland

Company: VX Company, an IT service provider Job title/description: Principal consultant, handling Oracle Exadata installation and configuration and IT consulting in the performance area Location: Baarn, the Netherlands Length of time using Oracle products: 15 years How did you get started in IT? I really got started by figuring out the architecture of my home computer during high school. It was an MSX2—a 1980s-era collaboration between Microsoft and ASCII Corp. that was especially popular in Europe—with a Z80A microprocessor, first designed by Zilog in the mid-1970s, at a whopping speed of 3.58 MHz. Five years later, I got my first IT job—as a Clipper programmer. [Clipper was a compiler for the dBASE III language, for MS-DOS.] Which new features in Oracle technology are you currently finding most valuable? The Oracle Exadata Database Machine’s optimizations have proven very valuable— especially the Smart Scan feature, which greatly reduces full scan response time. How do you use social media in your professional life? I blog and use Twitter. I’m both communicating with other IT professionals through Twitter and using it as an information aggregator. I used to use RSS feeds to get information about new blog posts, but now I let blog aggregators tweet them to me. Twitter is the newsgathering medium that costs me the least time, because it combines new blog posts, local and world news, and messages to and from industry peers. 

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

ADVERTISING SUPPLEMENT

Both Oracle and Fujitsu are focused on the future of SPARC technology— and that promises to pay off for Oracle users in the coming years.

SPARC Technology

D

ata centers have to contend with the ongoing day-to-day challenges of keeping critical systems up and running in support of the business. But they also have to look beyond today to determine how they will handle growing workloads and support new business processes and strategies—all while keeping costs down. To help in that effort, Fujitsu and Designed to provide mainframe Oracle released a roadmap last year for SPARC Enterprise servers running performance at open-system Oracle Solaris, depicting how these costs, these servers solidify the systems would evolve and continue to commitment to SPARC/Solaris deliver higher levels of performance products that deliver improved over the next five years. value and efficiency. Recently, the two companies took a long step forward in executing the SPARC roadmap with the launch of an enhanced SPARC Enterprise M3000, which uses the new SPARC64 VII+ processor, and features the logos of both companies.This server is the latest result of a 20-year-plus relationship between Fujitsu and Oracle, and it further solidifies the commitment in providing Oracle Solaris products that deliver improved value and efficiency.

ADVeRTISIng SUPPleMenT

The SPARC enterprise M3000 is designed to provide mainframe performance at open-systems costs. And it reflects the key qualities that enterprise servers should deliver—high performance, high levels of availability and customer investment protection.

Enhancing the SPARC Lineup The new SPARC enterprise M3000 joins a complete lineup of Fujitsu and SPARC enterprise M-Series servers,

which range from the single-socket M3000 to the 64-socket M9000. The SPARC enterprise M-Series servers run oracle Solaris and deliver reliability, scalability, virtualization and tightly engineered performance optimizations for companies using mission-critical applications, including oracle Applications and Fujitsu applications. For example, the new SPARC64 VII+ processor offers up to 20 percent better performance than the previous generation SPARC64 VII processor. It provides faster memory access and increased compute power, including

An end-to-end Perspective on oracle The shared focus on SPARC technology is just one aspect of the Fujitsu and oracle relationship. over the course of two decades, the companies have worked together on everything from innovative applications and middleware solutions to service-oriented architectures, data management, business-critical computing and grid computing. Fujitsu’s strength in the oracle arena was recently highlighted in an independent report. Fujitsu was positioned in the leaders category of The Forrester Wave™: oracle Services Providers, Q2 2011 report.The leaders category is the top-ranking section of the report. The report stated that “Fujitsu invests substantially in co-development and innovation related to oracle and is one of the leading partners in the Fusion applications space.” As a global IT company, Fujitsu provides end-to-end technology, services and expertise for oracle users, from consulting to design, build, implement and maintain activities. Fujitsu offers: • Comprehensive IT, business and infrastructure services, with a number of Oracle-specific managed and professional services covering applications, infrastructure and business imperatives such as operational excellence and customer experience. Fujitsu has experience and expertise with oracle e-Business Suite; oracle’s Siebel, PeopleSoft, and JD edwards applications; and oracle industry solutions as well as oracle Fusion Middleware and oracle Database. • A range of data center technologies, which focus on the creation and delivery of “Dynamic Infrastructures” supporting oracle applications and the oracle technology stack. Dynamic Infrastructures combine products, solutions and services that enable companies to assign IT resources dynamically to services as required, and to choose sourcing models that best fit their businesses, bringing IT flexibility and efficiency to the next level. Fujitsu has some 3,500 oracle professionals working in more than 20 countries—and the depth of their knowledge is well-documented by oracle itself. For example, Fujitsu, an Oracle Platinum Partner, was the first global partner to earn the Oracle PartnerNetwork (oPn) Specialized status for SoA. To earn an oPn specialization, an organization has to meet a stringent set of requirements in a certain area. overall, Fujitsu has 25 oPn specializations in oracle-related hardware, software and industries—and that number is growing.

an increased clock speed of up to 3.0 ghz, and a doubling of the size of the l2 cache up to 12MB.This type of improvement is actually part of Fujitsu’s long term strategy. Companies using SPARC enterprise servers have always benefited from regular increases in performance because Fujitsu is constantly improving its CPU and memory architecture. Fujitsu regularly releases processors that deliver significant performance increases by adding cores, introducing ever more sophisticated threading mechanisms, and extending CPU cache capacity. In addition to drawing on the power of the new processor, the SPARC hardware and oracle Solaris software have been jointly engineered to optimize oracle’s software in its performance and functionality. As a result, organizations running oracle enterprise Manager ops Center 11g can take advantage of new SPARC Enterprise M-Series-specific management capabilities such as creating and provisioning Dynamic Domains.The recent improvements to oracle enterprise Manager ops Center means that SPARC enterprise M-Series customers can use the same software to manage the entire integrated stack, from applications to the servers. To help data centers meet the challenges of both today and tomorrow, Fujitsu and oracle offer a broad range of no-cost virtualization technologies. These can be mixed and matched in order to support a company’s specific needs for business continuity, agility and improved business operation, for every application requirement, all in the same box. For example, an organization can efficiently run databases in Dynamic Domains in an SPARC enterprise M-Series server and the applications in oracle Solaris Containers in other Dynamic Domains in that same SPARC enterprise M-Series server. SPARC enterprise M-Series virtualization technologies provide no-cost,

low-overhead, secure and dynamic partitioning that meet all the business requirements for flexible resource allocation. The SPARC Enterprise M-Series server family provides the high availability required for “always-on” 24/7 missioncritical computing.The SPARC Enterprise M8000 and SPARC Enterprise M9000 even include processor and memory hot-swap capabilities. The SPARC Enterprise M-Series family includes “predictive self-healing,” with an automated loop of detection, correction, dynamic de-allocation and maintenance. Error detection and correction features are built into the processors, system interconnects, and other hardware components. This not only takes the self-healing load off of the operating system and applications, it also helps ensure that the platform can truly manage itself. Besides extending the number of cores, increasing cache capacity and clock frequencies, Fujitsu continues to devote significant resources to ensuring its systems remain at the forefront of mission critical technology. Finally, the SPARC Enterprise M-Series server line is designed to help data centers adapt to growing energy-usage and environmental mandates—which in turn helps control costs. For example, the enhanced SPARC Enterprise M3000 server provides up to 37 percent lower power consumption, compared to similarly equipped competing HP and IBM servers, while minimizing the environmental impact of CO2 emissions. With such performance, it has achieved Fujitsu’s highest sustainability-related classification of “Super Green Product.” “The SPARC Enterprise M-Series line with Oracle Solaris provides a high-performance foundation for mission-critical computing with unmatched investment protection,” sums up John Fowler, Oracle Executive Vice President, Systems. “Through Oracle’s collaboration with Fujitsu, we are providing customers with powerful SPARC/Oracle Solaris-based systems designed to provide best-in-class performance from application-to-disk.”

Tracking Success Fujitsu’s recent record of Oracle related achievements includes: n

n

n

n

n

n

Database Award 2011 ( Japan) Exadata Award 2011 ( Japan) Fujitsu has been positioned in the Leaders category of The Forrester Wave™: Oracle Services Providers, Q2 2011 report Won the Oracle Global Partner of the Year award at Oracle OpenWorld 2010 for its work with the Ministry of Justice in Japan Received two honorable mentions (Titan) awards at Oracle OpenWorld 2010 for projects in the USA (Manufacturing & Distribution and Supply Chain Management) First global Oracle SOA (Service Oriented Architecture) Specialized Partner.

n

n

n

Recipient of the Oracle global ‘Enable the Eco-Enterprise’ award – both announced at OpenWorld 2010 Winner of Public Sector Partner of the Year award at UK Oracle User Group (UKOUG) Awards 2010. Received a further two Bronze Awards (Training and Middleware). These awards are the only ones voted for by Oracle customers anywhere in the world In addition, Fujitsu also won the following Oracle awards in 2010:Technology System Integrator Partner of the Year (UK), Oracle Japan Industry Partner of the Year for Innovation, Oracle Japan Database Award and Oracle Japan Exadata Award

ADVeRTISIng SUPPleMenT

Powering next-generation operations The Andersen group companies sell quality breads through 450 stores across Japan, as well as locations in Denmark and the US.Within the group, each company essentially managed its own operations with its own systems. But to keep up with order volumes, the company decided to move to what it calls “next generation Total SCM,” based on a group-wide technology platform. “Until now, each group company has been optimizing its systems separately. However, for TCO reduction and efficient administration on a group-wide scale, these systems must all inter-work harmoniously,” says Toshiaki horio, manager of the company’s System Support Department.The company tackled that problem with a single platform that could support applications for ordering, procurement, manufacturing, inventory control and shipment—and ensure that they all work together. To create this platform, the Andersen group implemented two clustered Fujitsu SPARC enterprise M3000 servers and Fujitsu eTeRnUS 2000 storage systems. “From my long experience with UnIX servers, I consider the latest [SPARC enterprise] model with the sophistication of multi-core processors and virtualization to be dependable, because we know many companies worldwide are using it,” says horio. “In addition, SPARC enterprise M3000 can provide high performance with low cost.” The new group-wide system has indeed delivered a number of such benefits.The Andersen group has seen application server performance improve by a factor of 2.3, while database server performance has improved 3.5 times.With the clustered approach, both hardware and operating system reliability have improved as well, and maintenance is easier. “Data backup time was reduced to around one hour and forty minutes from over three hours,” says horio. Meanwhile, the company has been able to consolidate applications, including inventory control and data warehouse applications, and reduce the number of servers it needs from three to two. This has helped reduce server administration, space and power requirements, and contributes to a low TCo for the platform. overall, the Fujitsu technology is helping the company meet the needs of today and tomorrow, says horio: “The new system keeps response times constant even when handling multiple transactions. The new system will also be able to handle application workloads which will be added in the future.”

The SPARC Journey Continues Fujitsu and oracle have a long history of working together on a variety of fronts, and that includes tightly coupled development plans for SPARC and oracle Solaris. earlier this year, the two companies extended their SPARC development relationship with a new product distribution agreement and a commitment to further joint engineering, marketing and sales promotion efforts. Meanwhile, both companies have engineering centers focusing on SPARC and oracle Solaris technologies, with the engineers in their respective centers working and collaborating closely together.The two companies have also established virtual teams that focus on

various aspects of the SPARC enterprise server effort, such as data center server teams, mid-range server teams, service processor software teams, oracle Solaris teams and documentation teams. In addition, Fujitsu and oracle Japan have established a SPARC enterprise-oracle Database Solution Development Center that works on creating solutions for next-generation standard IT infrastructures. For its part, Fujitsu builds on its deep expertise in SPARC technol-

Certified No.1 in the world

ogy developed over many years. Fujitsu provided SPARC technology to Sun and was an Innovation Partner of Sun’s for years, and that focus has continued through oracle’s acquisition of Sun. What’s more, recent efforts at Fujitsu have taken SPARC technology to new heights, with the “K computer,” built by Fujitsu and RIKen, a Japanese research institute. Based on Fujitsu SPARC chips, the K computer, currently not even fully configured, recently achieved the highest performance level in the world, with a lInPACK benchmark result of 8.162 quadrillion floating-point operations per second. “The experience and knowledge enhanced through such innovations constantly strengthens our ability to use SPARC technology in enterprise computing,” says noriyuki Toyoki, Corporate Senior Vice President at Fujitsu. on another level, Fujitsu has re-aligned and re-focused its various business units into a cohesive, global “one Fujitsu”—a $55 billion company with some 170,000 employees, and one of the largest ICT providers in the world. This allows the company to closely align its SPARC and oracle resources with those of oracle, around the world. I

For more information on Fujitsu and SPARC technology, please visit www.fujitsu.com/oracle and www.fujitsu.com/sparcenterprise. or, visit the Fujitsu Booth #1311 at oracle openWorld 2011.

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.

Organizations deploy x86 blade servers from Oracle to reduce costs and sharpen business services.

ervers on the Cutting Edg

40

by David a. kelly

O

nce deployed only as Web servers and in less-critical deployment scenarios, x86-based servers are now deployed in datacenters and cloud computing environments across the globe and are successfully powering business-critical applications. x86 processors, including Intel’s powerful Xeon processors, are the processing foundation for cutting-edge Oracle hardware, including database and storage appliances such as Oracle Exadata. With standards-based x86 servers powering the engine rooms of so many organizations’ IT infrastructures, what differentiates Oracle x86-based servers?

41

oracle magazine September/October 2011

42

“We manage the storage of very, very large data sets. . . . A few years ago, we were talking 100 MB; now we’re pulling in terabyte and multiterabyte data sets.” —Young Sul, Senior Systems Administrator, Department of Health Care Policy, Harvard Medical School “Part of the attraction of purchasing x86-based servers from Oracle is that they’re delivering an optimized hardware and software technology stack,” says Jean S. Bozman, research vice president for the Enterprise Platforms Group at International Data Corporation (IDC). “For Oracle, it’s not just a matter of selling a lot of small servers but of providing solutions that support certain types of workloads appropriate for the enterprise, midmarkets, and service providers, and putting in the layers of Oracle’s software that support those workloads.” Oracle Delivers Real Horsepower for Harvard Medical School Gathering and organizing volume data that informs decision-makers requires significant processing capacity. That’s one of the key reasons that the IT group at the Department of Health Care Policy at Harvard Medical School turned to Oracle’s Sun servers to power high-end statistical analysis jobs. “While the department isn’t terribly large in terms of users, the types of high-volume transfer and highvolume statistical jobs that we run tend to push the limits of typical enterprise systems,” says Young Sul, senior systems administrator, Department of Health Care Policy, at Harvard Medical School. “That’s why Oracle [hardware and software] has always been a good fit.” The Department of Health Care Policy at Harvard Medical School is an interdisciplinary department that includes doctors, economists, sociologists, psychologists, and other specialists. It manages studies and research on healthcare, from the impact of social networking to outcomes for posttraumatic stress disorder treatments. The department is composed of about 140 people, including primary investigators, statisticians, and statistical programmers. As might be expected, much of the analysis relies on manipulating very large data sets and in-depth number crunching, requiring enterprise-class server horsepower and highly scalable, high-bandwidth storage. “We manage the storage of very, very large data sets and the acquisition of servers that can handle the kinds of statistical processing that our investigators and statisticians need to do for the publications they’re working on,” says Sul. “A few years ago, we were talking 100 MB; now we’re pulling in terabyte and multiterabyte data sets. It’s ramped up significantly.” For most of its 25 years, Health Care Policy has been a Sun shop, from its disk arrays to servers to Web servers. Key decision factors for using Oracle’s x86-based Sun servers include price for performance and a consistent management and deployment experience. “When we introduced our first x86 system a few years ago, it became one of the most popular systems

Oracle in the Engine Room In addition to scalability, reliability, and price/performance benefits, one of the key factors making x86 servers so popular is the relatively fast rate of change in the underlying CPU and chipset technologies. Newer processors are faster, and organizations like faster servers. “Our releases are timed with the pace of development in the x86 market,” says Ali Alasti, vice president of x86 management at Oracle. “As new x86 CPUs and chipsets are

made available, we introduce new systems that take advantage of those latest releases.” Instead of just delivering raw processing power in a new server, however, Oracle is focused on delivering x86-based solutions— tested and benchmarked combinations of the latest chips with enterprise-tuned configurations, with integrated software packages and options. Oracle offers a range of x86 servers, both rackmount and blades, engineered for virtual-

September/October 2011 Oracle.com/oraclemagazine

ization (with Oracle VM), high performance, availability, and reliability. In July 2011, for example, Oracle announced the availability of the Sun Fire X4800 M2 server. The rackmount server is powered by up to eight Intel Xeon E7-8800 processors and features 2 TB of memory, 4.8 TB of internal storage, eight hot-swappable PCI ExpressModules, and Oracle Integrated Lights Out Manager. The x86-based system is optimized to run Oracle Solaris, Oracle Linux, and Oracle VM.

snapshot

Department of Health Care Policy, Harvard Medical School www.hcp.med.harvard.edu Location: Boston, Massachusetts Employees: More than 140 Oracle products: Sun Fire X4470 and other Sun Fire x86 servers, Oracle Solaris 10

Dave Bradley

among the analysts to work on,” says Sul. “It actually became a little problematic because everyone wanted to use it, since it was just much, much faster than what we had previously.” Currently, Health Care Policy has several x86 servers—Sun Fire X4470 and other Sun Fire x86 servers— deployed. The prices for x86-based systems are competitive, but according to Sul, one of the more important benefits of an x86-based architecture is its scalability. “Since the systems are homogeneous, it makes it easier for us to incorporate new hardware whenever we need to,” says Sul. “For example, we’ve recently been purchasing multi-CPU dense systems with lots of memory, which are great for statistical analysis.” Another real advantage for Sul and the Health Care Policy group is the management solutions Oracle provides. “The lights-out management feature [Oracle Integrated Lights Out Manager] is really convenient for us because it allows us to control, manage, and even reboot a frozen server from a remote console,” says Sul.

The high-volume transfer and statistical jobs that Harvard Medical School’s Department of Health Care Policy run tend to push the limits of typical enterprise systems. “That’s why Oracle [hardware and software] has always been a good fit,” says the department’s Young Sul.

More for Less at CBOE Holdings When your business is built around microsecond response times for trading in highly active financial derivatives products, performance, reliability, and availability don’t just matter—they’re all that matters. That’s why CBOE Holdings migrated its infrastructure to Oracle’s Sun x86 servers. “Moving to x86-based servers wasn’t only a good idea; it was essential to our business,” says Gerald O’Connell, executive vice president and CIO at CBOE Holdings. “Price, performance, and capability on x86based servers are all significantly better than our previous architecture.” Established in 1973, CBOE Holdings is the largest U.S. options exchange and creator of listed options. In

oracle magazine September/October 2011

44

2010, CBOE Holdings posted its third consecutive year of trading volume at more than 1 billion contracts. CBOE Holdings has two exchanges in Chicago, Illinois—Chicago Board Options Exchange (CBOE) and CBOE Futures Exchange—as well as its all-electronic New Jersey–based C2 Options Exchange, which opened for trading in October 2010, and its partly owned CBOE Stock Exchange. As you might expect, CBOE Holdings’ IT environment revolves around its high-performance trading systems. The organization is currently running more than 700 Sun Fire x86–based servers—including Sun Fire X4270—on CBOEdirect, and all the applications run on Java. CBOE Holdings’ back-end systems run Oracle databases and data warehouses on x86 servers as well. “Everything we run is x86-based,” says O’Connell. “We’ve found x86-based servers easy to integrate into our IT environment.” While some companies might try to hold on to their hardware investments, CBOE Holdings tends to keep its servers active for only about two years in its production environment. “We’re always looking for the next fastest server,” says O’Connell. “When faster processors and faster machines come out, we put them in, because microsecond response time is key for us in the highly competitive exchange environment.” CBOE Holdings’ architecture is designed to scale both horizontally and vertically, depending on the organization’s needs. Scalability is especially important as the company grows its business. To help it capitalize on the scalability capabilities that x86-based servers can provide, CBOE Holdings has also made sure to engineer its software architecture to scale. “Our exchanges all run the same code set and the same platform,” says O’Connell. “We’ve built it in a way to handle different types of trading assets, classes, options, stocks, and futures. The system runs the same code set with just XML parameter changes.” Each exchange has different hardware configurations, based on that exchange’s scalability and load requirements.

SNAPSHOT

CBOE Holdings cboe.com Location: Chicago, Illinois Employees: 600 Revenue: US$437 million in 2010 Oracle products: Sun Fire X4270 servers, Sun ZFS Storage Appliance, Oracle Real Application Clusters, Oracle Database, Oracle Solaris

Think FasT. now Think FasTer! keeping your daTa cenTer MoVing

aT The speed oF Business

can Be a chaLLenge. Tallgrass Technologies provides outstanding delivery of advanced infrastructure solutions with Oracle. We have deep expertise in hardware, software and services to design a best in class system to support and grow your business.

TaLLgrass TechnoLogies and oracLe® wiLL heLp you achieVe your goaLs wiTh inTeLLigenT soLuTions designed To: » Dramatically enhance performance of multi-threaded applications with Oracle’s SPARC T3 servers. » Drastically reduce transaction times with Oracle’s Sun Storage F5100 Flash Arrays. » Seamlessly integrate data sharing between multiple environments with Oracle’s Sun ZFS Storage Appliances.

To geT your daTa cenTer up To speed, conTacT us Today. VisiT

www.tallgrassadvantage.com © 2011 Tallgrass Technologies. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. All company logos and product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

SEPTEMBER/OCTOBER 2011 ORACLE.COM/ORACLEMAGAZINE

45

One of the things that is important for CBOE Holdings as it continues to grow is ensuring that it can optimize its IT hardware and deployment platform for its core Java applications. “Everything we do is Java, so we depend on the Java Virtual Machine that comes from Oracle,” says O’Connell. CBOE Holdings leverages its expertise in Java across all of its applications and has been at the forefront of providing feedback to Sun—and now to Oracle—on how to deploy and run high-performance Java. In the end, CBOE Holdings’ business performance depends entirely on its IT infrastructure. And CBOE Holdings’ IT infrastructure is built on Oracle hardware and software. From O’Connell’s perspective, that’s a pretty solid foundation. “We have a very good relationship with Oracle,” says O’Connell. “We’re actually very close with Oracle, and to have Oracle in control of more of the software stack and having a single point of contact is a very good thing for us.”

Virtualization, Clustering, and the Cloud When it comes to scalability, whether it’s up, out, or in the cloud, organizations are turning to x86-based servers for their clustering, virtualization, and cloud computing capabilities. Virtualization allows different applications to share a single

piece of hardware. Clustering allows organizations to bring multiple individual hardware servers together to have them act as a single machine. “You can view x86 servers as building blocks for grids or clusters, or they can be used in blade systems so that multiple

blades can be housed together in the same chassis and managed easily,” says Jean S. Bozman, research vice president of the Enterprise Platforms Group at IDC. “The x86 server space is very, very popular for virtualization because it improves the utilization of the servers that you’re purchasing.”

“Moving to x86-based servers wasn’t only a good idea; it was essential to our business.” —Gerald O’Connell, Executive Vice President and CIO, CBOE Holdings

BUSINESS IS BUILT ON TRUST AND RELIABILITY. Your employees look to YOU to provide timely, reliable access to the information they need to succeed. It’s time to trust your data center.

OPTIMIZE PERFORMANCE, MAXIMIZE DATA PROTECTION, AND REDUCE COSTS WITH ORACLE IN YOUR DATA CENTER...

With the industry’s broadest portfolio of highperformance, cost effective, and easy-to-manage solutions - backed by the support of an Oracle Platinum Partner - you can rest assured that your users are as efficient as the data center they rely on.

» Oracle’s Sun Storage F5100 Flash Array accelerates database applications 2x, delivers up to 7x faster transaction times, and up to 180x better power efficiency.

Trust your Oracle needs to a proven solutions provider with the Oracle-backed experience and expertise to build, implement and support your business every step of the way. Trust CBTS.

» Oracle’s Sun ZFS Storage Appliance enables Oracle Database and Application software to run faster and more efficiently in NAS environments with automated and intelligent data placement within its storage hierarchy. » Oracle’s SPARC T3-2 server with 32 cores delivers up to 256 simultaneous threads to help consolidate legacy servers, save valuable datacenter real estate, and cut operational costs.

Discover why so many Fortune 500 companies trust CBTS for maximizing enterprise-wide IT efficiency.

Visit www.cbtsadvantage.com © 2011 CBTS. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. All company logos and product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

ORACLE MAGAZINE SEPTEMBER/OCTOBER 2011

CBOE Holdings’ business performance depends entirely on its Oracle hardware- and softwarebased IT infrastructure. “To have Oracle in control of more of the software stack and having a single point of contact is a very good thing for us,” says CBOE Holdings’ Gerald O’Connell.

READ more about Sun x86 servers oracle.com/us/products/ servers-storage/servers/ x86 Oracle x86 Rack Servers: Optimized for Rapid Deployments and Operational Efficiency bit.ly/pwtBdA Sun Blade servers oracle.com/us/products/ servers-storage/servers/ blades Sun Fire X4800 M2 server bit.ly/nVwMtq

More and Less From rackmount and blade deployments to the incredible integration and performance of specialized appliances like Oracle Exadata, Oracle x86-based systems deliver optimized software and hardware solutions. “Our x86 servers are designed to support enterprise features,” says Ali Alasti, vice president of x86 management at Oracle. “By marrying Oracle software with Oracle hardware, we can deliver better performance density and higher performance per server.” The bottom line is that the value of Oracle x86-based servers goes beyond the performance of the chips inside. “We can provide easier installs, easier patching, easier general management, and easier support for the software and hardware combination,” Alasti concludes. “All the integration and testing work we do pays off by delivering better performance, better support, and better value.” 

David A. Kelly (davidakelly.com) is a business, technology, and travel writer who lives in West Newton, Massachusetts

September/October 2011 Oracle.com/oraclemagazine

Andrea Mandel

Next Steps

Fastest Ever

Database Performance Sun 30 Million SPARC

IBM

10 M

HP

4M

P7

Superdome

tpmC Transactions/Minute

Source: Transaction Processing Performance Council, www.tpc.org as of 12/2/10. Oracle SPARC SuperCluster with T3-4 Servers, 30,249,688 tpmC, $1.01/tpmC, available 6/1/11. IBM Power 780 Server, 10,366,254 tpmC, $1.38/tpmC, available 10/13/10. HP Integrity Superdome-Itanium2, 4,092,799 tpmC, $2.93/tpmC, available 8/6/07. More at oracle.com/sunoraclefaster.

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

48

by David a. kelly

Getty Images

Organizations eliminate data integration bottlenecks and keep information in sync with Oracle data integration solutions.

September/October 2011 Oracle.com/oraclemagazine

C

loud computing, the data volume facing most organizations, and the need for real-time data and reporting are all driving a rethink of data integration solutions.

Jaime Ferrand/Staff

“As organizations move to elastic and highly scalable environments, dynamic data movement for synchronizing or replicating databases becomes very important,” says Carl Olofson, research vice president of database management and data integration software research at International Data Corporation (IDC). “Organizations typically have huge amounts of data in their internal systems that are not aligned and can’t be used easily in a data integration project. That’s why it’s critical for organizations to think about data integration strategically, not project by project.” That’s just one of the reasons why the old standby of doing data integration internally or creating one-off data integration solutions doesn’t work anymore. According to Olofson, in nearly all cases, internal development of data integration solutions is at the least a bad choice, and it could even be a disastrous choice, because the results can be so limiting. “You won’t be able to extend it, you can’t use it as part of a strategic initiative, and you have to maintain it for all time. It’s going to be an ongoing cost,” says Olofson.

50 snapshots

IQNavigator iqnavigator.com While elastic environments, data volume, “Data replication and integration are Location: Denver, Colorado and the need for efficient data integration critical for us as a SaaS [software as a service] Employees: 300-plus operations are driving integration strategies, provider, especially with the types of processes Oracle products: Oracle Database 11g, Oracle Financials, Sun Fire X4170 M2 server, SPARC the need for real-time data and reporting we automate for our clients,” says Martin. T3-1 server, Oracle Exadata, Oracle Solaris, reflects a change in how integrated data is IQNavigator is a leading provider of serOracle WebLogic Server, Oracle TopLink, Oracle viewed and used. Business users want to run vices procurement and extended workforce Business Intelligence Enterprise Edition a query and get an idea of how the business solutions. Global 2,000 companies use is doing as of a few minutes ago, instead of IQNavigator to help clients manage workAvea avea.com.tr yesterday or last week. force and services procurement in real-time, Location: Istanbul, Turkey “We’ve expanded the realm of business through IQNavigator’s SaaS applications. Employees: 2,600 intelligence beyond just the business ana“Our solution handles the sourcing of serRevenues: US$7.5 billion lysts who are doing long-term planning to vices, from identifying the right supplier to Oracle products: Oracle Database 11g, include a wide range of business users who negotiating and recording the contract to Oracle Data Integrator Enterprise Edition, Oracle GoldenGate, Oracle Exadata, Oracle are making tactical decisions,” Olofson says. payments for services,” says Martin. “We Business Intelligence Enterprise Edition, Siebel “And these people are more interested in upcan even track the workers onsite that are Customer Relationship Management to-the-minute data.” delivering those services and reconcile them That’s where Oracle Data Integrator against the contracts.” and Oracle GoldenGate—strategic data integration solutions from IQNavigator has to undertake an average of 10 different businessOracle—come in. critical integrations with each client’s internal systems. From master data transactions to personnel information to time cards, it all needs Oracle Data Integration Solutions to be integrated in real time with IQNavigator’s applications. Oracle Data Integrator is a high-performance, batch-oriented data IQNavigator is now using two Exadata Database Machine X2-2 integration solution for heterogeneous data integration. It provides systems from Oracle as the foundation of its next-generation online the ability to move data across different systems and transform it transaction processing (OLTP) applications and real-time reporting as necessary, regardless of whether it’s coming from or going to an services. From enabling IQNavigator to migrate to Oracle Exadata Oracle-based system. without downtime in a phased model to synchronization in the According to Alok Pareek, vice president of product manageOracle Exadata production environment with standby systems, ment for data integration at Oracle, Oracle Data Integrator performs Oracle GoldenGate is IQNavigator’s key to ensuring accurate and because it doesn’t use a midtier server like other data integration consistent data. solutions. “Oracle Data Integrator takes a next-generation data IQNavigator decided on the combination of Oracle Exadata and integration approach that extracts the data from the source system, Oracle GoldenGate because with more than 3 million users—from loads it into the target system, and then transforms the data,” says many Global 2000 companies—spread across 113 countries and thouPareek. “Thus you don’t have the performance cost of midtier server sands of suppliers, it needed a high-performance, integrated system. processing, and you get to leverage the computing power of the data“We need to be able to process financial transactions with 100 base or data warehouse.” percent transactional integrity while processing close to US$10 Oracle GoldenGate is Oracle’s solution for dynamic data integration billion annually,” says Martin. “The scalability that’s inherent in needs—where organizations need to have up-to-the-minute informa- Oracle Database and the Oracle Exadata platform is a huge benefit tion to make business decisions or want new ways of offloading expen- for our clients.” sive legacy systems. “Oracle GoldenGate provides high-performance With the new architecture based on Oracle Exadata and Oracle data integration with transactional integrity across heterogeneous GoldenGate, IQNavigator expects to be able to improve system systems,” says Pareek. “It can scale to very high volumes.” throughput and increase performance of complex queries by an While Oracle Data Integrator approaches data integration on a average of 10 times. An added benefit is the new real-time reporting table-by-table basis, Oracle GoldenGate is designed to keep transcapabilities from IQNavigator’s transactional system, which will actional data consistent as it moves data from a source to target enable the company to deliver a whole new class of real-time services system. “With Oracle GoldenGate you can have applications that are to its customers. deployed against your target system that continue to read or analyze “For high-volume global clients, real-time reporting is extremely data even as information is being updated,” says Pareek. important,” says Martin. “Gaining faster access to real-time data opens up the door to adding new types of services and capabilities for High-Performance Data Integration our clients.” When it comes to cloud-based applications and services, data replication and integration might not be the first things that come to Real-Time Data for Real-Time Business mind. But they are key for John Martin, COO of Denver, Colorado– Few industries move faster than telecommunications, especially based IQNavigator. when it comes to mobile services. Staying competitive in that industry

September/October 2011 Oracle.com/oraclemagazine

51

“The scalability that’s inherent in Oracle Database and the Oracle Exadata platform is a huge benefit for our clients.” —John Martin, COO, IQNavigator

Ray Ng

Oracle GoldenGate is key to ensuring accurate and consistent data for IQNavigator, a services procurement and workforce solutions provider. “Data replication and integration are critical for us as a SaaS provider, especially with the types of processes we automate for our clients,” says IQNavigator COO John Martin.

requires an IT and business information platform that can deliver near-real-time data to business analysts, marketing, sales, and customer support. Take the case of Avea, a leading provider of communications services to more than 12 million customers in Turkey. When Avea needed to ensure it could move data between its production systems, an operational data store, and its data warehouse environment rapidly enough to keep up with business requirements, it turned to Oracle Data Integrator, Oracle GoldenGate, and the Oracle Exadata Database Machine. “Rapid replication is critical in some areas, such as our mobile number portability systems,” says Özlem Topçakan, business intelligence manager at Avea. “If there’s any latency, we need to be able to take immediate action.” As with most mobile phone companies, new customer acquisition

is critical. But servicing new customers effectively requires business users and managers that have the most-accurate and -up-to-date information available. Avea needed to create a new data warehouse with a highperformance daily extract, transform, and load (ETL) process and the ability to provide high-performance user queries. One of the challenges it faced was an ETL processing window that was increasing day by day. It needed a new architecture that would meet expanding business requirements. The company redesigned its systems so that production systems were replicated using Oracle Data Integrator to an operational data store (ODS), which was then used to feed the data warehouse running on Oracle Exadata. In addition, the ODS became the primary source for online operational reports, which avoids putting a query

oracle magazine September/October 2011

52

Avea relies on Oracle GoldenGate, Oracle Data Integrator, and Oracle Exadata for rapid data replication between systems. “Most of the time we replicate from the production systems to the ODS in just three seconds,” says Özlem Topçakan (right), business intelligence manager at Avea, pictured with Hanife Keskin, ETL supervisor.

load on the production systems. Avea developed an innovative solution that uses Oracle GoldenGate and Oracle Data Integrator to feed its data warehouse. With the new solution, each day 500 million rows of data are moved from source systems into the data warehouse. Avea uses Oracle Data Integrator for its ETL operations, and it uses Oracle GoldenGate to replicate data to its ODS, which allows Avea’s business users to access data in near-real time. “We manage all our ETL windows within Oracle Data Integrator. We use Oracle GoldenGate for replicating the data from production systems to the ODS,” says Topçakan. “The ODS is used for online reports using interactive reporting tools.”

Topçakan further explains that Avea automatically manages latency on its operational data store environment through Oracle Data Integrator. At the start of the ETL process, Oracle Data Integrator checks the Oracle GoldenGate logs for latency and then automatically switches source systems if needed. “Most of the time we replicate from the production systems to the ODS in just three seconds, so business users can reach the data extremely quickly,” says Topçakan. When Avea moved its data warehouse to Oracle Exadata from IBM p5 servers, the ETL performance improved by four times. Prior to migration, the batch processing window had been 12 hours, and now on Oracle Exadata it’s down to 3.5 hours. At the same time, the

Oracle has released updates of Oracle GoldenGate and Oracle Data Integrator. Oracle GoldenGate 11.1.1.1.0 is a bundled set of patches for Oracle GoldenGate 11g Release 1 that includes features for increased data security, deeper integration with Oracle Database, and expanded heterogeneity. Oracle GoldenGate can now replicate data protected by Oracle Advanced Security. Oracle GoldenGate can also now integrate with Oracle Recovery Manager for improved archive log management and recovery, and it

supports databases including SQL Server 2008 Release 2, Sybase Adaptive Server Enterprise 15.5, and DB2 10 for z/OS. Oracle Data Integrator 11.1.1.5.0 is a bundled set of patches for Oracle Data Integrator 11g Release 1 that includes Oracle Data Integrator load plans, which organize at a high level execution of packages and scenarios. The update also offers Web services enhancements such as container-based authentication, asynchronous Web services and callbacks, and full SOAP

September/October 2011 Oracle.com/oraclemagazine

support for outbound calls. “Our latest Oracle GoldenGate and Oracle Data Integrator product updates demonstrate Oracle’s unique strategy to integrate its best-of-breed data integration solutions with Oracle’s stack, while expanding on heterogeneity,” says Alok Pareek, vice president of product management for data integration at Oracle. ”Oracle continues to enhance its pervasive data integration offering to deliver fast time to value to customers.”

Ali Kabas/Getty Images

Oracle GoldenGate and Oracle Data Integrator Updated

53

performance improved by an average of 10 times. Now that Avea has an operational data store, it can refresh its data warehouse multiple times a day, if needed. “That’s a prerequisite for a near-real-time data warehouse, so it’s a big plus for us,” says Topçakan. Prior to implementing Oracle Data Integrator, Avea used a combination of tools and utilities to do its data movement. “Before Oracle Data Integrator, we had to look at separate UNIX jobs, individual ETL tools, and different kinds of systems,” says Topçakan. “Now, we can manage all our data integration through Oracle Data Integrator.” Using Oracle Data Integrator to manage data integration has also simplified the development and management process for Avea. “Oracle Data Integrator is a very easy tool for development,” says Topçakan. “It’s easy to learn, easy to develop with, and easy to share information with other team members.”

“Data is simply growing at an exponential rate,” says Oracle’s Pareek. “And the IT infrastructures that used to work fine are starting to deteriorate because they simply can’t cope with the volumes of data that organizations are dealing with.” That’s why now is a good time for organizations to take a step beyond viewing data integration as a utility and recognizing it as something more. “It’s important to remember that data integration isn’t just plumbing,” says IDC’s Olofson. “Data integration is really the underlying technology for a more mature and unified enterprise information management environment. Everyone needs to be thinking about data integration strategically.” t

STRATEGIC DATA INTEGRATION When it comes to data integration, doing nothing isn’t a viable strategy. For most organizations, simply addressing the continuous growth of data volumes will require a re-evaluation of data strategy regardless of the effectiveness of an organization’s current solution.

NEXT STEPS

David A. Kelly (davidakelly.com) is a business, technology, and travel writer who lives in West Newton, Massachusetts.

LEARN more about Oracle data integration solutions oracle.com/goto/dataintegration

www.InsightSpecialized.com

InsIght Moves Your technologY

goals Forward

Now more than ever it’s important to have an IT solutions partner who can help you accomplish more of your IT goals, achieve new efficiencies and realize cost savings every step of the way. Whether you need fast access to essential technologies or the support of technical experts for the strategic planning and deployment of solutions, Insight can help.

» Significantly improve multi-threaded application speeds with Oracle’s SPARC T3 servers. » Increase productivity and ecoefficiency by reducing transaction times with Oracle’s Sun Storage F5100 Flash Array. » Optimize multiple data center management with Oracle’s Sun ZFS Storage Appliance. Let us map out a better enterprise data center solution for you. Visit

www.InsightSpecialized.com

© 2011 Insight. Oracle and Java are registered trademarks of 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.

ORACLE MAGAZINE SEPTEMBER/OCTOBER 2011

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

Java Developer

Oracle ADF By Frank Nimphius

Oracle Jdeveloper 11g and Oracle application development framework

All Aboard

Build train models for navigation in bounded task flows.

D

evelopers of Oracle Application Development Framework (Oracle ADF) applications usually implement page navigation by defining control flow cases between activities in unbounded and bounded task flows. An additional navigation configuration option is available for bounded task flows: defining a train. At runtime, a train guides users through a predetermined sequence of views required for completing a multistep task—for example, a series of wizard pages for making multiple form-field edits. In Oracle ADF, you define a train through a train model object, which can be a bounded task flow. In walking through this column, you will explore a completed sample application to learn how to build and use train models in your own Oracle ADF applications. To begin, download the sample application from bit.ly/kXOHGa and unzip the o51adf-409578.zip file. Ensure that you’re using the studio edition of the Oracle JDeveloper 11g 11.1.1.5 (or later) release, available as a free download on Oracle Technology Network at oracle.com/technetwork/ developer-tools/jdev/downloads. The application uses the HR schema available by default in Oracle Database.

Sample Application Overview Launch Oracle JDeveloper and browse to the directory where you unzipped the sample application. Open the AdfTrainSample.jws workspace from inside the AdfTrainSample folder. Before proceeding, adjust the properties of the hrconn connection in the Application Resources panel of the Application Navigator and confirm that you can successfully connect to the HR schema. The sample application consists of two bounded task flows: 1. adf-region-btf.xml, shown in Figure 1, is the parent bounded task flow, which

is exposed as a region in Main.jspx. This task flow contains two view activities and one task flow call activity. The dashed arrows between the activities indicate that this bounded task flow is configured as a train model and the activities therein as train stops that users navigate at runtime. 2. employees-btf.xml, shown in Figure 2, is a subprocess called from the task flow call activity in the adf-region-btf .xml task flow. It represents a threestep form-entry wizard for editing employee records, also configured for train navigation. To run the sample application, expand the ViewController project in the Application Navigator, right-click the Main .jspx page in the Web Content folder, and choose Run. On the browser page that opens, select an employee record and click

either the Employee Edit train stop or the Create New Employee button. Both actions enter the employees-btf subtask flow, enabling you to edit the selected (or newly created) employee’s data. Use the train UI component or the Next and Previous buttons to continue navigation and complete the task. On the last wizard page, click either Cancel to start over again or Submit to see a summary page with the changes applied. (To preserve the state of your HR schema, the sample application does not commit changes to the database.) Enabling Train Models You can declaratively configure a bounded task flow to expose a train model for use on the pages and page fragments it contains by setting the task flow’s Train property. On the pages and page fragments, you use the Oracle ADF Faces af:train and

Figure 1: Parent task flow exposed as an Oracle ADF region

Figure 2: Subtask flow for editing employee records

oracle magazine September/October 2011

55

56

Oracle ADF

af:trainButtonBar components to render the train model in the UI. Expand the sample application’s View Controller project in the Application Navigator. Open the Web Content folder and its WEB-INF folder, and then double-click employees-btf.xml to open it in the task flow visual editor. Next, open the Oracle JDeveloper Structure window (Ctrl-Shift-S). Under ADF Task Flow, select task-flow-definition – employees-btf. Open Oracle JDeveloper’s Property Inspector feature (Ctrl-Shift-I), and scroll to the Behavior section. You can see that the Train property is set to true to expose the task flow train model at runtime. (The default value is false.) Defining Train Stops and the Navigation Sequence After enabling a bounded task flow to expose a train model, you need to define view activities and task flow call activities as train stops. To add an existing view or task flow call activity to the train model, select the activity in the visual editor, right-click, and then select the Train option to create (or, later, to change) the train stop setting. Any view and task flow call activities you add to the task flow diagram are automatically added to the sequence of train stops. The navigation sequence is defined initially by the order in which view activities and task flow call activities are added as train stops. You can easily change this order via the Train option in the context menu associated with each activity. Train stops are further configured with the Property Inspector. To see how, select the EmployeeDetails view activity in the sample application’s employees-btf.xml task flow and open the Property Inspector. Scroll to the Train Stop section to see the train stop properties, as shown in Figure 3. The Train Stop section appears in the Property Inspector only if the bounded task flow’s Train property is set to true and the selected activity is either a view activity or a task flow activity. Train Stop Properties As you can see in Figure 3, train stops are configured with five properties:

Figure 3: Train stop configuration properties for the EmployeeDetails view activity

1. The Train Stop property is set to false by default. When set to true, it indicates that the view or task flow call activity is a train stop. You set this property at design time and cannot change it at runtime. 2. The Outcome property enables you to associate a control flow name with a train stop to define an alternative navigation case. The Outcome property can be read from Java code at runtime but not changed then. 3. The Sequential property defines the order in which users navigate the train stops. If a train stop’s Sequential property is set to true, users can access that stop only if they have already visited the preceding stop. A value of false defines nonsequential navigation, enabling the user to select any train stop whose Skip property is not set to true. You can use Expression Language (EL) to define the Sequential property value for a train stop dynamically. The sample application for this column demonstrates this usage. 4. The Skip property disables a train stop so that users cannot navigate to it. Like the Sequential property, the Skip property can be dynamically changed at runtime. 5. The Ignore property prevents a train stop from rendering in the UI. Although you can use EL to set the Ignore property

September/October 2011 Oracle.com/oraclemagazine

value dynamically at runtime, it can be changed only upon task flow initialization. Once a task flow is rendered, the Ignore property is immutable. Building the Train User Interface Oracle ADF Faces provides two UI components for displaying train models in your JavaServer Faces pages and page fragments: 1. The train component, af:train, displays the train model as a horizontal or vertical line with icons indicating the stops. The train component is used for both sequential and nonsequential navigation. To define display names for each stop, you edit the task flow. If it’s not open already, double-click the employeesbtf.xml file in the Application Navigator and expand the EmployeeDetails view activity node in the Structure window. Expand the train-stop node, as shown in Figure 4, to see the Display Name configuration. You add the display name element by using the right-click context menu on the train-stop node and selecting Insert inside train-stop -> Display Name. You then edit the Display Name value with the Property Inspector. You can also use EL to read the displayname value from a message bundle to

57

you must change the train component tag, as shown in Listing 2. In this changed tag configuration, af:commandNavigationItem is added to the nodeStamp facet to replace the default train stop renderer. The EL trainNode variable is defined on the var attribute of af:train to access the train stop properties defined in the train model. Note that the trainNode variable is also accessible from a managed bean (MBean) during train rendering. An example that uses this option to set the af:commandNavigationItem component’s immediate property is explained in the “Enabling Backward Navigation” section later in this column. Figure 4: Display name configuration for a train stop in the Structure window

support translated labels for internationalized applications. 2. The trainButtonBar component renders the train model with buttons for forward and backward navigation. Navigation with trainButtonBar is strictly sequential and does not skip over disabled train stops. If a train stop is configured with the Skip property set to true, the trainButtonBar will stop there. The Oracle ADF Faces train and trainButtonBar components are located in the Oracle JDeveloper Component Palette window under ADF Faces -> Common Components. When you drag either component to a page or a page fragment in a bounded task flow whose Train property is set to true, Oracle JDeveloper automatically sets the component’s value property, as shown in Listing 1, for each component. The EL string within each of these component tags references the train model of the active bounded task flow to determine the current stop in the navigation sequence. By doing so, it also determines the preceding and next stops in a sequential train. Customizing the Train User Interface To customize the train stop behavior—for example, to add an action listener to a train stop, to mark a train stop as required, or to use your own custom icons for train stops—

Enabling Reuse via a Page Template As you know from the preceding section, the af:train and af:trainButtonBar components reference the bounded task flow train model by using the generic expression #{controllerContext.currentViewPort .taskFlowContext.trainModel}

Because this string does not include the task flow name, train configuration is a good candidate for use in page templates. However, when you add a train component to a template, the value property is not automatically set, as it is when you add it

directly to a page. To set it for a template, open the Property Inspector for the selected train component and click the arrow icon next to the value property. Then choose the Expression Builder menu option to declaratively compose the expression #{controllerContext.currentViewPort .taskFlowContext.trainModel}

The sample application uses a page template, PageFragmentTemplate.jspx, to add the train definition to all views in the bounded task flows. In the Application Navigator, the template is under the ViewController project, in the templates folder inside the Web Content folder. When you reference an MBean from the train component configuration, as the sample application does, make sure the MBean is configured with the same name in all bounded task flows that use the page template. Including Method Call Activities in Train Navigation Train navigation occurs among view and task flow call activities, but in some use cases, you might also want to call a method as part of the navigation. To include method call activities in train navigation, you set the train stop’s Outcome property to reference a wildcard control flow case. As you can see in Figure 2, the

Code Listing 1: Automatic default settings for train components

Code Listing 2: Changing the settings of a train component

oracle magazine September/October 2011

58

Oracle ADF

At runtime, a train guides users through a predetermined sequence of views required for completing a multistep task—for example, a series of wizard pages for making multiple form-field edits.

HumanResourceDetails task flow activity’s Outcome property is set to humanResourceDetailsStop to invoke an MBean method before the view activity is accessed. By using a router activity instead of a method call activity, you can even divert train navigation based on an EL-accessible condition. Suppressing Model Validation If your application includes a train that uses multiple Oracle ADF–bound forms that update the same data control, you might need to disable model validation. Otherwise, errors can occur if required fields are located later in the navigation sequence. To disable Oracle ADF model validation temporarily, set the SkipValidation property in a view’s page definition file to custom. In the sample application, Oracle ADF model validation is disabled when users create a new employee record. To see the configuration for the EmployeeDetails view activity, select the EmployeeDetailPageDef .xml file in the oramag.adf.sample.view .pageDefs package of the ViewController project’s Application Sources folder. Open the Structure window and select the EmployeeDetailPageDef root node to see the SkipValidation property setting in the Property Inspector. Enabling Backward Navigation In a multistep form edit, as shown in Figure 2, users might need to navigate to a previous step without providing all the required information on the current page. To enable backward navigation without validating the current form, you must set the train stop’s immediate property to true, using the following steps: 1. Customize the train user interface with a commandNavigationItem (as explained under “Customizing the Train User Interface,” above). 2. Create an MBean method that references #{trainNode} to compare the rendered DOWNLOAD Oracle JDeveloper 11g Release 2 oracle.com/technetwork/developer-tools/jdev/ downloads READ more about this release oracle.com/technetwork/developer-tools/jdev/ documentation

train stop with the current selected stop. 3. If the rendered train stop is before the current train stop, the method should return true. Otherwise, the method should return false. 4. Using EL, reference the MBean from the immediate property of the commandNavigationItem. The sample application implements the immediate = true behavior in the train component in the PageFragmentTemplate .jspx template. The MBean referenced by commandNavigationItem is configured in both bounded task flows. Navigating the Train Model from Java Code You might have a use case that requires programmatic navigation in a train model. For example, you might want to create a “finish” option that skips all remaining steps in a train sequence or build your own trainButtonBar solution that can step over train stops that are marked as skipped. The JobDetails view activity in the sample application’s employees-btf.xml bounded task flow definition contains two af:commandButton components, for forward and backward navigation. The button action properties reference an MBean method that accesses the train model to determine what the next navigation stop is and whether that stop needs to be skipped. To determine if a train stop needs to be skipped, the MBean method accesses the same HashMap bean that is used to configure the Skip property on the train stops in the bounded task flow. Conclusion Train navigation is a valuable addition to control flow cases in Oracle ADF bounded task flows. In this column, you’ve learned how to create train models from bounded

September/October 2011 Oracle.com/oraclemagazine

task flows, including some advanced techniques you can use in your Oracle ADF application projects. To learn more about creating train models, see Section 18.8, “Creating a Train,” in Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 1 (11.1.1.5.0). 

Frank Nimphius is a senior principal product manager for Oracle JDeveloper and Oracle Application Development Framework. He is a coauthor of 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 oracle.com/technetwork/developer-tools/adf/ overview Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 1 (11.1.1.5.0) bit.ly/kRh9QL 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 READ more Nimphius Oracle ADF Code Corner bit.ly/hP3CBU OTN Harvest blog blogs.oracle.com/jdevotnharvest DISCUSS Oracle JDeveloper and Oracle ADF Oracle JDeveloper Oracle Technology Network forum bit.ly/fPiamS DOWNLOAD the sample application for this column bit.ly/kXOHGa Oracle JDeveloper and Oracle ADF oracle.com/technetwork/developer-tools/jdev/ downloads

Database Developer

Oracle Data Provider for .NET By Christian Shay

Oracle Data Provider for .NET

Distinctive Database Development Use Entity Framework with Oracle Data Provider for .NET.

O

bject-relational mapping (ORM) technologies enable developers to write object-oriented code against a conceptual model of their data rather than accessing the database directly. For example, with an ORM, a developer can make an update to an instance of an EMPLOYEE class or request a collection of EMPLOYEE instances rather than executing the equivalent UPDATE or SELECT SQL statements against the EMPLOYEES table in an Oracle database. Two popular ORMs for .NET developers are NHibernate and Microsoft’s Entity Framework. Both solutions integrate with Microsoft Visual Studio; leverage designers, wizards, and code templates; and require a layer of code to handle the mapping to an Oracle database. Oracle Data Provider for .NET (ODP.NET) and Oracle Developer Tools for Visual Studio provide that mapping layer as well as Visual Studio enhancements to enable .NET developers to target Oracle Database with their Entity Framework applications. In this article, I’ll demonstrate some common development tasks that use Entity Framework to target Oracle Database. (For information on NHibernate, visit nhforge.org.) Setup To work through the examples in this article, you’ll need Microsoft Visual Studio 2010 Professional (or a later release). You’ll also need to download and install ODP.NET and Oracle Developer Tools for Visual Studio Release 11.2.0.2.40 or later. Both of these products are included as part of the Oracle Data Access Components package, which can be downloaded from the Oracle Technology Network .NET Developer Center (oracle .com/technetwork/dotnet). All of Oracle’s .NET products are available for free from the

Oracle Technology Network Website. It’s important to note that earlier versions of this Oracle .NET software do not support Entity Framework; the integrated Visual Studio designers will not work properly, and you will see errors such as “The store provider factory type ‘Oracle.DataAccess.Client .OracleClientFactory’ does not implement the IServiceProvider interface” if you attempt to run already compiled Entity Framework code. If you are unsure of which version of the Oracle .NET software you have, you can check the About window (Help -> About Microsoft Visual Studio). You should see “Oracle Developer Tools for Visual Studio” followed by the version number. The application you create in this article will use tables that are part of the Oracle database sample HR schema, so you’ll also need access to an Oracle database and the HR schema. To begin the setup for the article, first confirm that you have a connection to the HR schema. From Visual Studio, right-click the Data Connections node in Server Explorer and choose Add Connection. When the dialog box appears, make sure that the Data source is set to Oracle ODP.NET. From the Data source name list, select the database alias and then provide the User name and Password for the HR schema. Download code and SQL scripts for this article at bit.ly/rhjzrG and extract the contents. From the Tools menu in Visual Studio, select Run SQL Plus Script. Browse to the location where you extracted the code and SQL scripts, select the HR_SP.SQL script, select the HR connection from the list, and click Run. Check the Visual Studio output window to make sure there were no errors. Right-click the Procedures node in Server Explorer and choose Refresh.

The Entity Data Model Wizard and Designer Use the Visual Studio Entity Data Model Wizard as the starting point for building an Entity Framework–based application. To begin, create a new project. From the File menu select New -> Project. Select Visual C#:Windows -> Console Application. Rename the project EntityFramework, and click OK. Open Solution Explorer, right-click EntityFramework, and select Add -> New Item. In the Add New Item window, choose ADO.NET Entity Data Model, enter HRModel .edmx as the Name, and click Add. The Entity Data Model Wizard will appear. In the first screen, Choose Model Contents, select Generate from database, and click Next. Select the data connection that you created earlier and the Yes, include the sensitive data in the connection string radio button. Enter HREntities in the text field under the Save entity connection settings in AppConfig as checkbox and click Next. In the Choose Your Database Objects screen, select DEPARTMENTS and EMPLOYEES from the Tables group and INSERT_EMPLOYEE, UPDATE_AND_RETURN_SALARY, and UPDATE_AND_RETURN_SALARY_BINDV from the Stored Procedures group. Enter HRModel in the Model Namespace field and click Finish. The Entity Data Model Designer will open and display a visualization of the newly created Employee and Department entities and their relationships, as shown in Figure 1. Entity SQL Now that you have some entities backed up by ODP.NET and Oracle Database, you can perform operations on them. As entities are created, modified, and deleted, these

oracle magazine September/October 2011

59

60

Oracle data Provider for .NET

changes will be automatically reflected seamlessly in Oracle Database. Let’s add some program code to try this out. In Solution Explorer, right-click References and add a reference to Oracle .DataAccess.dll. If multiple versions of the DLL are present, reference Release 11.2.0.2.40 or later. Replace the contents of your new project’s program file (Program.cs) with the code of the Program.cs file included in the code download for this article. There are several namespaces at the top of the file, most importantly System.Linq, System.Data .EntityClient, System.Data.EntityModel, and Oracle.DataAccess.Client. One way to perform queries on entities is to use Entity SQL, which is a SQL-like language. It is backed by the ADO.NET data access model, so the Entity SQL code is very similar to common database-specific ADO .NET implementations such as ODP.NET. The difference is that with Entity SQL, the entities—not the database objects—are queried by Entity SQL. In your updated program file, uncomment the first block of code (// Entity SQL -- Retrieve employees with ID number less than max_id), shown in Listing 1. Build and step through the code. Note that the EntityConnection, EntityCommand, and EntityDataReader classes are created and data is fetched from these entities. LINQ to Entities Another way to perform queries on entities is to use LINQ to Entities. LINQ (LanguageIntegrated Query) is a general-purpose query facility built into the .NET Framework that accesses all sources of information, including relational data, XML, and now entities. To try LINQ to Entities, first uncomment the second block of code in the program file (// LINQ to Entities query -Retrieve employees with ID number less than max_id) shown in Listing 2 and examine it. The first section of this code block contains the LINQ query: var Employees = from e in ctx.EMPLOYEES where e.EMPLOYEE_ID < max_id select e;

Code Listing 1: Entity SQL code for Program.cs // Entity SQL -- Retrieve employees with ID number less than max_id int max_id = 110; string esql = "select e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY from HREntities. EMPLOYEEs as e where e.EMPLOYEE_ID < " + max_id; EntityConnection econn = new EntityConnection("name=HREntities"); econn.Open(); EntityCommand ecmd = econn.CreateCommand(); ecmd.CommandText = esql; EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess); Console.WriteLine("Entity SQL Result"); while (ereader.Read()) { Console.WriteLine("ID: " + ereader.GetValue(0) + " Name: " + ereader.GetValue(1) + " Salary: " + ereader.GetValue(2)); }

Code Listing 2: LINQ to Entities code for Program.cs // LINQ to Entities query -- Retrieve employees with ID number less than max_id int max_id = 110; var Employees = from e in ctx.EMPLOYEES where e.EMPLOYEE_ID < max_id select e; Console.WriteLine("LINQ to Entities Result"); foreach (var Employee in Employees) { Console.WriteLine("ID: " + Employee.EMPLOYEE_ID + " Name: " + Employee.FIRST_NAME + " Salary: " + Employee.SALARY); }

LINQ queries are designed with the FROM clause first so that the Visual Studio development environment can perform type checking and enable intellisense on the queries as they are typed. The remaining code in this block fetches and displays employee records. Step through this code and view the results. Calling Stored Procedures Oracle developers can leverage PL/SQL stored procedures, with limitations, within the entity framework via Entity Framework Function Imports (used to call the procedures explicitly) and stored procedure mappings (which are automatically called for entity Insert, Update, and Delete operations). Only Oracle stored procedures can be called by Entity Framework, not stored functions. (Oracle stored functions can be used if they are wrapped inside of a stored procedure that uses an OUT parameter for the stored function return value.) The

September/October 2011 Oracle.com/oraclemagazine

Entity Framework maps Oracle stored procedures to Entity Framework functions. If an Entity Framework function mapped to a stored procedure is to have a return value other than null, the value must be provided by the Oracle stored procedure as an OUT parameter of the type SYS_REFCURSOR. Furthermore, only the first REF CURSOR in the parameter list is used as the Entity Framework function return value; any other REF CURSORS in the list are ignored. And because the metadata for this REF CURSOR is not known at design time, you must add this information to your App.Config file. Running stored procedures with SYS_ REFCURSORs and App.Config configuration. To try out PL/SQL stored procedures with entities, first update the App.Config file. In the files you downloaded, find App.Config.txt and cut and paste the contents into the App .Config file in your project somewhere after the section. (For more information on the format of this metadata,

61

refer to ODP.NET online help in the “Implicit REF CURSOR Binding Support” section.) Earlier you ran a script (HR_SP.SQL) that created the UPDATE_AND_RETURN_SALARY procedure and added it to the entity model. This procedure accepts an employee ID and a salary increase amount, and then it outputs a REF CURSOR containing the employee’s first name and the new salary. To get your application ready to call the UPDATE_AND_RETURN_SALARY procedure, first open the Model Designer, and then view the Model Browser (see Figure 1). Under HRModel.Store, click the Stored Procedures node, and then right-click UPDATE_AND_ RETURN_SALARY. From the menu, choose Add Function Import. In the Add Function Import dialog box (see Figure 2), for Returns a Collection Of, select Complex. Click Get Column Information. The column information will be retrieved from the App.Config file. To call the method from .NET, you will use the name listed in the Function Import Name field (in this case, UPDATE_AND_RETURN_ SALARY). Click Create New Complex Type and click OK. In the Model Browser, you will now see UPDATE_AND_RETURN_SALARY under HRModel.edmx -> EntityContainer: HREntities -> Function Imports. In your project’s program file, uncomment the next block of code—// CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (RETURNING VALUES)—shown in Listing 3. Build and step into it. Take a look at the output window and note that the employee’s first name and the updated salary are displayed there. Binding to stored procedure output values. If you only need to return a single scalar value, rather than a collection of complex types, you can bypass the need to use SYS_REFCURSORs and the resulting App .Config configuration file by binding directly to stored procedure parameter output values in your code and allowing the imported function to simply return null. Earlier you ran a script (HR_SP.SQL) that created the UPDATE_AND_RETURN_ SALARY_BINDV procedure and added it to the entity model. The UPDATE_AND_ RETURN_SALARY_BINDV procedure accepts an employee ID and a salary increase amount, and it includes an output NUMBER parameter

Figure 1: Entity Data Model Designer

Figure 2: Add Function Import dialog box

oracle magazine September/October 2011

62

Oracle data Provider for .NET

containing the employee’s new salary. In the Model Browser under HRModel .Store, click the Stored Procedures node, and then right-click UPDATE_AND_RETURN_ SALARY_BINDV. From the menu, choose Add Function Import. In the Add Function Import dialog box, select None for Returns a Collection Of (because the Oracle stored procedure did not include a SYS_REFCURSOR, this function cannot return a value), and click OK. In your project’s program file, uncomment the next block of code—// CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (USING OUTPUT BIND VARIABLE)—shown in Listing 3. Note that the code in Listing 3 includes the following line to set up the output bind variable: ObjectParameter newsal = new ObjectParameter("NEWSAL", typeof(decimal));

The first argument to this particular ObjectParameter constructor must be set to the name of the PL/SQL output parameter. Build the code and step into it. Take a look at the output window, and you can see that the employee’s updated salary is displayed there and no configuration change to the App.config file was required. Stored Procedure Mappings The entity model also supports “mapping” stored procedures to insert, update, and delete operations on Entities. When these operations occur, the mapped stored procedure will fire. For this article, the INSERT_ EMPLOYEE procedure will fire every time a new Employee entity is created. This stored procedure was included in the HR_SP.SQL script you ran earlier, and because it returns an output value, it must include an output SYS_REFCURSOR in its parameter list. (The metadata for that REF CURSOR was included when you earlier copied the contents of the App.Config.txt file to the App.Config file. ) To enable this stored procedure mapping, first open up Entity Designer, right-click Employee, and select Stored Procedure Mapping. This will open the Mapping Details pane at the bottom of the designer (as shown in Figure 3). In Mapping Details, select <Select

Code Listing 3: Code for calling stored procedure in Program.cs // CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (RETURNING VALUES) int empid = 100; int raise = 9000; foreach (var result in ctx.UPDATE_AND_RETURN_SALARY(empid, raise)) { Console.WriteLine("Name: " + result.FIRST_NAME + " Updated Salary: " + result.SALARY); } Console.WriteLine(); Console.ReadLine(); // CALLING A STORED PROCEDURE VIA IMPORT FUNCTION (USING OUTPUT BIND VARIABLE) int empid = 100; int raise = 1111; ObjectParameter newsal = new ObjectParameter("NEWSAL", typeof(decimal)); ctx.UPDATE_AND_RETURN_SALARY_BINDV(empid, raise, newsal); Console.WriteLine(newsal.Value);

Insert Function> and select the INSERT_ EMPLOYEE stored procedure. By default, mappings will be created between the entity and the procedure. Notice that this Oracle stored procedure accepts as its parameters all of the data for the Employee entity except for the EMPLOYEE_ID. The stored procedure uses a sequence to generate an EMPLOYEE_ ID, inserts a row into Oracle Database, and then returns the generated EMPLOYEE_ID inside of a SYS_REFCURSOR. This ensures that the Entity is consistent. Under Result Column Bindings, enter EMP_ID and press tab. EMP_ID is the name of the column in the REF CURSOR that contains the new id. When tab is pressed, a mapping is created by default to the EMPLOYEE_ID member of the entity. Uncomment the next section of code in your program file—// MAPPING A SPROC WITH SEQUENCES—which creates a new Employee entity, adds it to the Employees collection, and then saves it. After you save the program file, note that the EMPLOYEE_ID now contains the sequence value that was generated and inserted into Oracle Database. Triggers and Sequences In addition to working with stored procedures that generate sequences, developers also work with triggers that generate sequences. Developers using Entity Framework will need to make sure that their entities are able to receive the new values being generated by triggers. To try out triggers and sequences with entities, first remove the Stored Procedure

September/October 2011 Oracle.com/oraclemagazine

Mapping you just created so that there is no special processing occurring when a new Employee entity is created. Open the Entity Designer, right-click the Employees entity, and select Stored Procedure Mapping again. In the Mapping Details pane, clear the INSERT_EMPLOYEES entry by choosing <delete> from the list. Now add the trigger. From the Tools menu, select Run SQL Plus Script. Browse to the location where you extracted the code and scripts, select the triggers.sql script, select the HR connection from the list, and click Run. The INSERTEMPLOYEES trigger created by the script generates a new sequence for EMPLOYEE_ID whenever NULL is passed in for that value. If you were to attempt to create a new Employee entity now and save it, you would get a unique constraint violation, because the Entity Framework infrastructure would always pass a zero for the EMPLOYEE _ID value. Entity Framework needs to ignore the NOT NULL constraint on EMPLOYEE_ID and send Oracle Database a NULL. At the same time, Entity Framework needs to promise that a valid value will eventually be provided by the database server to the entity so that the NOT NULL constraint is not violated. Fortunately, the StoreGeneratedPattern property for entity members, such as EMPLOYEE_ID, will allow an entity to temporarily reconcile a NULL without violating a NOT NULL constraint and ensure that a value is generated to replace a NULL. Due to an issue in Visual Studio, it is not currently possible to set the

63

StoreGeneratedPattern property through the product’s user interface. Instead, to set this property, you must edit the underlying model XML file. In Solution Explorer, right-click HRModel1.edmx, choose Open with, and then XML Text Editor. Scroll down until you find the entry for EMPLOYEE_ID and after "Precision = 6"

add "StoreGeneratedPattern="Identity"

Uncomment the last block of code in your program file (// TRIGGERS WITH SEQUENCES) and execute it. Note that the value of EMPLOYEE_ID is automatically populated with the newly generated sequence. Model First Up to now, this article has followed a “Database First” paradigm—the entities were created based on whatever the database already contains. There’s another paradigm, “Model First,” in which the entities are designed and created first and then the database schema objects are created to store them. Oracle Developer Tools for Visual Studio offers a wizard to make Model First easy by automatically generating the required data definition language (DDL) scripts to create the database objects. There are currently two Model First choices for generating DDL scripts: generating a table per type and generating a table per hierarchy. To demonstrate this, you will modify the Employees entity in the Entity Designer and then regenerate the Oracle database tables needed to store the new design. In the Entity Designer, right-click the Employee entity and select Add -> Scalar Property. Name the property ADDRESS. Right-click in the white space in the Entity Designer and choose Properties. In the Properties window, change the Database Schema Name to HR, select SSDLtoOracle .tt for the DDL Generation Template, and select Generate Oracle via T4(TPT).xaml for the Database Generation Workflow property (as shown in Figure 4). These settings ensure that the Oracle-provided DDL generation

Figure 3: Entity Designer; Mapping Details

Figure 4: Entity Designer; Properties

code is used and that the Model First paradigm is implemented. Finally, right-click in the Entity Designer and select Generate Database for Model. The DDL code is generated. Click Finish to save the script to the filename in the Save DDL As field. Note that DELETE statements in the DDL code are commented out for safety. Remove the comments before you use the script to modify an existing schema.

and query that entity using Entity SQL and LINQ to Entities. Oracle developers can also use PL/SQL stored procedures, triggers, and sequences with an entity. And rather than start with the database, developers can first create an entity model and generate a database from that model.

Summary Entity Framework enables developers to work with a conceptual data model rather than a database. With Entity Framework, Oracle Data Provider for .NET, and Oracle Developer Tools for Visual Studio, developers can start with an Oracle Database, create an entity,

Next Steps

Christian Shay ([email protected]) is a principal product manager at Oracle.

LEARN more about Oracle Data Provider for .NET and Oracle Developer Tools oracle.com/technetwork/dotnet DOWNLOAD sample code for this article bit.ly/rhjzrG

oracle magazine September/October 2011

Connect legacy technologies affordably with the complete set of data integration tools from Altova® Experience how the Altova MissionKit®, the integrated suite of XML, data mapping, and database tools, can help you leverage existing technology and business software investments while integrating modern technologies – without breaking your budget.

The Altova MissionKit includes multiple intelligent tools for data integration: MapForce® – Graphical data mapping, transformation, & conversion tool

1: ion 201

Vers New in

itally rt for dig o p p u s e ignatur • XML S ML files X g nalyze signin tion to a a e r c t r t cha • Instan ase data orce d datab n a L in MapF n M o X ti a r g ® te ision in • StyleV t creation r L for repo g for ET A EDI treamin & HIPA IS • Data s D A P A T L rt for IA st WSD • Suppo n again o ti a d li va • SOAP

• Drag-and-drop data conversion with instant transformation & code gen • Support for mapping XML, DBs, EDI, Excel®2007+, XBRL, flat files & Web services XMLSpy® – XML editor and Web services tool • XML editor with strong database integration • Web services tool, JSON <> XML converter DatabaseSpy® – multi-database query, design, comparison tool • Support for all major relational databases and translation between DB types • SQL editor, graphical database design & content editor

Download a 30 day free trial! Try before you buy with a free, fully functional, trial from www.altova.com

Database Application Developer | PL/SQL

PL/SQL 101 By Steven Feuerstein

Oracle Database

Working with Strings

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

E

I-Hua Chen

very application needs data. That seems rather obvious, doesn’t it? An application is almost always built on top of database tables. Those tables are full of different kinds of data. And the programs you write—whether they are in PL/SQL or another language—manipulate that data. It is, therefore, extremely important for you to be aware of the different datatypes supported by PL/SQL and how you can work with those datatypes. As you might expect, there is an awful lot to learn about datatypes, and not all of that knowledge can fit into a single article. So I will start with one of the most common types of data: strings. Very few database tables and programs do not contain strings—strings such as a company name, address information, descriptive text, and so on. As a result, you quite often need to do the following: • Declare string variables and constants • Manipulate the contents of a string (remove characters, join together multiple strings, and so on) • Move string data between PL/SQL programs and database tables This article gives you the information you need to begin working with strings in your PL/SQL programs.

What Is a String? A string, also referred to as character data, is a sequence of selected symbols from a particular set of characters. In other words, the symbols in a string might consist of English letters, such as “A” or “B.” They might also consist of Chinese characters, such as 字串. There are three kinds of strings in PL/SQL: Fixed-length strings. The string is rightpadded with spaces to the length specified in the declaration. (See the next section, “Declaring String Variables,” to see padding in action.)

Variable-length strings. A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place. Character large objects (CLOBs). CLOBs are variable-length strings that can be up to 128 terabytes. Strings can be literals or variables. A string literal begins and ends with a single quotation mark: 'This is a string literal'

must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a company name, which cannot (in this declaration) have more than 100 characters: DECLARE l_company_name VARCHAR2(100);

You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below:

If you need to embed a single quote inside a string literal, you can type in two single quotes right next to one another, as in:

SQL> DECLARE

'This isn''t a date'

'Oracle Corporation';

2 3

l_company_name

4

You can also use the “q” character to indicate an alternative terminating character for the literal:

VARCHAR2;

BEGIN l_company_name :=

5

END;

6

/

l_company_name

VARCHAR2; *

ERROR at line 2: q'[This isn't a date]'

ORA-06550: line 2, column 21: PLS-00215: String length constraints

A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

must be in range (1 .. 32767)

Declaring String Variables To work with strings in your PL/SQL programs, you declare variables to hold the string values. To declare a string variable, you must select from one of the many string datatypes Oracle Database offers, including CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an ”N“ are “national character set” datatypes, which means they are used to store Unicode character data. (Unicode is a universal encoded character set that can store information in any language using a single character set.) To declare a variable-length string, you

DECLARE

To declare a fixed-length string, use the CHAR datatype:

l_yes_or_no CHAR(1) := 'Y';

With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1. In other words, the two declarations below are identical: DECLARE l_yes_or_no1 CHAR(1) := 'Y'; l_yes_or_no2 CHAR := 'Y';

oracle magazine September/October 2011

65

66

PL/SQL 101

If you declare a CHAR variable with a length greater than 1, Oracle Database automatically pads whatever value you assign to that variable with spaces to the maximum length specified. Finally, to declare a character large object, use the CLOB datatype. You do not specify a maximum length; the length is determined automatically by Oracle Database and is based on the database block size. Here is an example: DECLARE l_lots_of_text CLOB;

So, how do you determine which datatype to use in your programs? Here are some guidelines: • If your string might contain more than 32,767 characters, use the CLOB (or NCLOB) datatype. • If the value assigned to a string always has a fixed length (such as a U.S. Social Security number, which always has the same format and length, NNN-NN-NNNN), use CHAR (or NCHAR). • Otherwise (and, therefore, most of the time), use the VARCHAR2 datatype (or NVACHAR2, when working with Unicode data). Using the CHAR datatype for anything but strings that always have a fixed number of characters can lead to unexpected and undesirable results. Consider the following block, which mixes variable and fixedlength strings:

Take the Challenge! Each of my PL/SQL 101 articles offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and answer the quiz here in Oracle Magazine, and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win your choice of an e-book from O’Reilly Media (oreilly.com). Question 1 What will be displayed after executing this block? BEGIN sys.DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein' , 'e' , -1 , 2)); END;

Question 2 True or false: When assigning a literal value to a string, that value may not contain within it any single quotes. Question 3 What will be displayed after executing this block? BEGIN DBMS_OUTPUT.put_line ( 'REPLACE=' || REPLACE ('steven feuerstein' , 'e' , NULL)); DBMS_OUTPUT.put_line ( 'TRANSLATE=' || TRANSLATE ('steven feuerstein' , 'e' , NULL)); END;

column, or parameter. Once you have declared a variable, you can assign it a value, change its value, and perform operations on the string contained in that variable using string functions and operators. For the rest of this article, I focus on the VARCHAR2 datatype. Using Built-in Functions with Strings Once you assign a string to a variable, you most likely need to analyze the contents of that string, change its value in some way, or combine it with other strings. Oracle Database offers a wide array of built-in functions to help you with all such requirements. Let’s take a look at the most commonly used of these functions. Concatenate multiple strings. One of the most basic and frequently needed operations on strings is to combine or concatenate them together. PL/SQL offers two ways to do this: • The CONCAT built-in function • The || (concatenation) operator The CONCAT function accepts two strings as its arguments and returns those two strings “stuck together.” The concatenation operator also concatenates together two strings, but it is easier to use when combining more than two strings, as you can see in this example: DECLARE l_first l_last

DECLARE l_variable VARCHAR2 (10) := 'Logic'; l_fixed

CHAR (10) := 'Logic';

BEGIN

10 with spaces. Consider the padding demonstrated in the following block; you would expect the block to display “Not Equal”:

DBMS_OUTPUT.put_line ('Equal'); ELSE

:= 'Feuerstein'; /* Use the CONCAT function */ DBMS_OUTPUT.put_line ( CONCAT ('Steven', 'Feuerstein'));

BEGIN IF 'Logic' = 'Logic

/* Use the || operator */

'

DBMS_OUTPUT.put_line (

THEN

DBMS_OUTPUT.put_line ('Not Equal'); END IF;

l_first

DBMS_OUTPUT.put_line ('Equal');

|| ' '

ELSE

END;

|| l_middle

DBMS_OUTPUT.put_line ('Not Equal');

|| ' '

END IF;

At first glance, you would expect that the word “Equal” would be displayed after execution. That is not the case. Instead, “Not Equal” is displayed, because the value of l_fixed has been padded to a length of

VARCHAR2 (20)

BEGIN

IF l_variable = l_fixed THEN

VARCHAR2 (10) := 'Steven';

l_middle VARCHAR2 (5) := 'Eric';

|| l_last);

END; END;

You should, as a result, be very careful about the use of the CHAR datatype, whether as the type of a variable, database

September/October 2011 Oracle.com/oraclemagazine

/

The output from this block is:

67

StevenFeuerstein

Code Listing 1: Examples of case-changing functions

Steven Eric Feuerstein

In my experience, you rarely encounter the CONCAT function. Instead, the || operator is almost universally used by PL/SQL developers. If either of the strings passed to CONCAT or || is NULL or ‘’ (a zero-length string), both the function and the operator simply return the non-NULL string. If both strings are NULL, NULL is returned. Change the case of a string. Three built-in functions change the case of characters in a string: • UPPER changes all characters to uppercase. • LOWER changes all characters to lowercase. • INITCAP changes the first character of each word to uppercase (characters are delimited by a white space or nonalphanumeric character). Listing 1 shows some examples that use these case-changing functions. Extract part of a string. One of the most commonly utilized built-in functions for strings is SUBSTR, which is used to extract a substring from a string. When calling SUBSTR, you provide the string, the position at which the desired substring starts, and the number of characters in the substring. Listing 2 shows some examples that use the SUBSTR function. As you can see, with the SUBSTR function you can specify a negative starting position for the substring, in which case Oracle Database counts backward from the end of the string. If you do not provide a third argument—the number of characters in the substring—Oracle Database automatically returns the remainder of the string from the specified position. Find a string within another string. Use the INSTR function to determine where (and if) a string appears within another string. INSTR accepts as many as four arguments: • The string to be searched (required). • The substring of interest (required). • The starting position of the search (optional). If the value is negative, count from the end of the string. If no value is provided, Oracle Database starts at the beginning of the string; that is, the starting position is 1.

SQL> 2 3 4 5 6 7 8

DECLARE l_company_name VARCHAR2 (25) := 'oraCLE corporatION'; BEGIN DBMS_OUTPUT.put_line (UPPER (l_company_name)); DBMS_OUTPUT.put_line (LOWER (l_company_name)); DBMS_OUTPUT.put_line (INITCAP (l_company_name)); END; /

ORACLE CORPORATION oracle corporation Oracle Corporation

Code Listing 2: Examples of SUBSTR function DECLARE l_company_name VARCHAR2 (6) := 'Oracle'; BEGIN /* Retrieve the first character in the string */ DBMS_OUTPUT.put_line ( SUBSTR (l_company_name, 1, 1)); /* Retrieve the last character in the string */ DBMS_OUTPUT.put_line ( SUBSTR (l_company_name, -1, 1)); /* Retrieve three characters, starting from the second position. */ DBMS_OUTPUT.put_line ( SUBSTR (l_company_name, 2, 3)); /* Retrieve the remainder of the string, starting from the second position. */ DBMS_OUTPUT.put_line ( SUBSTR (l_company_name, 2)); END; / The output from this block is: O e rac racle

• The Nth occurrence of the substring (optional). If no value is provided, Oracle Database looks for the first occurrence. Listing 3 shows some examples that use the INSTR function. INSTR is a very flexible and handy utility. It can easily be used to determine whether or not a substring appears at all in a string. Here is a Boolean function that does just that: CREATE OR REPLACE FUNCTION is_in_string ( string_in ,substring_in RETURN BOOLEAN IS BEGIN

IN VARCHAR2 IN VARCHAR2)

RETURN INSTR (string_in , substring_in) > 0; END is_in_string; /

Pad a string with spaces (or other characters). I warned earlier about using the CHAR datatype, because Oracle Database pads your string value with spaces to the maximum length specified in the declaration. However, there are times, primarily when generating reports, when you want to put spaces (or other characters) in front of or after the end of your string. For these situations, Oracle Database offers LPAD and RPAD.

oracle magazine September/October 2011

68

PL/SQL 101

When you call these functions, you specify the length to which you want your string padded and with what character or characters. If you do not specify any pad characters, Oracle Database defaults to padding with spaces. Listing 4 shows some examples that use these LPAD and RPAD padding functions. Replace characters in a string. Oracle Database provides a number of functions that allow you to selectively change one or more characters in a string. You might need, for example, to replace all spaces in a string with the HTML equivalent (“ ”) so the text is displayed properly in a browser. Two functions take care of such needs for you: • REPLACE replaces a set or pattern of characters with another set. • TRANSLATE translates or replaces individual characters. Listing 5 shows some examples of these two character-replacement built-in functions. Notice that when you are replacing a single character, the effect of REPLACE and INSTR is the same. When replacing

multiple characters, REPLACE and INSTR act differently. The call to REPLACE asked that appearances of “abc” be replaced with “123.” If, however, any of the individual characters (a, b, or c) appeared in the string outside of this pattern (“abc”), they would not be replaced. The call to TRANSLATE, however, specified that any occurrence of each of the individual characters be replaced with the character in the third argument in the same position. Generally, you should use REPLACE whenever you need to replace a pattern of characters, while TRANSLATE is best applied to situations in which you need to replace or substitute individual characters in the string. Remove characters from a string. What LPAD and RPAD giveth, TRIM, LTRIM, and RTRIM taketh away. Use these trim functions to remove characters from either the beginning (left) or end (right) of the string. Listing 6 shows an example of both RTRIM and LTRIM. RTRIM removed all the periods, because the second argument specifies the character

(or characters) to trim, in this case, a period. The call to LTRIM demonstrates that you can specify multiple characters to trim. In this case, I asked that all letters and spaces be trimmed from the beginning of string b, and I got what I asked for. The default behavior of both RTRIM and LTRIM is to trim spaces from the beginning or end of the string. Specifying RTRIM(a) is the same as asking for RTRIM(a,’ ‘). The same goes for LTRIM(a) and LTRIM(a,’ ‘). The other trimming function is just plain TRIM. TRIM works a bit differently from LTRIM and RTRIM, as you can see in this block: DECLARE x

VARCHAR2 (30) := '.....Hi there!.....';

BEGIN DBMS_OUTPUT.put_line ( TRIM (LEADING '.' FROM x)); DBMS_OUTPUT.put_line ( TRIM (TRAILING '.' FROM x)); DBMS_OUTPUT.put_line ( TRIM (BOTH '.' FROM x));

Code Listing 3: Examples of INSTR function --The default is to trim BEGIN /* Find the location of the first "e" */ DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein', 'e')); /* Find the location of the first "e" starting from position 6 */ DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein' , 'e' , 6)); /* Find the location of the first "e" starting from the 6th position from the end of string and counting to the left. */ DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein' , 'e' , -6)); /* Find the location of the 3rd "e" starting from the 6th position from the end of string. */ DBMS_OUTPUT.put_line ( INSTR ('steven feuerstein' , 'e' , -6 , 3)); END; /

--from both sides DBMS_OUTPUT.put_line ( TRIM ('.' FROM x)); --The default trim character --is the space: DBMS_OUTPUT.put_line (TRIM (x)); END;

The output from this block is: Hi there!..... .....Hi there! Hi there! Hi there! .....Hi there!.....

With TRIM, you can trim from either side or from both sides. However, you can specify only a single character to remove. You cannot, for example, write the following:

The output from this block is: 3 9 11 5

September/October 2011 Oracle.com/oraclemagazine

TRIM(BOTH ',.;' FROM x)

If you need to remove more than one character from the front and back of a string, you

69

Code Listing 4: Examples of padding functions DECLARE l_first VARCHAR2 (10) := 'Steven'; l_last VARCHAR2 (20) := 'Feuerstein'; l_phone VARCHAR2 (20) := '773-426-9093'; BEGIN /* Indent the subheader by 3 characters */ DBMS_OUTPUT.put_line ('Header'); DBMS_OUTPUT.put_line ( LPAD ('Sub-header', 13, '.')); /* Add "123" to the end of the string, until the 20 character is reached.*/ DBMS_OUTPUT.put_line ( RPAD ('abc', 20, '123')); /* Display headers and then values to fit within the columns. */ DBMS_OUTPUT.put_line ( /*1234567890x12345678901234567890x*/ 'First Name Last Name Phone'); DBMS_OUTPUT.put_line ( RPAD (l_first, 10) || ' ' || RPAD (l_last, 20) || ' ' || l_phone); END; / The output from this block is: Header ...Sub-header abc12312312312312312 First Name Last Name Steven Feuerstein

Phone 773-426-9093

Code Listing 5: Examples of character replacement functions DECLARE l_name VARCHAR2 (50) := 'Steven Feuerstein'; BEGIN /* Replace all e's with the number 2. Since you are replacing a single character, you can use either REPLACE or TRANSLATE. */ DBMS_OUTPUT.put_line ( REPLACE (l_name, 'e', '2')); DBMS_OUTPUT.put_line ( TRANSLATE (l_name, 'e', '2')); /* Replace all instances of "abc" with "123" */ DBMS_OUTPUT.put_line ( REPLACE ('abc-a-b-c-abc' , 'abc' , '123')); /* Replace "a" with "1", "b" with "2", "c" with "3". */ DBMS_OUTPUT.put_line ( TRANSLATE ('abc-a-b-c-abc' , 'abc' , '123')); END; /

need to use RTRIM and LTRIM: RTRIM(LTRIM(x,',.;'),',.;')

You can also use TRANSLATE to remove characters from a string by replacing them with (or “translating” them into) NULL. You must, however, take care with how you specify this replacement. Suppose I want to remove all digits (0 through 9) from a string. My first attempt yields the following block: BEGIN /* Remove all digits (0-9) from the string. */ DBMS_OUTPUT.put_line ( TRANSLATE ('S1t2e3v4e56n' , '1234567890' , '')); END; /

When I execute this block, however, nothing (well, a NULL string) is displayed. This happens because if any of the arguments passed to TRANSLATE are NULL (or a zero-length string), the function returns a NULL value. So all three arguments must be nonNULL, which means that you need to put at the start of the second and third arguments a character that will simply be replaced with itself, as in the following: BEGIN /* Remove all digits (0-9) from the string. */ DBMS_OUTPUT.put_line ( TRANSLATE ('S1t2e3v4e56n' , 'A1234567890' , 'A')); END; /

Now, “A” is replaced with “A” and the remaining characters in the string are replaced with NULL, so the string “Steven” is then displayed.

The output from this block is: St2v2n F2u2rst2in St2v2n F2u2rst2in 123-a-b-c-123 123-1-2-3-123

Good to Know Beyond awareness of the basic properties of strings in PL/SQL and built-in functions, you can benefit by keeping the following

oracle magazine September/October 2011

70

PL/SQL 101

points about long strings and maximum string sizes in mind. When the string is too long. You must specify a maximum length when you declare a variable based on the VARCHAR2 type. What happens, then, when you try to assign a value to that variable whose length is greater than the maximum? Oracle Database raises the ORA-06502 error, which is also defined in PL/SQL as the VALUE_ERROR exception. Here is an example of the exception being raised and propagated out of the block unhandled:

Code Listing 6: Examples of LTRIM and RTRIM functions DECLARE a VARCHAR2 (40) := 'This sentence has too many periods....'; b VARCHAR2 (40) := 'The number 1'; BEGIN DBMS_OUTPUT.put_line ( RTRIM (a, '.')); DBMS_OUTPUT.put_line ( LTRIM ( b , 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ' || 'abcdefghijklmnopqrstuvwxyz')); END; The output from this block is: This sentence has too many periods 1

SQL> DECLARE 2 3

l_name VARCHAR2(3); 5

BEGIN

4

l_name := 'Steven';

5

END;

6

/

/

Table created. SQL> BEGIN

DECLARE

2

*

3

ERROR at line 1:

4

END;

5

/

ORA-06502: PL/SQL: numeric or value

INSERT INTO small_varchar2 VALUES ('abc');

error: character string buffer too small

BEGIN

ORA-06512: at line 4

*

128 terabytes, while in SQL the maximum is just (4 GB – 1) * DB_BLOCK_SIZE. There’s More to Data than Strings Character data plays a very large role in PL/SQL applications, but those same applications undoubtedly also rely on data of other types, especially numbers and dates. I will cover these datatypes in the next PL/SQL 101 article. 

ERROR at line 1:

Here is a rewrite of the same block that traps the VALUE_ERROR exception:

ORA-12899: value too large for column "HR"."SMALL_VARCHAR2"."STRING_VALUE" (actual: 3, maximum: 2) ORA-06512: at line 2

SQL> DECLARE 2 3

l_name BEGIN

4 5

VARCHAR2 (3);

l_name := 'Steven'; EXCEPTION

6

WHEN VALUE_ERROR

7

THEN

8

DBMS_OUTPUT.put_line (

9

'Value too large!');

10

END;

11

/

Value too large!

Interestingly, if you try to insert or update a value in a VARCHAR2 column of a database table, Oracle Database raises a different error, which you can see below: SQL> CREATE TABLE small_varchar2 2

(

3 4

string_value )

VARCHAR2 (2)

Different maximum sizes. There are a number of differences between SQL and PL/SQL for the maximum sizes for string datatypes. In PL/SQL, the maximum size for VARCHAR2 is 32,767 bytes, while in SQL the maximum is 4,000 bytes. In PL/SQL, the maximum size for CHAR is 32,767 bytes, while in SQL the maximum is 2,000 bytes. Therefore, if you need to save a value from a VARCHAR2 variable in the column of a table, you might encounter the ORA-12899 error. If this happens, you have two choices: • Use SUBSTR to extract no more than 4,000 bytes from the larger string, and save that substring to the table. This option clearly has a drawback: you lose some of your data. • Change the datatype of the column from VARCHAR2 to CLOB. This way, you can save all your data. In PL/SQL, the maximum size for CLOB is

September/October 2011 Oracle.com/oraclemagazine

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 LEARN more about PL/SQL datatypes bit.ly/nrpGAw differences between CHAR and VARCHAR2 bit.ly/pigCcv built-in functions bit.ly/nIdZ8o DOWNLOAD Oracle Database 11g oracle.com/software/products/database TEST your PL/SQL knowledge plsqlchallenge.com READ PL/SQL 101, Parts 1 and 2 bit.ly/fc0uoJ

DBA

Support By Tamzin Oscroft

Oracle Database

Troubleshooting Internal Errors

A guide to assessing and resolving ORA-600 and ORA-7445 errors

I

f you’re an Oracle DBA, you’re likely to have come across an error message in your Oracle Database alert.log files prefixed by either ORA-600 or ORA-7445, such as Thu Jan 20 13:35:52 2011 Errors in file /DATA/oracle/admin/ prod/udump/prod_ora_2131.trc: ORA-00600: internal error code, arguments: [ktfbtgex-7], [1015817], [1024], [1015816], [], [], [], []

Because these internal error messages include no attached explanation in the way that external error messages do (for example, “ORA-00942: table or view does not exist”), it is difficult to assess the seriousness of the error and whether it is cause for concern. This column explains what you can do to assess some ORA-600 or ORA-7445 errors and identify solutions. ORA-600 or ORA-7445: What Is the Difference? ORA-600 is a catchall message that indicates an error internal to the database code. The key point to note about an ORA-600 error is that it is signaled when a code check fails within the database. At points throughout the code, Oracle Database performs checks to confirm that the information being used in internal processing is healthy, that the variables being used are within a valid range, that changes are being made to a consistent structure, and that a change won’t put a structure into an unstable state. If a check fails, Oracle Database signals an ORA-600 error and, if necessary, terminates the operation to protect the health of the database. The first argument to the ORA-600 error message indicates the location in the code where the check is performed; in the

example above, that is ktfbtgex-7 (which indicates that the error occurred at a particular point during tablespace handling). The subsequent arguments have different meanings, depending on the particular check. An ORA-7445 error, on the other hand, traps a notification the operating system has sent to a process and returns that notification to the user. Unlike the ORA-600 error, the ORA-7445 error is an unexpected failure rather than a handled failure. The Oracle function in which that notification signal is received is usually, from Oracle Database 10g onward, contained in the ORA-7445 error message itself. For example, in the error message ORA-07445: exception encountered: core dump [kocgor()+96] [SIGSEGV] [ADDR:0xF000000104] [PC:0x861B7EC] [Address not mapped to object] []

the failing function is kocgor, which is associated with the handling of user-defined

objects. The trapped signal is SIGSEGV (signal 11, segmentation violation), which is an attempt to write to an illegal area of memory. Another common signal is SIGBUS (signal 10, bus error), and there are other signals that occur less frequently, with causes that range from invalid pointers to insufficient OS resources. Both ORA-600 and ORA-7445 errors will • Write the error message to the alert.log, along with details about the location of a trace containing further information • Write detailed information to a trace file in the location indicated in the alert.log • In Oracle Database 11g Release 1 onward, create an incident and place the relevant files in the incident directory in the location defined by the diagnostic_dest initialization file parameter • Write the error message to the user interface if the server process is not terminated or signal ORA-3113 if it is Often you will see multiple errors reported within the space of a few minutes,

Code Listing 1: Query plan accessing three tables and two indexes ———————————————————————————————————————————————————————————————————————————————————————————————————————————————————— |Id |Operation |Name |Rows |Bytes |Cost |Time | ———————————————————————————————————————————————————————————————————————————————————————————————————————————————————— |0 |SELECT STATEMENT | | | |883K | | |1 | WINDOW NOSORT | |2506K | 318M |883K |04:31:56| |2 | SORT GROUP BY | |2506K | 318M |883K |04:31:56| |3 | HASH JOIN RIGHT OUTER | |2506K | 318M |837K |03:20:05| |4 | VIEW |index$_join$_006| 8777 | 257K | 35 |00:00:01| |5 | HASH JOIN | | | | | | |6 | INDEX FAST FULL SCAN |XC179S1 | 8777 | 257K | 18 |00:00:01| |7 | INDEX FAST FULL SCAN |XC179P0 | 8777 | 257K | 25 |00:00:01| |8 | VIEW | |2506K | 245M |837K |03:20:04| |9 | HASH JOIN OUTER | |2506K | 296M |837K |03:20:04| |10 | HASH JOIN | |2506K | 184M |454K |02:49:32| |11 | TABLE ACCESS FULL |TESTTAB1 |2484K | 102M |361K |01:26:13| |12 | TABLE ACCESS FULL |TESTTAB2 | 21M | 688M | 49K |00:12:37| |13 | TABLE ACCESS FULL |TESTTAB3 | 94M |4326M |146K |00:35:51| ————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

oracle magazine September/October 2011

71

72

Support

typically starting with an ORA-600. It is usually, but not always, the case that the first is the significant error and the others are side effects. Can I RESolve These Errors Myself? For some ORA-600 and ORA-7445 errors, you can either identify the cause and resolve the error on your own or find ways to avoid the error. The information provided in this section will help you resolve or work around some of the more common errors. ORA-600 [729]. The first argument to this ORA-600 error message, 729, indicates a memory-handling issue. The error message text will always include the words space leak, but the number after 729 will vary: ORA-00600: internal error code, arguments: [729], [800], [space leak], [], [],

A space leak occurs when some code does not completely release the memory it used while executing. In this example, when that process disconnected from the database, it discovered that some memory was not cleaned up at some point during its life and reported ORA-600 [729]. The number in the second set of brackets (800) is the number of bytes of memory discovered. You cannot determine the cause of the space leak by checking your application code, because the error is internal to Oracle Database. You can, however, safely avoid the error by setting an event in the initialization file for your database in this form:

Code Listing 2: Find the index SELECT owner, segment_name, segment_type, partition_name FROM DBA_SEGMENTS WHERE header_file = (SELECT file# FROM v$datafile WHERE rfile# = dbms_utility.data_block_address_file(to_ number('&rdba','XXXXXXXX')) AND ts#= &tsn) AND header_block = dbms_utility.data_block_address_block(to_ number('&rdba','XXXXXXXX'));

example above, you could set the number to 1000, in which case the event instructs the database to ignore all user space leaks that are smaller than 1000 bytes. ORA-600 [kddummy_blkchk]. The kddummy_blkchk argument indicates that checks on the physical structure of a block have failed. This error is reported with three additional arguments: the file number, the block number, and an internal code indicating the type of issue with that block. The following is an alert.log excerpt for an ORA-600 [kddummy_blkchk] error: Errors in file/u01/oracle/admin/ PROD/bdump/prod_ora_11345.trc: ORA-600: internal error code, arguments: [kddummy_blkchk], [2], [21940], [6110], [], [], [], []

The ORA-600 [kddummy_blkchk] error message usually reports a corruption, so to identify the object involved, first use the file number (&afn) and the block number (&bn) reported in the error message in the SQL query:

SQL>analyze index validate structure;

If there is definitely a corruption in the object, the error returned will be ORA 1498 "block check failure see trace file"

The best way to resolve the corruption is to restore a copy of the affected datafile from before the error and to recover the database so these changes are applied to the restored datafile and brought forward to the current time. This action is possible only if the archivelog feature for your database is enabled. (Enabling this feature ensures that all changes to the database are saved in archived redo logs.) ORA-600 [6033]. This error is reported with no additional arguments, as shown in the following alert.log file excerpt: Errors in file/u01/oracle/admin/PROD/ bdump/prod_ora_2367574.trc: ORA-600: internal error code, arguments: [6033], [], [], [], [], [], [], []

select * from dba_extents event="10262 trace name

where file_id=&afn

context forever, level xxxx"

and &bn between block_id and block_id + blocks –1;

or by executing the following: SQL>alter system set events '10262 trace name context forever, level xxxx' scope=spfile;

You will also need to shut the database down and restart it to enable the event to take effect. Replace xxxx with a number greater than the value in the second set of brackets in the ORA-600 [729] error message. In the

&afn is the first additional argument (2 in this example); &bn is the second additional argument (21940 in this example). If the query returns a table, confirm the corruption by executing SQL>analyze table validate structure;

If the query returns an index, confirm the corruption by executing

September/October 2011 Oracle.com/oraclemagazine

The ORA-600 [6033] error often indicates an index corruption. To identify the affected index, you’ll need to look at the trace file whose name is provided in the alert.log file, just above the error message. In this alert.log excerpt, the trace file you need to look at is called prod_ora_2367574.trc and is located in /u01/oracle/admin/PROD/bdump. There are two possible ways to identify the table on which the affected index is built: 1. Look for the SQL statement that was executing at the time of the error. This statement should appear at the top of the trace file, under the heading “Current SQL Statement.” The affected index will

73

belong to one of the tables accessed by that statement. 2. Search within the trace file for the words Plan Table. This search will return the tables and indexes the Oracle Database optimizer is using to access the data that will satisfy the query being executed. For example, the query using the plan in Listing 1 is accessing the testtab1, testtab2, and testtab3 tables and the XC179S1 and XC179PO indexes. For each of the tables used by the SQL statement that was executing at the time of the error, execute the following statement:

Figure 1: ORA-600/ORA-7445 lookup tool interface /u01/app/oracle/admin/prod/bdump/

SQL>analyze table validate

prod_smon_8201.trc:

structure cascade;

ORA-7445: exception encountered: core dump [ksxmcln()+0] [SIGBUS]

This will check to ensure that every value in the index is also in the table, and vice versa. If it finds a mismatch, it will report ORA-1499 table/Index Cross Reference Failure - see trace file

The trace file will be in the location indicated by the user_dump_dest or diagnostic_ dest initialization parameter and will contain information similar to row not found in index tsn: 8 rdba: 0x04d01348

You can then find the index with by using the query in Listing 2. Replace the &rdba and &tsn values in Listing 2 with the appropriate values. For this example, the &rdba value is the rdba from the trace file with the 0x portion removed and &tsn is the tablespace number (tsn) from the trace file. (&rdba in this case would be 04d01348,and &tsn would be 8.) Once you have identified the index, drop and re-create it. It is important to drop and re-create the index rather than rebuilding it online, because only re-creating it will reinitialize the values in the index. ORA-7445 [xxxxxx] [SIGBUS] [OBJECT SPECIFIC HARDWARE ERROR]. This ORA7445 error can occur with many different functions (in place of xxxxxx). For example, the following alert.log excerpt shows the failing function as ksxmcln.

Once you’ve identified the resource that affects the running of the statement, increase the amount of that resource available to Oracle Database.

[object specific hardware error] [6822760] [] []

The important part of this error is the ”object specific hardware error” argument, which indicates that there were insufficient operating system resources to complete the action. The most common resources involved are swap and memory. To diagnose the cause of an ORA-7445 error, you should first check the operating system error log; for example, in Linux this error log is /var/log/messages. Within the error log, look for information with the same time stamp as the ORA-7445 error (this will be in the alert.log next to the error message). You will often find an error message similar to Jun 9 19:005:05 PRODmach1 genunix: [ID 470503 kern.warning] WARNING: Sorry, no swap space to grow

Summary By following the instructions in this article, you should be able to resolve some errors that are caused by underlying physical issues such as file corruption or insufficient swap space. But because ORA-600 and ORA-7445 errors are internal, many cannot be resolved by user-led troubleshooting. For those Oracle Database users with Oracle support contracts, however, additional knowledge content is available via My Oracle Support. Most notably, the ORA-600/ORA-7445 lookup tool [Knowledge Article 153788.1], shown in Figure 1, enables you to enter the first argument to an ORA-600 or ORA-7445 error message and use that information to identify known defects, workarounds, and other knowledge targeted specifically to that error/argument combination. 

stack for pid 9632

If no errors are reported in the operating system error log with the same time stamp as the ORA-7445 error, check the ORA-7445 trace file. If there is a statement in the trace file under the heading “Current SQL Statement,” execute that statement again to try to reproduce the error. If the error is reproduced, run the statement again while monitoring OS resources with standard UNIX monitoring tools such as sar or vmstat (contact your system administrator if you are not sure which to use).

Tamzin Oscroft is a senior principal support engineer in Oracle Database Support. She has worked for Oracle Global Software Support for 16 years.

Next Steps LEARN more about My Oracle Support support.oracle.com blogs.oracle.com/supportportal READ more about error messages Oracle Database Error Messages 11g Release 2 bit.ly/mqNFVQ

oracle magazine September/October 2011

74

Database Application Developer and DBA oracle Database

On Caching and Evangelizing SQL

Our technologist caches scalar subqueries, votes for SQL, and recommends technology and community.

O

ne of the talks I gave recently at the Oracle Benelux User Group (OBUG) conference in Belgium was regarding techniques—some SQL tricks, if you will—you can use when writing SQL. One of the techniques I discussed at length was scalar subqueries and how they can be used to minimize the number of times a PL/SQL function is called from SQL. This is important because the overhead of going from SQL to PL/SQL over and over again can be quite expensive, and the scalar subquery caching database feature can dramatically decrease this overhead. What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used. For example, you can obviously create the query

select deptno, dname, 'Hello world'

select dept.deptno, dept.dname,

select project_name, deptno,

count(emp.empno)

(select count(*)

from dept left outer join emp

from emp

on (dept.deptno = emp.deptno );

where emp.deptno = projects.deptno)

Regarding these last two queries, if your goal is to optimize your query for initial response time, you may want to use the former query, with the scalar subquery, because Oracle Database would have to get just the first row from DEPT, run the scalar subquery (select count…) against EMP to get the count, and then return it. The process for returning that first row would be very fast. The latter query, which includes an outer join, would likely require two full table scans, a hash outer join, and an aggregation—and require all these processes to be completed before the first row could be returned. So, now that you know what a scalar subquery is, you need to understand the scalar subquery caching feature of Oracle Database. In the above query with a scalar subquery, the database would actually extract the scalar subquery and rewrite it to use bind variables. In effect, the database would be executing

from projects;

will have to execute the scalar subquery at least as many times as there are unique DEPTNO values in the PROJECTS table. Note that I said, “at least as many times”— the database does not have to execute the scalar subquery for every row in the PROJECTS table if it caches some of the results and reuses them, which is what happens with scalar subquery caching. When you’re using a scalar subquery, Oracle Database will set up a small inmemory hash table for the subquery and its results each time it runs the query. So, when you run the previous query, Oracle Database sets up in memory a hash table that looks like this: Select count(*) from emp where emp.deptno = :deptno :deptno

Count(*)

….

….

from dept; (select count(*) from emp

You can also (since Oracle8i Database Release 8.1.5, anyway) include a scalar subquery: select deptno, dname, (select count(*) from emp where emp.deptno = dept.deptno) from dept;

Note that the complete last query is semantically equivalent to

where emp.deptno = ?)

for each row in the DEPT table. Given that DEPTNO is unique in DEPT, the database would have to physically execute that scalar subquery for each row in DEPT. But what if you are not querying the DEPT table but, rather, another table that includes a DEPTNO column? Perhaps a PROJECTS table with records that have PROJECT_ NAME and DEPTNO columns? Then a query such as

September/October 2011 OrAcle.cOm/oraclemagazine

Oracle Database will use this hash table to remember the scalar subquery and the inputs to it—just :DEPTNO in this case— and the output from it. At the beginning of every query execution, this cache is empty, but suppose you run the query and the first PROJECTS row you retrieve has a DEPTNO value of 10. Oracle Database will assign the number 10 to a hash value between 1 and 255 (the size of the hash table cache in Oracle Database 10g and Oracle Database 11g currently) and will look in that hash table

Ask Tom by Tom Kyte

slot to see if the answer exists. In this case, it will not, so Oracle Database must run the scalar subquery with the input of 10 to get the answer. If that answer (count) is 42, the hash table may look something like this: Select count(*) from emp where emp.deptno = :deptno :deptno

Count(*)

….

…

10

42

….

….

Code Listing 1: PL/SQL function f SQL> create or replace function f( x in varchar2 ) return number 2 as 3 begin 4 dbms_application_info.set_client_info (userenv('client_info')+1 ); 5 return length(x); 6 end; 7 / Function created.

Code Listing 2: Demonstrating once-per-row repetition in function f You’ll have saved the DEPTNO value of 10 and the answer (count) of 42 in some slot—probably not the first or last slot, but whatever slot the hash value 10 is assigned to. Now suppose the second row you get back from the PROJECTS table includes a DEPTNO value of 20. Oracle Database will again look in the hash table after assigning the value 20, and it will discover “no result in the cache yet.” So it will run the scalar subquery, get the result, and put it into the hash table cache. Now the cache may look like this: Select count(*) from emp where emp.deptno = :deptno :deptno

Count(*)

…

…

10

42

….

….

20

55

…

…

Now suppose the query returns a third row and it again includes a DEPTNO value of 10. This time, Oracle Database will see DEPTNO = 10, find that it already has that value in the hash table cache, and will simply return 42 from the cache instead of executing the scalar subquery. In fact, it will never have to run that scalar subquery for the DEPTNO values of 10 or 20 again for that query—it will already have the answer. What happens if the number of unique DEPTNO values exceeds the size of the hash table? What if there are more than 255 values? Or, more generally, if more than one DEPTNO value is assigned to the same slot in the hash table, what happens in a hash collision? The answer is the same for all these questions and is rather simple: Oracle

SQL> begin 2 :cpu := dbms_utility.get_cpu_time; 3 dbms_application_info.set_client_info(0); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select owner, f(owner) from stage; … 72841 rows selected. SQL> 2 3 4

select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

CPU_HSECS —————————————— — 118

USERENV('CLIENT_INFO') ———————————————————————————————— 72841

Database will not be able to cache the second or nth value to that slot in the hash table. For example, what if the third row returned by the query contains the DEPTNO = 30 value? Further, suppose that DEPTNO = 30 is to be assigned to exactly the same hash table slot as DEPTNO = 10. The database won’t be able to effectively cache DEPTNO = 30 in this case—the value will never make it into the hash table. It will, however, be “partially cached.” Oracle Database still has the hash table with all the previous executions, but it also keeps the last scalar subquery result it had “next to” the hash table. That is, if the fourth row also includes a DEPTNO = 30 value, Oracle Database will discover that the result is not in the hash table but is “next to” the hash table, because the last time it ran the scalar subquery, it was run with an input of 30. On the other hand, if the fourth row includes a DEPTNO = 40 value, Oracle Database will run the scalar subquery with the DEPTNO = 40 value (because it hasn’t seen that

value yet during this query execution) and overwrite the DEPTNO = 30 result. The next time Oracle Database sees DEPTNO = 30 in the result set, it’ll have to run that scalar subquery again. So, all this discussion so far was a setup—a prelude, if you will—for what I really wanted to write about: how to reduce the number of times a PL/SQL function invoked from SQL is called. Suppose you have a PL/SQL function like the one in Listing 1. This function will simply increment a counter each time it is called. The counter value will be stored in the CLIENT_INFO column of V$SESSION, and the function will then return the length of its input. If you execute a simple query like the one in Listing 2 (note that the STAGE table is simply a copy of ALL_OBJECTS), you can see that the function was called once per row even though the inputs to the function were repeated over and over again. If you use a scalar subquery—if you replace “f(owner)”

oracle magazine September/October 2011

75

76

Ask Tom

with “(select f(owner) from dual)”—you will notice a massive reduction in calls to the function, as shown in Listing 3. As you can see in Listing 3, the function went from 72,841 calls down to 66! And the CPU time dropped dramatically as well. That reduction in CPU time resulted from not calling the function (and therefore not making the context switch from SQL to PL/SQL and not invoking DBMS_ APPLICATION_INFO and LENGTH so often). Now, some of you might be thinking about the possibility of marking the function deterministic, because it is, in fact, deterministic. Wouldn’t that reduce the number of function calls as well? The short answer is “yes,” but the longer answer is “yes, but not as well as scalar subquery caching can.” As you can see in the deterministic function in Listing 4, the number of calls is reduced, but only down to 8,316. The scalar subquery cache in this case was better than just marking the function as deterministic. (Note: Marking a function deterministic affects caching only in Oracle Database 10g and above; you will see no change in the number of calls in Oracle9i Database with deterministic functions). Going one step further, you might ask, “But what if you used the function result cache in Oracle Database 11g? Could you get the function calls down to zero?” The answer is, “Yes, sort of.” The function calls would go down to zero, but the context switching from SQL to PL/SQL would remain at a very high value: 72,841 times in this case. Listing 5 shows a function result cache solution. The number of function calls is 32 (because I happen to have 32 schemas in my database), and the CPU time is 0.73 seconds—about the same as that of the DETERMINISTIC function and far above the CPU time of 0.29 seconds used by the 66 calls made by the scalar subquery. Furthermore, if you were to run this query again—as shown in Listing 6—you’d discover that it calls the function zero times, but the CPU time (0.63 seconds) is still longer than the CPU time for the scalar subquery cache example in Listing 3. This just shows that even if your function is deterministic, even if it is “result-

Code Listing 3: Reducing function calls with scalar subquery SQL> begin 2 :cpu := dbms_utility.get_cpu_time; 3 dbms_application_info.set_client_info(0); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select owner, (select f(owner) from dual) f from stage; … 72841 rows selected. SQL> 2 3 4

select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

CPU_HSECS —————————————— — 29

USERENV('CLIENT_INFO') ———————————————————————————————— 66

Code Listing 4: Deterministic function variation SQL> create or replace function f( x in varchar2 ) return number 2 DETERMINISTIC 3 as 4 begin 5 dbms_application_info.set_client_info (userenv('client_info')+1 ); 6 return length(x); 7 end; 8 / Function created. SQL> begin 2 :cpu := dbms_utility.get_cpu_time; 3 dbms_application_info.set_client_info(0); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select owner, f(owner) from stage; … 72841 rows selected. SQL> 2 3 4

select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

CPU_HSECS —————————————— — 69

USERENV('CLIENT_INFO') ———————————————————————————————— 8316

cached,” there is a good reason to always wrap your function call in a SELECT FROM DUAL. I’ve gotten into the habit over the years of never coding

but rather including a simple scalar subquery select * from t

select * from t where column = plsql_function(..);

September/October 2011 Ooraclemagazine

where column = (select plsql_function(…) from dual);

77

Code Listing 5: Function result cache solution SQL> create or replace function f( x in varchar2 ) return number 2 RESULT_CACHE 3 as 4 begin 5 dbms_application_info.set_client_info (userenv('client_info')+1 ); 6 return length(x); 7 end; 8 / Function created. SQL> begin 2 :cpu := dbms_utility.get_cpu_time; 3 dbms_application_info.set_client_info(0); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select owner, f(owner) from stage; … 72841 rows selected. SQL> 2 3 4

select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

CPU_HSECS —————————————— 73

USERENV('CLIENT_INFO') ———————————————————————————————— 32

settings. Could you explain CACHE and NOCACHE with an example? Sure. In short, if you use NOCACHE, every time you call sequence.nextval, you will cause a table named SYS.SEQ$ to be updated and committed. Every single time. If, on the other hand, you use the default value, CACHE 20, you will cause SYS.SEQ$ to be updated and committed every 20th time you call sequence.nextval. It is a performance thing. If you don’t cache the next couple of sequence values in the system global area (SGA), you will be doing an UPDATE and COMMIT every single time you call nextval. You can evaluate the performance impact this might have on your application—especially with large data loads—if the cache value is set too low. For example, let’s create three sequences SQL> create sequence nocache_seq NOCACHE; Sequence created. SQL> create sequence default_cache_seq; Sequence created.

to get the performance benefit of the scalar subquery cache. Is the Cursor Dying? With subquery factoring, MODEL, PIVOT, MERGE with DELETE, “Materialize” and other hints, global temporary tables, DML (data manipulation language) error logging, and so many other powerful new SQL features, when is it necessary to use cursors (apart from using ref cursors for reports). Is the cursor dying? Well, the simple UPDATE statement update t set x = 5;

requires a cursor. In fact, every SELECT you execute, every DELETE you perform—pretty much everything—requires a cursor. So it is doubtful that cursors are “dying.” However, I think you may mean, “Is the need to procedurally process the output of a cursor dying?” Many times (many, many, many times), a ton of procedural code can be replaced with a single SQL statement. That has been true for a long time and is

truer today than ever with features such as DML error logging. So, yes, we should all be striving to erase as much procedural code as possible and go set-based. Does it mean the death of cursors? No, not at all. It means the reduction of procedural code—more code = more bugs; less code = fewer bugs. But it also means that people have to read the documentation, understand the benefits, learn the full SQL language, and start using it. I was once asked in a question-andanswer session what I thought was the most underutilized Oracle Database feature. I responded almost immediately with the answer “SQL.” The questioner then asked me, “But what do you mean— everyone uses SQL all the time.” My response was that everyone uses very, very simple SQL and avoids 99 percent of its actual capabilities. Sequence Caching I read about sequence caching, but I still do not understand the CACHE and NOCACHE

SQL> create sequence big_cache_seq cache 100000; Sequence created.

and a table to test with SQL> create table t ( x int ); Table created.

Now let’s execute the following block of code with SQL trace enabled for each of the three sequences—replace &1 with the actual sequence name (nocache_seq, default_cache_seq, or big_cache_seq) as you run them: SQL> truncate table t; Table truncated. SQL> insert into t (x) 2

with data(x)

3

as

4

(select 1 x

5 6

from dual union all

oracle magazine September/October 2011

78

Ask Tom

Code Listing 6: Rerunning the function result cache solution

was around 2.1 seconds, according to the TKPROF report.

SQL> begin 2 :cpu := dbms_utility.get_cpu_time; 3 dbms_application_info.set_client_info(0); 4 end; 5 / PL/SQL procedure successfully completed. SQL> select owner, f(owner) from stage; … 72841 rows selected. SQL> 2 3 4

select dbms_utility.get_cpu_time-:cpu cpu_hsecs, userenv('client_info') from dual;

CPU_HSECS —————————————— 63

7

from data

9

where x <= 100000) select &1.nextval

11 12

the TKPROF report for all the statements executed (not shown) was 54 seconds, and most of that time was spent maintaining the sequence. Using the default cache sequence (default_cache_seq), you see very different numbers:

select x+1

8 10

USERENV('CLIENT_INFO') ———————————————————————————————— 0

from data /

100001 rows created. call

The resulting TKPROF reports are pretty telling. For the NOCACHE sequence (nocache_seq), you see

count

cpu

elapsed

————————— ———————— ———————————— ————————————— Parse Execute Fetch

1

0.00

0.00

5001

1.60

1.89

0

0.00

0.00

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

update seq$

total

set …

5002

1.60

1.89

where obj#=:1 call

count

cpu

elapsed

————————— ———————— ———————————— ————————————— Parse

1

0.00

0.00

Execute 100001

33.22

40.23

0.00

0.00

Fetch

0

————————— ———————— ———————————— ————————————— total

100002

33.22

You are down to about 2 seconds of runtime for the sequence operation, and the total runtime (not shown) for all the statements executed was about 4.5 seconds (yes, 4.5 seconds). Using the big cache sequence (big_ cache_seq), you see

40.23 call

It took 40 seconds to update the SYS .SEQ$ table! Every time you needed a new sequence, you had to do an UPDATE and COMMIT. The total elapsed time in

count

cpu

elapsed

————————— ———————— ———————————— ————————————— Parse

1

0.00

0.00

Execute

2

0.00

0.00

Fetch

0

0.00

0.00

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

DOWNLOAD Oracle Database 11g Release 2 bit.ly/epBiUG Oracle Database, Express Edition 11g (Beta) bit.ly/epBiUG

total

3

0.00

0.00

You basically spent no time maintaining the SYS.SEQ$ table, and the total runtime (not shown) for all the statements executed

September/October 2011 Ooraclemagazine

Something I don’t usually do If you’ve been looking for an excuse to learn Oracle Application Express and you wanted a book for working through it, Expert Oracle Application Express (Apress, 2011) is for you. I know most of the authors personally and can attest to their technical knowledge of Oracle Application Express. The technical content of the book is good, and I also really appreciate what the authors are doing with the royalties. They are contributing 100 percent of the royalties received to the families of two Oracle Application Express developers who have passed away. You can read about the book and the royalty donations at bit.ly/jSLd4q. 

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 Effective Oracle By Design amzn.to/p8c5Bu The Tom Kyte Blog tkyte.blogspot.com DISCUSS Oracle Database, Express Edition bit.ly/njntq5 FOLLOW Oracle Database on Twitter twitter.com/oracledatabase on Facebook facebook.com/oracledatabase

Database Application Developer and DBA

SQL 101 By Melanie Caffrey

Oracle database

Get Your Information in Order Part 1 in a series on the basics of the relational database and SQL

A

Lindy Groening

sk most seasoned professionals working with Oracle Database instances today what their chief complaint regarding performance issues is, and 9 times out of 10, they will answer with responses along the lines of “lack of SQL expertise,” “poorly written SQL statements,” or “poorly trained database programmers.” As relational databases have become a necessary part of everyday business life, knowledge of structured query language (SQL) has become paramount. Paradoxically, however, learning good SQL programming techniques has taken a backseat to creating, for example, user-friendly, attractive interfaces written in database-agnostic programming languages such as Java. Programmers may spend a great deal of time learning their chosen or assigned interface language and very little to no time learning SQL. This series of SQL 101 articles is for those new to or not yet completely familiar with relational database concepts and SQL coding constructs. It is for anyone learning SQL, tasked with teaching SQL to others within a workgroup, or managing programmers who write database access code. This first article in the series begins with information about the basic building blocks that all programmers (or DBAs/designers/ managers) should know when writing their first set of SQL statements.

How Is Data Organized in a Relational Database? Being able to visualize how data is organized in a database is key to retrieving that data quickly and easily. Whenever you withdraw money from an ATM, you are reading and manipulating data. Whenever you purchase anything online, you are changing data. Whether you are banking, shopping, or performing one of many business activities, you are likely interacting with a relational database.

A relational database stores data in a twodimensional matrix known as a table, and tables generally consist of multiple columns and rows. (I say generally here because it is possible to have a table with just one column and no rows, although it is not common. I will cover that exception in later articles in this series.) Relational databases employ relational database management system (RDBMS) software to help manage the task of giving a user the ability to read and manipulate data without knowing the exact file and/or drive storage device location where a particular piece of information can be found. (Oracle Database is, among other things, an RDBMS.) Users need only know which tables contain the information they seek. The RDBMS relies on SQL constructs and keywords, provided by users, to access the tables and the data contained within the tables’ columns and rows. How Is Data Represented in a Relational Database? Each table in a relational database usually contains information about a single type of data and has a unique name, distinct from all other tables in that schema. A schema is typically a grouping of objects (such as tables) that serve a similar business function. For example, three tables that contain data about employees, departments, and payroll details, respectively, may exist together inside a schema named HR. There can be only one table named EMPLOYEE inside the

HR schema (for the purposes of this introductory explanation, discussions regarding features that support the coexistence of tables with the same name, such as Oracle Database 11g’s editions and Edition-Based Redefinition, are beyond the scope of this article). Now suppose the information in an EMPLOYEE table includes the structure and content shown in Figure 1. A table consists of at least one column, and a column is a set of values of a particular type of data. Like a table’s name within a schema, a column’s name is unique within a table and should clearly identify the type of data it contains. For example, the EMPLOYEE table has columns for the employee’s first name (FIRST_NAME), last name (LAST_NAME), hire date (HIRE_DATE), and manager (MANAGER) in this initial, scaled-down representation. In Figure 1, the employee LAST_NAME Newton represents a single data element within the table. And the HIRE_DATE 14-SEP-2005 represents another single data element. Further, each row in a table represents a single set of data. For example, the row of the EMPLOYEE table with the FIRST_NAME Frances and LAST_NAME Newton represents a unique set of data. Each intersection of a column and a row represents a value. However, note that some values are not present. In Figure 1, not every row/column combination for MANAGER contains a value. In this case, such values are said to be NULL. A null value is not a blank, a space, or a zero. It is the absence of a value.

First_Name

Last_Name

Hire_Date

Frances

Newton

14-SEP-2005

Donald

Newton

24-SEP-2006

Manager

ROW 28

COLUMN Figure 1: The EMPLOYEE table

oracle magazine September/October 2011

79

80

SQL 101

The Key to Good Relations For a row to be able to uniquely represent a particular set of data, it must be unique for the entire set of row/column value intersections within the table. If the company using the EMPLOYEE table were to hire another employee named Frances Newton on September 14, 2005, and Frances Newton had no MANAGER value associated with her row in the EMPLOYEE table yet, the original entry for Frances Newton would no longer be unique. This coincidence of identical data is referred to as a duplicate. Duplicate entries should not be allowed in tables (more in subsequent articles on why this is so). Therefore, the EMPLOYEE table requires a column that will ensure uniqueness for every row, even if the company hires several employees with the same names and employment details. Enter the primary key. The primary key is a column that ensures uniqueness for every row in a table. When a primary key is added to the EMPLOYEE table, the two Frances Newtons are no longer alike, because one now has an EMPLOYEE_ID value of 37 and the other has an EMPLOYEE_ID value of 73. Figure 2 illustrates the addition of the EMPLOYEE_ID primary key to the EMPLOYEE table. Note that the EMPLOYEE_ID value appears to have nothing specifically to do with the rest of the column/row combination values that follow it within each row. In other words, it has nothing to do with the employee data per se. This type of key is often a system-generated sequential number, and because it has nothing to do with the rest of the data elements in the table, it is referred to as a synthetic or surrogate key. Using such a key is advantageous in maintaining the uniqueness of each row, because the key is not subject to updates and is therefore immutable. It is best to avoid primary key values that are subject to changes, because they result in complexity that is almost impossible to manage. A table can have only one primary key, comprising one or several columns. A key comprising more than one column is referred to as a composite or concatenated key. In some cases, a primary key may not be necessary or even appropriate. In most cases, however, it is strongly recommended that every table have a primary key. (Oracle

Employee_ID

First_Name

Last_Name

Hire_Date

37

Frances

Newton

14-SEP-2005

73

Frances

Newton

14-SEP-2005

1234

Donald

Newton

24-SEP-2006

Manager

28

PRIMARY KEY Figure 2: The EMPLOYEE_ID primary key EMPLOYEE Employee_ID

First_Name

Last_Name

Hire_Date

37

Frances

Newton

14-SEP-2005

1234

Donald

Newton

24-SEP-2006

Manager

Department ID 10

28

10

FOREIGN KEY DEPARTMENT Department ID

Name

10

Accounting

20

Payroll

PRIMARY KEY Figure 3: EMPLOYEE and DEPARTMENT tables with a foreign key relationship

Employee_ ID

First_ Name

Last_ Name

Hire_Date

37

Frances

Newton

14-SEP2005

1234

Donald

Newton

24-SEP2006

Manager

28

Dept ID

Office_1

Office_2

Office_3

10

New York

Colorado Springs

San Francisco

10

New York

San Francisco

Figure 4: EMPLOYEE table with office location columns—first normal form violation

Database does not require every table to have a primary key, however.) Careful Consideration of Foreign Relations Up to now, this discussion has focused on how data is organized in a single table. But a relational database also connects (relates) tables and organizes information across multiple tables. An important connector in a relational database is the foreign key, which identifies a column or set of columns in one table that refers to a column or set of columns in another table. In addition to connecting information, the use of foreign key relations between tables helps keep information organized. For example, if you store the department name of each employee alongside each employee’s details in the EMPLOYEE table,

September/October 2011 Oracle.com/oraclemagazine

you may very well see the same department name repeated across multiple employee listings. And any change to a department name would require that name to subsequently be updated in every row for every employee in that department. If, however, you split the data into two tables, EMPLOYEE and DEPARTMENT, as shown in Figure 3, you will be simultaneously establishing a relationship between the tables by using a foreign key and organizing the data to minimize updates and provide the best-possible data consistency. Consider the resultant data split shown in Figure 3. In Figure 3, DEPARTMENT_ID is a foreign key column in the EMPLOYEE table—it links the EMPLOYEE and DEPARTMENT tables together. You can find all employee details for every employee in a particular department by looking at that DEPARTMENT_ID

81

column in the EMPLOYEE table. The DEPARTMENT_ID value corresponds to one row in the DEPARTMENT table that provides department-specific information. Less Is More, or Less Is the Norm Key to understanding relational databases is knowledge of data normalization and table relationships. The objective of normalization is to eliminate redundancy and thereby avoid future problems with data manipulation. The rules that govern how a database designer should go about minimizing the duplication of data have been formulated into various normal forms. The design of tables, columns, and primary and foreign keys follows these normalization rules, and the process is called normalization. There are many normalization rules. The most commonly accepted are the five normal forms and the Boyce-Codd normal form. In my experience, many programmers, analysts, and designers do not normalize beyond the third normal form, although experienced database designers may. First and foremost. For a table to be in first normal form, all repeating groups must be moved to a new table. Consider the example in Figure 4, in which several office location columns have been added to the EMPLOYEE table. The table has multiple columns containing office location values for those employees who travel frequently for work and are required to physically work in multiple office locations. Office location values are listed in three columns: OFFICE_1, OFFICE_2, and OFFICE_3. What will happen when one of these employees is required to work in an additional location? To avoid problems or having to add yet another column, OFFICE_4, the database designer moves the office location data to a separate table named EMPLOYEE_OFFICE_ LOCATION, as shown in Figure 5. Second normal form and composite keys. Second normal form is a special-case normal form that has to do with tables that have composite primary keys. A composite primary key includes two or more columns. In second normal form, all nonkey columns must depend on the entire primary key. In other words, any nonkey columns added to a

EMPLOYEE Employee_ID

First_Name

Last_Name

Hire_Date

37

Frances

Newton

14-SEP-2005

1234

Donald

Newton

24-SEP-2006

Manager

Department ID 10

28

10

EMPLOYEE OFFICE LOCATION Employee_ID

Office

37

San Francisco

37

New York

37

Colorado Springs

1234

New York

1234

San Francisco

Figure 5: The EMPLOYEE and EMPLOYEE_OFFICE_LOCATION tables in first normal form EMPLOYEE OFFICE LOCATION Employee_ID

Office

Office_Phone_Number

37

San Francisco

415-595-2122

37

New York

212-489-0024

37

Colorado Springs

719-591-0078

1234

New York

212-489-0024

1234

San Francisco

415-595-2122

COMPOSITE PRIMARY KEY Figure 6: Second normal form violation

Employee_ID

First_Name

Last_Name

Hire_Date

37

Frances

Newton

14-SEP2005

1234

Donald

Newton

24-SEP2006

Manager

28

Dept_ID

Dept_Name

10

Accounting

10

Accounting

Figure 7: Third normal form violation

table with a composite primary key cannot be dependent on only part of the primary key. Figure 6 illustrates the EMPLOYEE_ OFFICE_LOCATION table, and in this table the primary key is a combination of the EMPLOYEE_ID and OFFICE columns, so any columns added to this table must be dependent on both primary key columns. The OFFICE_PHONE_NUMBER column is dependent solely on the OFFICE column, however; it has nothing to do with the EMPLOYEE_ID column. The EMPLOYEE_OFFICE_LOCATION table in Figure 6 is in violation of second normal form. For this table to comply with second normal form, another table must be created and the OFFICE_PHONE_NUMBER data must be moved to the new table.

The key and nothing but the key. Third normal form expands on second normal form. It dictates that every nonkey column must be a detail or a fact about the primary key. Figure 7 illustrates a third normal form violation. The addition of the DEPT_NAME column in this EMPLOYEE table violates third normal form in that DEPT_NAME is dependent on the DEPT_ID value, not the EMPLOYEE_ID value. Complying with the rules of third normal form necessitates creating another table and moving the department name values into this new table, with a foreign key reference from the EMPLOYEE table to the new table. (The solution in Figure 3 demonstrates third normal form: DEPARTMENT_ID is a foreign key column in the EMPLOYEE table.)

oracle magazine September/October 2011

82

SQL 101

WHERE DOES SQL FIT IN? With SQL you create new data, delete old data, modify existing data, and retrieve data from a relational database. The following statement, for example, creates a new EMPLOYEE table: CREATE TABLE employee (employee_id

NUMBER,

first_name

VARCHAR2(30),

last_name

VARCHAR2(30),

hire_date

DATE,

salary

NUMBER(9,2),

manager

NUMBER);

Note that this statement creates a table with the names of each column (aka data attribute) and each column’s respective datatype and length. For example, this line of the CREATE TABLE statement: employee_id

NUMBER

creates a table column (data attribute) called EMPLOYEE_ID with a datatype of NUMBER. This EMPLOYEE_ID column is therefore defined to contain only numeric data The SQL SELECT statement enables you to retrieve, or query, data. For example, the following SQL statement retrieves all FIRST_NAME, LAST_NAME, and HIRE_ DATE column values from this article’s EMPLOYEE table: SELECT first_name, last_name, hire_date FROM employee;

As you can see, the syntax is fairly straightforward. CONCLUSION This article introduced the organization and structure of a relational database. It described tables, columns, and rows and presented examples of data and how it is represented in

a relational database. The next article in this SQL 101 series will continue the discussion of data normalization and introduce the SQL execution environment. t

Melanie Caffrey is a senior development manager at Oracle. Caffrey is a coauthor of Expert PL/SQL Practices for Oracle Developers and DBAs (Apress, 2011). NEXT STEPS READ more about relational database design and concepts Oracle Database Concepts 11g Release 2 (11.2) bit.ly/aonqPP Oracle Database SQL Language Reference 11g Release 1 (11.1) bit.ly/jYXQZn

THE RIGHT PARTNER MAKES

A WORLD OF DIFFERENCE

WHEN yOu NEED TO IMPROvE DATA CENTER PERFORMANCE AND EFFICIENCy, WHILE REDuCING OvERHEAD, THERE’S ONLy ONE PLACE TO GO.

DIMENSION SySTEMS AND ORACLE® DELIvER POWERFuL yET COST-EFFECTIvE SOLuTIONS TO yOuR IT MANAGEMENT NEEDS. DIMENSION SySTEMS WILL HELP yOu:

Dimension Systems is a proven leader in complete enterprise infrastructure and management, with global experience and the expertise to provide scalable, reliable solutions that enhance productivity, while finding new ways to lower operational expenses.

» Boost speed and performance of multi-threaded applications with Oracle’s SPARC T3 servers. » Cut transaction times by half, while reducing power and space requirements with Oracle’s nimble Sun Storage F5100 Flash Array. » Optimize storage and simplify management of multiple data centers with Oracle’s Sun ZFS Storage Appliance.

IF yOu’RE LOOKING FOR A DATA CENTER SOLuTION THAT IS A WORLD APART, CONTACT DIMENSION SySTEMS TODAy. vISIT

© 2011 Dimension Systems. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. All company logos and product names mentioned herein may be trademarks and/or registered trademarks of their respective companies.

SEPTEMBER/OCTOBER 2011 ORACLE.COM/ORACLEMAGAZINE

www.dimensionsystems-advantage.com

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

84

In the Field by Mike Riley

Education Insurance

Stay current, and stay employed: five easy ways for technologists to update their skill sets.

A

lthough some technologists feel that the best way to take care of business is to keep their heads down and their noses to the grindstone, they are likely not doing themselves any favors with this attitude. With the rate of change in technology and tools, technologists would be wise to enhance their skill sets. Over the past few years, the annual Oracle Development Tools User Group (ODTUG) tools survey has shown a shift in technology away from some traditional development tools. For example, although core SQL and PL/SQL technologies still play a huge part in a majority of development shops today, tools such as Oracle Forms, Oracle Reports, and Oracle Designer are not as in demand as they once were. So technologists who choose to keep their heads down and focus on tools that are playing a diminishing role in an organization rather than learn new technologies may be limiting their value to their current employer, and may also limit their marketability to other potential employers. One way to enhance your current technology skill set is to attend your local community college or university. Education in this structured environment allows you to learn about the latest in Oracle technology—without traveling far from home. Additionally, you can benefit from the camaraderie and networking of learning with other like-minded individuals. Another way to learn about current technology is available to you right now from your desktop: Oracle allows you to download its software for use in a development environment. And supporting resources available on Oracle Technology Network (at oracle.com/ technetwork) include Oracle By Example tutorials, sample code, demos, and much more. In fact, one of the most powerful tools available is Oracle VM VirtualBox. This virtual machine environment allows you to load and run several prebuilt developer

One of the most powerful tools available is Oracle VM VirtualBox. appliances, including Java Development VM, Database App Development VM, SOA & BPM Development VM, Oracle WebLogic Server Hands-on VM, Oracle WebCenter Portal Framework 11g Hands-on VM, Enterprise PHP Development VM, and several Oracle Solaris VMs. Running Microsoft Windows Vista Home Premium and want to try your hand at one of these environments? Just download the Oracle VM VirtualBox software, download a prebuilt appliance, and away you go. Not satisfied with these options? Consider attending one of the many courses that Oracle University offers. These courses are available in many countries and come in several flavors. Instructor-led training lets you choose the topic and the location and then attend and learn with many other students. Live virtual classes save you travel and accommodation costs. Self-study CD-ROMs provide an even more economical learning option. Private events (available upon request) are advantageous for companies looking to educate several employees at the same time. For more information on the various options that Oracle University offers, visit education.oracle.com. Another way to continue your education is through self-study. Several great Oracle technology books have come out recently, including Achieving Extreme Performance with Oracle Exadata (Oracle Press, 2011), Look Smarter Than You Are with Hyperion Planning: An Administrator’s Guide (lulu .com, 2010), and Expert Oracle Application Express (Apress, 2011). However, the technology education option that I feel gives you the most for your money

September/October 2011 Oracle.com/oraclemagazine

is attending a user group event. Although I am partial to ODTUG (odtug.com) and Kscope (kscope11.com), there are certainly numerous events out there for you to choose from. From local user group meetings to regional, national, and international events, you have many choices. For events like Kscope, you have the opportunity to spend several days learning from the best of the best—the independent technologists who are often the speakers at these events. With several tracks, each covering a different subject area, you can choose the topics that interest you. In addition, the networking opportunities allow you to build relationships that can further enhance your value as an employee. For technologists, education choices are many. The only wrong choice you can make, in my opinion, is to ignore the value that continuing your education and expanding your skill set can bring to your career. In this day and age, a little extra education insurance can keep you happily employed. 

Mike Riley (mriley@ hortica-insurance.com) is the president of ODTUG and an Oracle ACE. Riley is a project manager/DBA for Hortica Insurance & Employee Benefits in Edwardsville, Illinois, where he has developed applications using Oracle Database and Oracle tools for more than 20 years.

Next Steps LEARN more about Oracle VM VirtualBox bit.ly/mkJ6bu prebuilt developer VMs bit.ly/iIraln ODTUG odtug.com

Exadata Replaces Teradata

36 Teradata Racks

3 Exadata Racks

Consumed 10x Energy

8x Faster

At Teradata’s Largest Asian Customer

Save The Planet, Dump Your Teradata For more information visit oracle.com/savetheplanet