TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) AppsWorld 2004 – did you miss it? 4) Feedback on SQL Tip from last issue 5) Oracle News 6) ANSWER TO THE POP QUIZ -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you catch errors during a Bulk FORALL statement? -------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- Oracle 10G Application Service is now available. This new release offers many new features, including enhancements in integration, Web services and enterprise portals. As part of Oracle’s Grid infrastructure software, it lets you run all of your existing applications on an enterprise grid, pooling industry-standard servers, storage, and software as needed. For more information about Oracle Application Server 10G, visit: http://www.oracle.com/appserver/index.html?content.html. -------------------------------------------------------- 3) AppsWorld 2004 – did you miss it? --------------------------------------------------------- If you missed Oracle AppsWorld 2004, you can still watch it online. You’ll hear product strategy, directions, and technology "how-to" advice from Oracle experts; plus, watch keynotes and explore online demos. For more information, visit: http://www.oracle.com/appsworld/online/index.html. --------------------------------------------------------- 4) Feedback on SQL Tip from last issue --------------------------------------------------------- In the last issue, we showed you how to login to somebody’s account without knowing their password. Well, this ‘technique’ raises security issues as well as another issue that one of our readers kindly pointed out. I’ve included the feedback below as a follow up on this topic from Bruce Copping, who works for Computer Sciences Corporation in Virginia. Many of us have used that technique before, but a word of caution is necessary for those with databases with security profiles setup to disallow a second use of the same password. That can be done with system resource profile parameters like password_reuse_max and password_reuse_time. Just like for our network passwords where we are forced to pick something new, if the DBA grabs the encrypted value, re- connects as in your example, and then tries to put the original password back, the DBA might be stopped when trying to put the original one back. This might be an unpleasant surprise. Then it would be necessary to tinker with the user's profile, either changing it or temporarily assigning the user a profile without the same password restrictions and then back again to the restricted profile after getting the original password back. The technique can still be done, just with an increased stress level and working with system resource profiles. If anyone else has any feedback, I’d love to hear it, though I can’t guarantee that it will be published. -------------------------------------------------------- 5) Oracle News -------------------------------------------------------- Oracle recently introduced the Oracle Customer Data Hub, which centralizes customer information in one place. It can be used with third-party applications not developed by Oracle, such as Siebel, SAP, PeopleSoft, Microsoft and Lawson, or legacy applications. Oracle realizes that not all customers want to convert every one of their applications over to Oracle, yet they want the ability to access customer data in 1 place. This new data hub allows customers to centralize customer data in 1 database. For more information, please review the press release at http://www.oracle.com/corporate/press/index.html?2791016.html. * * * * Oracle states that the next release of the Oracle E-Business Suite, labeled, 11i.10, is promising enhanced integration and better Industry specific functionality. The new release will support open-standard interfaces to more than 150 common business functions standardized by the Open Applications Group (OAG). Some of the industry enhancements include new capabilities for radio frequency identification (RFID), functionality to support Base1 II compliance in the financial services industry and new clinical studies and data analysis for the pharmaceuticals industry. This new version is scheduled for release in mid-2004 so visit the Oracle website for more details. * * * * Oracle’s takeover target, PeopleSoft, recently reported a strong 4th quarter, with revenues growing 34% from a year ago and earnings growing from 18 to 20 cents per share. These figures are prior to the special charges that related to the $1.7 billion acquisition of J.D. Edwards. Also, the company reported that first quarter projections would not live up to analyst’s projections. Oracle’s $19.50 per share bid for the company is still about $2 below the current trading price of the stock. -------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- Well, if you are running Oracle 9i, then you are in luck and can use the new SAVE EXCEPTIONS syntax in the FORALL statement. In addition, you can use the SQL%BULK_EXCEPTIONS collection to ‘catch’ any errors in the exception handler arising from the ORA-24381 exception. Let’s look at an example: I’ll create a simple table with a Primary Key: create table emp (emp_id number(3),lname varchar2(10)); alter table emp add constraint emp_pk primary key(emp_id); Then, I’ll write a pl/sql block to populate the table with data that I initialize in a collection using a FORALL statement. I purposely have duplicate values in the num_tab collection to force a PK violation and ‘catch’ these exceptions and display them using DBMS_OUTPUT. DECLARE TYPE NumList IS TABLE OF NUMBER; num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1,12); l_id emp.emp_id%type; dml_errors EXCEPTION; PRAGMA exception_init(dml_errors, -24381); BEGIN FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS insert into emp values (num_tab(i), 't'||num_tab(i)); EXCEPTION WHEN dml_errors then dbms_output.put_line('Number of errors is ' || errors); FOR i IN 1.. SQL%BULK_EXCEPTIONS.COUNT LOOP dbms_output.put_line('Error ' || i || ' occurred during '|| 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); dbms_output.put_line('Oracle error is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); dbms_output.put_line(num_tab(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)); END LOOP; END; / Now, you can take advantage of the performance gains using Oracle’s Bulk operations without worrying about the pesky ORA-24381: error(s) in array DML” exception.