TruTek TruTalk Newsletter for Oracle Professionals

TruTek Leaves

 

October, 2009
Greetings!
 
We're back from Oracle OpenWorld 09, with lots of news about announcements and presentations. Don't forget to check out our terrific line-up of training classes:  

Tilt your chair back and relax as we offer our first TruTek Online Training classes. That's right, you no longer have to schedule classes around the ski season in Utah... you can enjoy our classes from the comfort of your own desk!

 
We're hitting the streets! Take a look at our upcoming training classes. We're offering classes in UtahNew Jersey, Washington, Nevada, Arizona, Colorado, Massachusetts and California.

If you've started thinking about upgrading from Release 11i to Release 12, check out my Oracle E-Business Suite R11i to R12.1 Technical Upgrade class. This is a no holds barred, hands on class. This class offers an essential practice run at doing this upgrade. Plus, you'll perform the upgrade using my new book, the little r12.1 upgrade guide.

And if you're ready to do the database upgrade, but not the Apps upgrade, we've created a special hands-on class: Oracle RDBMS 11gR2 Upgrade. Learn how to do the upgrade so your company can take advantage of 11gR2's performance and cost savings features. 

Don't miss out on this terrific lineup of classes with the best trainers in their fields!

Sincerely,

Mike Swing
TruTek

IN THIS ISSUE
What We Do
the NEW little r12.1 upgrade guide is HERE!
Oracle E-Business Suite R11i/R12 Technical Upgrade Training
Onsite & Online Training
We Do More Than Just Training!
Fusion Applications Are Getting Closer
Oracle OpenWorld Final Thoughts
New Upgrading to RDBMS 11g Class
Best in Show at OpenWorld 09
Patch 'til there's nothin' left to fix...
Resolving an LTRIM Mystery
Robert Freeman
Confio
Article Writing Contest
Where to Find the Status of Invoices in WF
Hot Off the Press
QUICK LINKS
What We Do
TruTekTruTek is a national leader in technical and functional Oracle training and consulting. We also offer permanent placement services for part-time and full-time employees. We offer Oracle database and E-Business Suite consulting, training and remote services. We have a state of the art training facility in Salt Lake City, Utah.
 
If you have 5 or more people interested in a class, we can also bring classes to your company if you would like onsite training. And if you've wanted to take a training class, but were intimidated by the crowds, we also offer "Personal Training" for certain classes, as well as online training. 
the NEW little r12.1 upgrade guide is HERE!
 
the little r12.1 upgrade guideTruTek Cheer!Mike Swing's latest book, the little r12.1 upgrade guide, is available! Wondering what all the excitement's about? This edition is bigger and better than the last, as Mike continues to add information about patches and bugs and features that you need to understand to upgrade to E-Business Suite Release 12.
 
Are you looking for a guided tour? Don't worry, Mike uses this book as the basis for  TruTek's Oracle E-Business Suite R11i to R12.1 Technical Upgrade class.  He's also available to chat if you'd like to discuss your upcoming upgrade plans - just give him a call at 801 486-6655 or send him an email at [email protected].                  

 

Oracle E-Business Suite R11i to R12.1.1 Technical Upgrade Training
If your company is starting to think about upgrading to Release 12, one of the most important first steps is to try out the upgrade. TruTek's Oracle E-Business Suite R11i to R12.1 Technical Upgrade class is a 5 day hands-on class where you'll upgrade the Release 11.5.10.2 Vision instance to Release 12.1. Mike Swing does his best to make it look easy, but in reality, a good upgrade takes a lot of planning and preparation.
 
How hard can it be? We'd like to think we've taken some of the agony out of upgrading by publishing a book, the little r12.1 upgrade guide, that takes you through the steps. Our guide - and the class - include additional patches that you'll need to apply, plus an assortment of "gotchas", with their solutions.

The class size is limited to 4 to 6 students to allow plenty of instructor attention for dealing with problems that arise, and questions that students have. Each student uses a quad core Linux server with 4-8 GB of memory and 1 TB of disk space to perform the upgrade.
 
Here's some feedback from a recent student:
Great Class.  Teacher was very informative and let you work at your own pace.  My company was upgrading to 12.1.1 and Mike let me try the upgrade during class instead of the 12.0.4 upgrade.  Best part of the class was each student was able to do their own upgrade.  That way if one upgrade failed all other students could keep working at their own pace.  Can't wait to take another class with Trutek. 
Learn the Way that Suits You!
You have brains in your head. You have feet in your shoes. You can steer yourself in any direction you choose. You're on your own. And you know what you know. You are the guy who'll decide where to go.
Dr. Seuss

In these tight economic times, finding a way to get the training you need can be tough. Perhaps you've got training budget, but not travel dollars? If that's the case, we'd just like you to know that our instructors are willing to pack up their hardware and bring the class to you. And if that doesn't work for you, we're also offering some of our classes as online classes. That's right, relax, sit back in the comfort of your own office, and listen to the not-so-soothing tones of our instructors.

TruTek Online TrainingIf you're ready to start learning about Release 12, give us a call at 801 486-6655 or drop us a line. If you have a location in mind, we can track down additional students to help fill the class. And if there's a class you'd like to see offered online, let us know that too!
Did You Know? We Do More Than Just Training!
At TruTek, we offer training classes, remote database and applications administration support, on-site consulting, and permanent placement services for part-time and full-time employees. And in the next month, a few of our top resources will be rolling off of projects, so check them out:

Mike Swing - Besides teaching many of our E-Business Suite technical classes, including several of our DBA classes, Mike has extensive experience implementing, upgrading and maintaining various Oracle RDBMS versions and the E-Business Suite. Mike teaches our classes on installing and upgrading to Release 12, so if you need someone with plenty of hands on experience, he is one of our best resources. Mike also has experience supporting RAC implementations and has a deep understanding of parallel concurrent processing, load balancing and failover. 
 
Eric - Eric has a very deep understanding of both Unix and Oracle DBA technical material, and is a solid, very experienced Oracle Applications DBA. His strong analytical skills make him a valuable member of any team.
 
Dennelle - It's always good to have a skilled financials functional expert in your corner. Dennelle specializes in General Ledger, Fixed Assets, Purchasing, Payables, Receivables, Cash Management, Order Management, ADI, FSGs and UPK.  Dennelle is also a great trainer, so her hand-offs to clients go especially well.

Rick - With 15 years of manufacturing planning, forecasting and inventory management experience, and 10 years of functional Oracle ERP implementation experience, Rick is one of our top manufacturing experts for the E-Business Suite. Rick specializes in supporting the Oracle BOM, CST, EAM, ENG, INV, PO, MRP, MSCA and WIP modules.

Susan - Offering strong technical skills combined with excellent project management and team lead capabilities, Susan is an experienced ERP application designer and developer. Susan is a Certified Oracle Application Developer, and can resolve issues quickly due to her wide variety of experience and technical knowledge.

Fred - Fred has strong experience supporting large, worldwide Oracle E-Business Suite implementations and upgrades as a project manager, lead, and functional consultant. Fred's experience includes both Project Manufacturing and Oracle Financials. 

Joe - Joe is an Oracle developer with over 12 years of experience in designing, building, maintaining and enhancing both ERP and custom applications across a variety of business sectors. 

Bob - Bob is an accomplished Oracle DBA with extensive experience as an Oracle Apps DBA. Bob's E-Business Suite experience includes installing, updating and cloning environments.

Nathan - Nathan has both functional and technical expertise, which makes him a valuable asset in supporting the E-Business Suite. Nathan has worked as both an Oracle Database Analyst and Oracle Financial Applications specialist, responsible for implementing Oracle Accounts Payable, Oracle Purchasing, Order Management, Fixed Assets, Cash Management, Accounts Receivable and Oracle General Ledger. Nathan has performed data conversions as well as Oracle database installation, reorganization, tuning, and instance recovery on various Linux, Unix, NT, and VAX/VMS platforms. Nathan's technical skills include RMAN backup and recovery, RAC on AIX, Oracle Warehouse Builder, Workflow and XML Publisher.

Jim - Jim is one of our best manufacturing consultants. His experience includes full life cycle implementations in manufacturing management, purchasing management, production planning, and materials control. Jim has worked as a Project Manager, Team Leader, and as a Functional Application Implementer.

Craig - Craig's most recent assignment had him multi-tasking as a Unix System Administrator, Oracle Database Administrator, Applications System Administrator and E-Business Suite Developer. Craig's solid technical background makes him an excellent candidate for any E-Business Suite team.

Also, if you're looking for a developer to design custom software or programs for your company, we have a Java team ready to roll!  We have other consultants with E-Business Suite, DBA and developer skills available as well.

Contact us if you'd like to see more resumes.

Oracle OpenWorld 2009

It's Alive! It's Alive! Fusion Applications Are Getting Closer!
FrankensteinWell sure, we've been hearing about the Fusion Applications for a while, but that's just because Oracle is set on doing them right. They've learned the lessons over the years about false starts and Applications releases offered up before their time, so Fusion is being handled with extra care. 
 
Fusion Sales Dashboard
 
 
 
 
 
 
 
 
 
 
 
 
Larry Ellison's keynote at Oracle OpenWorld included a demonstration of the Fusion Applications, given by Steve Miranda and Chris Leone. The audience seemed impressed by what they saw, an indication that Oracle's Oracle Applications User Expterience Team has listened and incorporated feedback from end users.
 
Take a look at Floyd Teter's ORCLVILLE blog for his impressions of the Fusion Applications. Click here if you'd like to see more Fusion Applications screen shots from the conference. 
Oracle OpenWorld 2009 Final Thoughts by John Stouffer
Guest Author John Stouffer offered up some of his views after attending Oracle OpenWorld 2009:
 
Now that I've finally caught up with all of my email and have had a moment to reflect on this year's lightly attended OOW (25,000 of my closest Oracle friends ), I thought I would list the items that caught my attention the most:
 
Fusion Applications - nobody but Larry was allowed to say the words "Fusion Applications" until after his keynote.  I believe we will see a "full" release sometime next year but it's going to have limited functionality especially when compared to the current generations of PeopleSoft and E-Business Suite.  I'd recommend you look at them but you might want to use the approach outlined below first so that you get comfortable with the new technology, features and look and feel.
 
Fusion Apps-based Analytics were touted about in many presentations as add-ons to your current ERP environment.  Many of the Analytic packages are in the Fusion Applications, but these have the ability to standalone with a Release 11i/12 environment. It's a good way to "put your toe into the water", so to speak, as you continue to maximize your investment from your current applications.
 
The number one message I took away was that upgrading to the 11g database version is well worth the costs that would be incurred from a database-only upgrade.  With all of the immediate uses of the new features and functionality available to the Release 11i/12 Applications (which is not normally the case), the upgrade can pay for itself in a relatively short period of time.
 
Finally, Oracle now realizes that these applications are very complicated and a toolkit of easy to use utilities (not quite free yet!) is required to manage and maintain the applications technology and code set so that the applications perform as advertised.  The Application Management Pack (AMP) and Application Change Management Pack (CMP) plug-ins for Oracle Enterprise Manager were pushed in several sessions.  The prices for these two packs has come down dramatically from what they were initially introduced at, and they're a great set of tools for your Apps DBA to manage your environment.

All in all, it was a nice show because of the lighter than usual attendance, worst storm in 50 years, and a minor 3.5 earthquake, along with some really old rock and rollers for the audience.  And not to forget the Terminator made an appearance...:-)

I can't wait until next year to see the surprise at Larry's keynote.
A New Class from TruTek - Upgrading to RDBMS 11gR2
Perhaps you're not ready to upgrade your E-Business Suite of Applications, but you are ready to upgrade your RDBMS? The word is out that 11gR2 has a lot to offer E-Business Suite users, so we've decided to offer a hands-on  "RDBMS Upgrade Only" class. The class is intended for DBAs and Apps DBAs - you don't have to be running the E-Business Suite to take this class; we just want to make sure we cover Apps-specific topics in addition to doing the upgrade.
 
Let's start by describing some of the "must have" features. We've borrowed a slide from Andy Mendelsohn's OOW presentation Lowering Your IT Costs with Oracle Database 11g Release 2:  
 
Oracle Database 11g Release 2
Specific Areas of Cost Reduction
  • Reduce hardware capital costs by factor of 5x
  • Reduce storage costs by factor of 10x
  • Eliminate downtime AND unused redundancy
  • Improve performace by at least a factor of 10x
  • Considerably simplify your software portfolio
  • Raise DBA productivity by at least 2x
  • Reduce upgrade costs by a factor of 4x
You can check out the paper Lowering Your IT Costs with Oracle Database 11g Release 2 at http://www.oracle.com/technology/products/database/oracle11g/pdf/oracle-database-11g-release2-overview.pdf
 
Here's the class agenda:
 
Day 1
            Database Upgrade Overview
            Install 9.2.0.6 instance

            Upgrade RDBMS Version 9.2.0.6 to Version 10.2.0.3
Day 2
            Complete the Database Upgrade to 10.2.0.3
            Daylight Savings Time patch 4
            Install 11.1.0.7 Oracle Home
            Upgrade RDBMS Version 10.2.0.3 to Version 11.1.0.7
Day 3  
            Finish RDBMS Version 11.1.0.7 Upgrade
 
Check out our class schedule. We'll offer this class online, onsite and at locations around the country. Also, we cover the 11gR2 upgrade in the little r12.1 upgrade guide.
Best in Show at OpenWorld 09
 
TruTek Thumbs UpThere were a lot of terrific presentations at Oracle OpenWorld this year. Here are a few of our "favs". TruTek gives a big thumbs up to:
 
Dr. Nadia Bendjedou, Oracle's Director of Product Strategy for Oracle Applications Development, presented 10 Things You Can Do to Prepare for the Next-Generation Applications. You can download the detailed white paper and the Oracle Applications Planning tool. As your organization prepares for the future E-Business Suite applications, you can use the white paper and tool to help determine areas that need to be addressed upfront. After using the tool, which currently asks 46 questions about your environment, Oracle provides a screen that shows your status:
 
Oracle Applications Planning Tool
 
Jonathan Lewis presented Hints on Hints and was rated by attendees as one of the top 10 presentations at the conference. We've borrowed a slide from Jonathan's presentation that summarizes his views nicely:
 
Guidelines for hinting
1. Don't
2. When you ignore #1 - be very careful
3. Try to stick to "strategic" (block level) hints
4. Check the outline for complete hinting
5. Test after every upgrade and patch
6. Test after every piece of associated DDL
 
Jonathan is one of our favorite authors / presenters / instructors, and will be teaching training classes in Boston for Trutek in May. His classes are always packed, so sign up early if you can!
 
Are there more?
 
If you attended a particularly good presentation at OOW, drop us a line and tell us about it!
Patch 'til there's nothin' left to fix...
Steven Chan's Oracle E-Business Suite Technology blog included a number of interesting articles last month, including several about E-Business Suite Release 12. Important patch announcements include the arrival of the October 2009 Oracle Critical Patch Update (CPU) and a Critical Rollup Patch 8459663 for E-Business Suite Integrated SOA Gateway Release 12.1.1.
 

Pearls of Wisdom From Our Favorite Instructors

TruTek Pearls of Wisdom

Resolving an LTRIM Mystery by Steven Feuerstein
Steven Feuerstein is a noted (and prolific) author, instructor, and an Oracle ACE. We received such good feedback from his last training class that we're bringing him back! Steven will be presenting The Best of Oracle PL/SQL Seminar in December in New Jersey and Dallas. Don't miss it!
 
In the meantime, we have Steven's insights about the use of the LTRIM command: 
 
I received this email recently: 

Hi Steven

When executing the following query I receive an output with the first letter missing. Is this an Oracle bug?

select LTRIM('
believe it or not','
') from dual;

Thanks, Marcel

First of all, I need to point out to Marcel and perhaps many other PL/SQL developers: you do not need to call LTRIM (or SYSDATE or USER or....) within a SELECT from dual. Instead you can run this SQL function directly inside PL/SQL, as in:

BEGIN
   DBMS_OUTPUT.put_line (LTRIM ('
believe it or not', '
'));
END;

If you do, you will see this output:

elieve it or not

Marcel feels that this is the wrong result, but in fact it is perfectly correct. You see, LTRIM does not trim by patterns of characters. It trims any and all of the individual characters you provide in the list. So what Marcel requested in the above code can be translated into English as follows:

"Please trim < and b and r and / and > from the beginning of this string."

Thus, the following block will display the same results:

BEGIN
   DBMS_OUTPUT.put_line (
      LTRIM ('<<>>brrrrrrr/////
believe it or not', '
'));
END;

I assume what Marcel wanted to do was remove from the start of the string only if it is present. To do that, you might write something like this:

CREATE OR REPLACE FUNCTION lremove (string_in         IN VARCHAR2
                                  , remove_in         IN VARCHAR2
                                  , case_sensitive_in IN BOOLEAN DEFAULT TRUE
                                   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN CASE CASE
                  WHEN case_sensitive_in THEN INSTR (string_in, remove_in)
                  ELSE INSTR (UPPER (string_in), UPPER (remove_in))
               END
             WHEN 1
             THEN
                SUBSTR (string_in, LENGTH (remove_in) + 1)
             ELSE
                string_in
          END;
END lremove;
/

BEGIN
   DBMS_OUTPUT.put_line (lremove ('
believe it or not', '
'));
   DBMS_OUTPUT.put_line (lremove ('Whether you believe it or not', '
'));
END;
/

believe it or not
Whether you believe it or not
 
Oracle PL/SQL Programming by Steve FeuersteinBooks by Steven Feuerstein:
 
Be sure to check out Steven's latest book, Oracle PL/SQL Programming. This edition covers: 
  • Optimize PL/SQL performance with the aid of a brand-new chapter in the fifth edition;
  • Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation from subtypes, and enhancements to native compilation, triggers, and dynamic SQL;
  • Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects.
Check out Steven's latest papers:
Hints for Direct-path Insert Statements by Christian Antognini
Troubleshooting Oracle Performance by Christian AntogniniWe're excited to announce that Christian Antognini will be conducting a class for us in March! Christian wrote the very popular book Troubleshooting Oracle Performance, and will be teaching a class based on the book.
 
Following is an article by Christian about RDBMS 11gR2: 
 
Up to Oracle Database 10g Release 2, direct-path inserts are supported only by INSERT INTO ... SELECT ... statements (including multitable inserts), MERGE statements (for the part inserting data), and applications using the OCI direct-path interface (for example, the SQL*Loader utility). At the statement level two methods are available to specify that a direct-path insert has to be used:

  • Specify the APPEND hint in the SQL statement
  • Execute the SQL statement (actually, at least the INSERT part) in parallel
Let's have a look to an example. Notice that:
  • The APPEND hint is used to execute a direct-path insert.
  • The APPEND hint does not work with a "regular" INSERT statement that uses the VALUES clause.
  • To check whether the direct-path insert is performed, the modified table is queried without committing (or rolling back) the transaction. As a result, after a direct-path insert the database engine raises an ORA-12838.
SQL> SELECT * FROM v$version WHERE rownum = 1;   

BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi   

SQL> CREATE TABLE t (n NUMBER); 
SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;   
SQL> SELECT * FROM t; 
SELECT * FROM t                
              *  ERROR at line 1: 
ORA-12838: cannot read/modify an object after modifying it in parallel   

SQL> COMMIT; 
SQL> INSERT /*+ append */ INTO t VALUES (2);   
SQL> SELECT * FROM t; 
          
         N 
----------          
         1          
         2 

Strangely enough, at least for me, in Oracle Database 11g Release 1 the behavior of the APPEND hint has changed. In fact, it is accepted also for a "regular" INSERT statement that uses the VALUES clause. Let's run the same test as before to illustrate the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1; 
 
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production   

SQL> CREATE TABLE t (n NUMBER);  
SQL> INSERT /*+ append */ INTO t
SELECT 1 FROM dual;   
SQL> SELECT * FROM t; 
SELECT * FROM t               
              * 
ERROR at line 1: 
ORA-12838: cannot read/modify an object after modifying it in parallel
   
SQL> COMMIT;
SQL> INSERT /*+ append */ INTO t VALUES (1);   
SQL> SELECT * FROM t;  SELECT * FROM t               
                   * 
ERROR at line 1: 
ORA-12838: cannot read/modify an object after modifying it in parallel 

Even more strange, in Oracle Database 11g Release 2 the behavior of the APPEND hint was reverted to the pre-11g one! But, since the feature is really useful in some situations, a new hint called APPEND_VALUES is available. The following example illustrates the new behavior.

SQL> SELECT * FROM v$version WHERE rownum = 1;   

BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production   

SQL> CREATE TABLE t (n NUMBER);   
SQL> INSERT /*+ append */ INTO t SELECT 1 FROM dual;   
SQL> SELECT * FROM t; 
SELECT * FROM t               
              * 
ERROR at line 1: 
ORA-12838: cannot read/modify an object after modifying it in parallel   

SQL> COMMIT;   
SQL> INSERT /*+ append */ INTO t VALUES (2);   
SQL> SELECT * FROM t;         

         N 
----------          
         1          
         2   

SQL> COMMIT;   
SQL> INSERT /*+ append_values */ INTO t VALUES (3);   
SQL> SELECT * FROM t; 
SELECT * FROM t  
              *  ERROR at line 1: 
ORA-12838: cannot read/modify an object after modifying it in parallel 
SystemTap is production supported in Redhat EL5.4 by Tanel Poder
Tanel Poder, an Oracle ACE Director and Oracle Certified Master, is one of our most popular instructors. He'll be teaching two classes in February, 2010, Advanced SQL Tuning and Advanced Oracle Troubleshooting. Check out Tanel's blog, Core IT for Geeks and Pros.
 
If you don't know what SystemTap is - it's the Linux world's attempt to build Solaris DTrace-style safe, dynamic instrumentation into the Linux kernel.

I'm not going into religious discussions about which one is better here; I have used both SystemTap and DTrace successfully for diagnosing low level issues inside OS kernel, so both are good enough for me :)
The problem with SystemTap, though, has been that it's not production quality, rather, it has been a technology preview. But with RHEL 5.4 part of it has changed. Redhat says so in the following article:
SystemTap is no longer a technology preview, and now has production support. Red Hat recommends that users run scripts on development machines before deployment in production environments. Since SystemTap is an optional diagnostic tool, users can easily stop using it in the event of a problem. Options such as -g for Guru mode, and -D* allow users to disable several security checks. Scripts using these options may not be supported.
 
Red Hat plans to fix problems in SystemTap, or the Linux kernel, as they arise in connection with new scripts. In some cases, a fix may include extending the blacklist for known areas of the Linux kernel that are unsafe to probe. All scripts that use probes targeting blacklisted areas will need to be revised. SystemTap users are advised to upgrade to this version.
So, at least officially, SystemTap is now supported by Redhat. Well, at least as long as you're not doing crazy stuff with the -g option -  the geek mode ;-)
 
I would still be very careful before using any SystemTap features in production. In fact, in critial environments I wouldn't run it at all, unless there is no other option (and you're prepared to get a crash + kernel panic).
 
However, the significance of this note is that SystemTap is accepted for production by Redhat in principle. So now it's just a matter of time until it gets stable and widely used enough to be as practical and useful as DTrace is on Solaris.
Learn From the Master: Robert Freeman
TruTek karateYes, Grasshopper, you too can learn from the best - Robert Freeman, noted author, is also an Oracle ACE and a karate black belt. Don't miss Robert's upcoming classes: Oracle 11g DBA Boot Camp I, Backup and Recovery Using 10g RMAN, Oracle 11g DBA Boot Camp II, and Oracle Database 11g New Features.
 
Here's some feedback from Robert's Oracle 11g DBA Bootcamp I class:
 
Excellent class.  Nice hands on examples. 
I consider myself an OEM (point & click( DBA. This is an excellent class to take the next step to be a real DBA.
 
The class was very good.  I learned so much that will help me in my job.
 
The drive to class... tolerable (she commuted over an hour each way each day). Time away from the office... well spent. Oracle training at TruTek... Priceless!

Robert Freeman's OCP: Oracle Database 11g Certification KitRobert's latest technical book, the OCP Oracle Database 11g Certified Professional Study Guide, is available. It's a prep guide for the Oracle Database 11g OCP Exam (Exam 1Z0-053). Check out this and other great books on our books link.  
 
   
Other Papers by Robert Freeman:
Confio Igniter Suite

Confio builds performance management software that improves the effectiveness of IT systems and the people who run them. The Confio Igniter Suite currently includes products that help Oracle DBAs, managers, and developers continuously monitor the performance of databases and the applications that depend on them, isolate specific problems, and identify solutions in production systems.

Confio Ignite


Your Claim to Fame...
AmazonYou might argue that our newsletter staff weighs in more on the technical side than the functional side. Yes, we admit it, we're geeks. But that doesn't mean we don't want to include articles about the functional side of the E-Business Suite. Really, we do. So here's your chance to help us keep a balanced perspective in our newsletter. If you've got a good functional topic, please, let us know. Enter our writing contest!  If you'd like to enter, just write an article about Oracle or the Oracle E-Business Suite. Tell us about a technique that you've used that made your life easier at work. Or describe something you've learned from all those books on your bookshelf, and how it applied to your environment. If you're not sure if you've got a good topic, send us a note, and we'll help you figure it out. Go ahead, you know you want to!

Submissions should be sent to [email protected] by November 25th. We'll include the winning article in our next newsletter. And the winning author will win a very cool prize, a $50 Gift Certificate from Amazon!
AP Invoice Query to Find the Status of Invoices in Workflow by Chris Compton
Chris Compton, from ASRC in Anchorage, Alaska, iis our newest guest author and winner of the prestigious TruTek Writing Contest. 
 
The design of invoices and workflow comes into play after one has been submitted for approval and after it's approved or  rejected. After taking responsibility for workflow, I took many calls to determine the status of an invoice persisting in an "In Progress" workflow status. This drove me to write the following extract:

WITH InWF AS   
 (
 -- open in workflow 
 SELECT to_number(SUBSTR(wias.item_key, 1,instr(wias.item_key, '_', 1) - 1)) Invoice_id,
  wias.error_name                                                                        ,     
  wias.error_message      
   FROM applsys.wf_item_activity_statuses wias     
  WHERE wias.end_date IS NULL   
  AND wias.item_type = 'APINV'), OnesToWatch AS (
 -- open in workflow    
  SELECT * FROM InWF           
   UNION       
 -- Invoices not in workflow but *should be*      
  SELECT b.invoice_id, 'N/A' error_name,'NotInWorkflow' error_message      
   FROM ap.ap_invoices_all b, InWF c     
  WHERE b.wfapproval_status = 'INITIATED'
  AND b.invoice_id = c.invoice_ID(+) AND c.invoice_id IS NULL)  
 SELECT aia.org_id, aia.invoice_num, aia.invoice_id, aia.invoice_date,    
  aia.cancelled_date   ,    aia.wfapproval_status,    c.error_message      ,    
  aia.attribute1       ,    aia.attribute2       ,    aia.attribute3       ,   
  aia.attribute4       ,    aia.attribute5   
 --, c.*     FROM OnesToWatch c, ap.ap_invoices_all aia    WHERE aia.invoice_id       = c.invoice_id  AND NOT (aia.cancelled_date IS NOT NULL AND c.error_message          ='NotInWorkflow')  AND c.error_message         IS NOT NULL  ORDER BY 1, 2 

In broad strokes, this query pulls in data from two sources.

From workflow, the first extraction pulls all active APINV workflows, changing item_key to invoice_id. From Accounts Payable, the second extraction pulls additional invoices that have been submitted for approval. (i.e. an APINV should exist) nbsp;

Those in workflow without any errors are pulled in the first extraction showing a blank error message. Those invoice_ids not in workflow but pulled from the second extraction have a blank error message as well.

Invoices cancelled after submitted to workflow were a strange exception. You may not experience this at your site.

How I use it

In general, I run this to find all invoices worth reviewing. If an invoice displays in this list with a cancellation date, I cancel the workflow. If it shows as manually approved, I cancel the workflow. If not, I review the transactions myself before raising its visibility to our AP personnel for manual approval.

 
Hot Off the Press...  
Oracle Data Guard 11g Handbook
Co-authored by Larry Carpenter, Joseph Meeks, Charles Kim, Bill Burke, Sonya Carothers, Joydip Kundu, Michael Smith and Nitin Venguerleker, the Oracle Data Guard 11g Handbook describes how Data Guard provides superior data protection, availability, and disaster recovery using the tested techniques in this Oracle Press guide. Co-written by a team of Oracle experts, the Oracle Data Guard 11g Handbook provides a sound architectural foundation along with best practices for configuration, monitoring, maintenance, and troubleshooting. You will get full details on implementing deployment architectures to address requirements that extend beyond disaster recovery. This invaluable resource also includes a complete set of monitoring scripts available for download.
 
We're seeing nothing but positive feedback on this book on Amazon:
I just love this book. The best book on Data Guard i have read so far. Very detailed and covers the basic fundamental concepts of database that helps in understanding a Data Guard architecture. A must for every and any DBA (from a beginner to expert). It not only talks about the stanbdby features in the previous versions but also tell how the database has evolved and gro wn with the business and market, specifically with the release of 11g. It also talks about the Best Practices and the DR approaches different customers take in different situations. According to me this is like a Data Guard Bible.  
 
Biswaroop Biswal 
 
If you have been deploying High Availability and Disaster Recovery solutions then you probably agree that the MAA docs and white papers are among the best material published on the Oracle OTN website. 
  
This book actually manages to go deeper and beyond the docs, white papers and all conference presentations you may have attended on Oracle Data Guard.
 
Aris Prassinos, Oracle ACE
Learning SQLAnother popular book released this year is Alan Beaulieu's Learning SQL:
 
Each chapter presents a self-contained lesson on a key SQL concept or technique, with numerous illustrations and annotated examples. Exercises at the end of each chapter let you practice the skills you learn. With this book, you will: 
  • Move quickly through SQL basics and learn several advanced features
  • Use SQL data statements to generate, manipulate, and retrieve data
  • Create database objects, such as tables, indexes, and constraints, using SQL schema statements
  • Learn how data sets interact with queries, and understand the importance of subqueries
  • Convert and manipulate data with SQL's built-in functions, and use conditional logic in data statements
Knowledge of SQL is a must for interacting with data. With Learning SQL, you'll quickly learn how to put the power and flexibility of this language to work.
If a person is looking to become a database administrator, or just needs to get up to speed on the basics with some understanding of what they are doing and why, this is an excellent place to start. However, this book does require that you have some experience with computers, especially a base knowledge of what a database is and how or why programs might want to interact with one. This isn't a book for someone who is a complete novice to the realm of data storage and interaction, but rather intended for people who know what they want to do and why and need an in depth manual for how to accomplish the task of storing, retrieving, and using data.

I love that the book is written by a very experienced Oracle administrator, but uses MySQL as the base for all examples and instruction. The book itself focuses on teaching the ANSI standard clearly and well, but gives great examples using one free and open source product that is available for any reader to download and install on their system. Then, the author discusses some of the differences that may be discovered by readers as they move into the real world and begin to interact with other database systems such as Oracle or SQL Server.

The book uses examples that are well formed, clear, and which adhere well to standards. I especially appreciate that the examples use good coding technique that is easy to read and would be a joy to maintain in the wild if it were discovered by a person taking over responsibility for a database they did not create. 
 
M. Helmke
Enough Already!
Our newsletter editor's dad, who is 82, likes to spend a little bit of time every day shredding his junk mail and then putting it into the pre-addressed stamped reply envelope and mailing it back in. That's one way to deal with too much information! We know how it is. You're on everybody's mailing list, and maybe you're just not interested in being there. Hey, we don't want to cram your mailbox unless you'd like to hear from us. So here's what you can do:

If you like our newsletter and think someone you know would like it as well, click on this box to forward it:
Forward to a Friend

And, if you're just not the right person for this newsletter, click on Safe Unsubscribe at the bottom of this newsletter and take yourself off the list.

And if you accidentally remove yourself from the list and want to be put back on, click here, enter your email and be sure to click Submit, then click Update Profile in the email that you receive.

And if we're just not hitting the topics that you want to hear about, either submit an article yourself, or click here and fill out our survey. We know people who know stuff - that's our claim to fame - and we'll get them to write about that stuff!
TruTek Training Schedule 
We're always open to adding new classes, so let us know your interests! We add new classes regularly, so be sure to check the latest version of the schedule on our website. 

*** Class Dates and Locations are Subject to Change
Time Zone for Online Classes is MST 
 

Date

Class

Location

 

Online Technical Classes

 

MST

Nov 2-6

Advanced PL/SQL Tips and Techniques

9:30 am-1:00 pm

Nov 16-20

Oracle 11i/R12 Applications  DBA Concepts and Administration

9:00 am-5:00 pm

Nov 16-20

Introduction to SQL

9:00 am-12:00 pm

Nov 30- Dec 4

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

9:00 am-5:00 pm

Nov 30- Dec 4

Oracle SQL Performance Tuning Tips and Techniques

9:30 am-1:00 pm

Dec 14-18

Oracle 11i/R12 Applications  DBA Concepts and Administration

    9:00 am-5:00 pm

Dec 14-18

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

    9:00 am-5:00 pm

Dec 14-18

Introduction to SQL

    9:00 am-12:00 pm

Jan 11-15

Oracle SQL Performance Tuning Tips and Techniques

    9:30 am-1:00 pm

Jan 11-15

Oracle Release R12 Applications System Administration

    9:00 am-12:00 pm

Jan 25-29

Oracle 11i/R12 Applications  DBA Concepts and Administration

    9:00 am-5:00 pm

Feb 8-12

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

    9:00 am-5:00 pm

Feb 22-26

Introduction to SQL

    9:00 am-12:00 pm

Mar 1-5

Advanced PL/SQL Tips and Techniques

    9:30 am-1:00 pm

Mar 1-5

Oracle RDBMS 11gR2 Upgrade

    9:00 am-12:00 pm

 

Live Technical Classes

 

 

Oracle Apps Technical Classes

 

Nov 3-5

Oracle Release R12 Applications System Administration

SLC, UT

Nov 9-13

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

Denver, CO

Nov 9-13

Oracle 11i/R12 Applications  DBA Concepts and Administration

Denver, CO

Nov 30-Dec 4

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

Las Vegas, NV

Nov 30-Dec 4

Oracle 11i/R12 Applications  DBA Concepts and Administration

Las Vegas, NV

Dec 1-3

Oracle RDBMS 11gR2 Upgrade

SLC, UT

Dec 7-11

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

New Jersey

Dec 7-11

Oracle 11i/R12 Applications  DBA Concepts and Administration

New Jersey

Jan 11-15

Oracle RDBMS 11gR2 Upgrade

SLC, UT

Jan 18-22

Oracle Release R12 Applications System Administration

Oakland, CA

Feb 1-5

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

Oakland, CA

Mar 1-5

Oracle Release R12 Applications System Administration

Washington, DC

Mar 8-12

Oracle 11i/R12 Applications  DBA Concepts and Administration

Washington, DC

May 17-21

Oracle E-Business Suite R11i-R12.1 Technical Upgrade

Boston, MA

May 18-20

Oracle Release R12 Applications System Administration

Boston, MA

May 17-21

Oracle 11i/R12 Applications  DBA Concepts and Administration

Boston, MA

 

BI & Reporting Classes

 

Jan 18-20

Introduction to Oracle APEX Iwith Scott Spendolini

SLC, UT

Jan 21-22

Introduction to Oracle APEX IIwith Scott Spendolini

SLC, UT

Jan 25-27

Intermediate Oracle APEXwith Scott Spendolini

SLC, UT

Jan 28

Troubleshooting & Debugging APEX with Scott Spendolini

SLC, UT

Jan 29

APEX Installation and Administration with Scott Spendolini

SLC, UT

Feb 16-19

Introduction to Oracle Business Intelligence EE (OBIEE)

SLC, UT

Dec 15-16

Oracle Discoverer for End Users Training

SLC, UT

Dec 17-18

Oracle Discoverer for Administrators

SLC, UT

 

Oracle Database Classes

 

Nov 17-20

Scaling to Infinity: Partitioning in Data Warehouses on Oracle with Tim Gorman

SLC, UT

Dec 1-3

Oracle RDBMS 11gR2 Upgrade

SLC, UT

Dec 7-11

Oracle 11g DBA Boot Camp I with Robert Freeman

New Jersey

Dec 8-11

Relational Database Modeling with Robert Freeman

SLC, UT

Dec 15-18

Backup and Recovery using Oracle 11g RMAN with Robert Freeman

SLC, UT

Jan 11-15/10

Oracle 11g DBA Boot Camp II with Robert Freeman

SLC, UT

Jan 11-15

Oracle RDBMS 11gR2 Upgrade

SLC, UT

Feb 22-25

Advanced Oracle Troubleshooting with Tanel Poder

Oakland, CA

Mar 16-19

Relational Database Modeling with Robert Freeman

SLC, UT

Mar 23-26

Troubleshooting Oracle Performance with Christian Antognini

SLC, UT

Mar 30-Apr 2

Backup and Recovery using Oracle 11g RMAN with Robert Freeman

SLC, UT

Apr 12-16

Oracle Database Performance Tuning with Robert Freeman

SLC, UT

Mar 23-26

Troubleshooting Oracle Performance with Christian Antognini

SLC, UT

May 17-21

Oracle 11g DBA Boot Camp I

Boston, MA

May 17-20

Backup and Recovery using Oracle 11g RMAN with Robert Freeman

Boston, MA

May 17-21

Oracle 11g DBA Boot Camp II

Boston, MA

 

Developer Classes

 

Nov 10-12

Introduction to SQL

Denver, CO

Dec 8-10

Introduction to SQL

New Jersey

Dec 10-11

The Best of Oracle PL/SQL Seminar with Steve Feuerstein

New Jersey

Dec 12-13

The Best of Oracle PL/SQL Seminar with Steve Feuerstein

Dallas, TX

Dec 15-17

Introduction to SQL

Phoenix, AZ

Dec 15-16

Oracle SQL Performance Tuning Tips and Techniques

SLC, UT

Dec 17

Toad Tips and Techniques

SLC, UT

Jan 19-21

Introduction to SQL

Las Vegas, NV

Jan 25-26

Oracle SQL Performance Tuning Tips and Techniques

Denver, CO

Jan 27-29

Advanced PL/SQL Tips and Techniques

Denver, CO

Feb 10-12

Advanced SQL Tuning with Tanel Poder

SLC, UT

May 17

Writing Optimal SQL Seminar with Jonathan Lewis

Boston, MA

May 18

Troubleshooting and Tuning Oracle Seminarwith Jonathan Lewis

Boston, MA

 

Functional Classes

 

Oct 26-27

Oracle Release 11i Fixed Assets

SLC, UT

Nov 17-19

R12 AME

Albany, NY

Dec 1-3

Oracle Release 12 New Features (Functional) 

SLC, UT

Dec 8-11

Oracle R11i/R12 Financials Business Process Overview

SLC, UT

Dec 15-18

Oracle Release 11i Procure to Pay

SLC, UT

Dec 15-16

Oracle Release 11i Accounts Payable

SLC, UT

Dec 17-18

Oracle Release 11i  Purchasing

SLC, UT

 

Conferences and User Group Meetings - We'll Be There!

 

Nov 10

Regional BI User Group Meeting

SLC, UT

Feb 16-18/10

RMOUG Training Days

Denver, CO

Apr 18-22

Collaborate 10

Las Vegas, NV

 
Contact us for Group Discounts and Additional Offers 801-486-6655