TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) SQL Tip 4) OAUG Connection Point 2005 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the INDICES OF clause used for? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the June issue of Oracle Professional, Steven Feuerstein discusses the ins and outs of parameters and how to avoid some common pitfalls with their usage. Javier Narro explains the basic components for creating and executing Jobs and describes why the Oracle 10g Scheduler is a powerful tool that can quickly simplify your administration and monitoring of tasks. And Bulusu Lakshman’s introduction of the new features in PL/SQL exception handling in Oracle 10g continues with his second and final instalment. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) SQL Tip --------------------------------------------------------- Have you ever had the need to call a function from SQL, but that said function has a DML statement in it which causes an Oracle error? Well, here’s a way around that using a view and the AUTONOMOUS_TRANSACTION pragma. Assume I have a table called Student and want to get some information from the table by calling a view that queries the functions. --I’ll create 1 ‘safe’ function to return the name: create or replace function f1 (pi_id number) return varchar2 is v_name varchar2(200); begin select initcap(student_fname)|| ' ' ||initcap(student_lname) into v_name from student where student_id=pi_id; return v_name; end; / --Then create a function that does a DML: create or replace function f2 (pi_id number) return varchar2 is v_gender varchar2(200); begin select case gender when 'M' then 'Male' else 'Female' end case into v_gender from student where student_id=pi_id; -- do some DML that will cause ORA-14551 if called from SQL insert into query_log values(user,sysdate); commit; return v_gender; end; / --Now, I’ll create my view that selects from these functions to see if I can get around the ORA-14551 exception: create or replace view stud_v as select student_id , f1(student_id) fname , f2(student_id) gender from student / select * from stud_v; ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "ORA1.F3", line 8 --Nope, looks like I can’t do this. But, what if I make my function an autonomous transaction? Let’s see: create or replace function f2 (pi_id number) return varchar2 is PRAGMA AUTONOMOUS_TRANSACTION; v_gender varchar2(20); begin select case gender when 'M' then 'Male' else 'Female' end case into v_gender from student where student_id=pi_id; insert into query_log values(user,sysdate); commit; return v_gender; end; / --Now, I’ll select from the view: select * from stud_v; STUDENT_ID FNAME GENDER ---------- --------------- ------ 1111 Diane Brown Female 2299 Chris Adams Male 4568 Kevin Cox Male 5556 John Tyler Male 6874 Nancy Gibbs Female 6789 Jenny Rose Female 1199 Frank Nelson Male 6263 Ken Crick Male 3452 Nigel Turner Male 2346 Carl Dudley Male 9842 Mike Cooper Male 6901 Lily Green Female 7652 Mary Franks Female 4631 Joe Fisher Male 3459 Larry Myers Male 6738 Nick Carter Male 8390 Carol Wood Female 8872 Debby Archer Female 6474 Peter Hall Male 9835 Paul Marks Male 7777 Fred Flintstone Male 21 rows selected. Hey, it worked! And if I checked my query_log table, I’d see 21 records there. Let me know if you have experienced any cases where this has come in handy for you. -------------------------------------------------------- 4) OAUG Connection Point 2005 -------------------------------------------------------- The Oracle Applications Users Group (OAUG) is co-hosting the OAUG Connection Point 2005 event in conjunction with the Quest International Users Group Conference & Expo 2005 on June 13-16, at the Gaylord Texan Resort and Convention Center in Grapevine, Texas. In light of the recent Oracle takeover of PeopleSoft, this is a great event for Oracle Applications, PeopleSoft and J.D. Edwards users to participate in educational and networking opportunities within the Oracle community. More than 160 sessions are offered regarding topics such as 11i, Oracle E-Business Suite, business intelligence, PeopleSoft to Oracle best practices and corporate measurement. For more information, visit: www.oaug.org -------------------------------------------------------- 5) In the News -------------------------------------------------------- Researchers at Gartner Inc. have recently reported that both IBM and Oracle were virtually tied in 2004 in the Database market. Oracle gained some market share and is now even with IBM at approximately 34% each, with Microsoft in third with 20% of the total $7.8 billion market, which grew 10% from 2003. Oracle’s strength is on both the Linux and Unix operating systems where they claim 80.5% and 56% of the market respectively. IBM’s growth was in its DB2 database on the company's zSeries mainframe computers as well as on Unix. Microsoft naturally dominates on the Windows OS. * * * * RIM now has a viable competitor in the increasingly popular mobile data market. This week, Microsoft Inc. unveiled its real-time mobile messaging platform with promises of making on-the-go e-mail and other data a tool of the masses rather than just for the business and affluent consumer. Although it won’t be available until the Fall, Microsoft is touting that they will be able to bring the technology to consumers for a cheaper cost. RIM’s spokesperson was quick to point out that they have over 100 partnerships with leading phone companies around the world and that network is expanding at a rate of 20-30 each quarter. Analysts believe that this market can sustain a few competitors so there’s definitely no immediate concern at RIM’s offices. Competition will only help to foster better features and cheaper rates for consumers in the end. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The INDICES OF clause is a new Oracle 10g enhancement to Bulk Operations. By default, the collection used in a FORALL statement can’t have missing elements. Oracle10g allows us to use sparse collections with the FORALL statement and 1 of those ways is through the use of the INDICES OF clause. Let’s take a look at a simple example: declare type type_emp is table of employee.name index by binary_integer; tab_emp type_emp; begin --populate the pl/sql table from a simple loop for rec in (select emp_id, name from employee) loop tab_emp(rec.emp_id) := rec.name; end loop; -- now loop through the collection and perform an insert -- use FORALL for performance reasons. Since the collection may -- be sparse, we only want to access the elements that have data FORALL i in INDICES OF tab_emp insert into employee_backup (emp_id, name) values ( i, tab_emp(i) ); end; If you tried this test in a 9i version of Oracle, you could not use the INDICES OF and your FORALL would raise exceptions if it weren’t sparse, so your code would look something like this: FORALL i in tab_emp.first .. tab_emp.last SAVE EXCEPTIONS insert into employee_backup (emp_id, name) values ( emp_seq.nextval, tab_emp(i) ); EXCEPTION when others then null; You’d want a better error handler here using a pragma and catching the ORA-24381 exception. You can get more information about handing errors with bulk operations by reading Bulusu Lakshman’s article in the June 2005 edition of Oracle Professional.