TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) This Month in Oracle Professional 3) PL/SQL Tip 4) Oracle OpenWorld 2005 5) In the News 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Can a synonym become invalidated? --------------------------------------------------------- 2) THIS MONTH IN ORACLE PROFESSIONAL --------------------------------------------------------- In the September issue of Oracle Professional, Steven Feuerstein takes the next step in describing a framework for detecting NO_DATA_FOUND exceptions when fetching single rows of data through a function. In this second installment, Steven introduces the ability to dynamically represent datatypes in PL/SQL and uses it to simplify the NDF framework code. Roy Gomes shows you how you can incorporate the business event framework into your application, creating code that allows customers to extend rather than customize the base application. This is based on Oracle Workflow Builder 2.6 with an Oracle10g database. The September issue is available online now; even non-subscribers can read Steven's article for free, until we post the October issue. If you're not already a subscriber, visit http://www.oracleprofessionalnewsletter.com to find out more about Oracle Professional and sign up for a free trial subscription. --------------------------------------------------------- 3) PL/SQL TIP --------------------------------------------------------- This month's tip comes from Dan Clamage (http://www.clamage.com). This program illustrates a few things: * Building a multidimensional array * Simulating cache hits (and populating misses) * Walking through the multidimensional array -- 2D array to simulate a complex cache DECLARE -- user-defined types -- innermost (changes most rapidly) offset TYPE typ_cnt IS TABLE OF NUMBER INDEX BY emp.ename%TYPE; -- outermost (changes least rapidly) offset TYPE typ_typ_cnt IS TABLE OF typ_cnt INDEX BY emp.job%TYPE; -- explicit cursors CURSOR cur_load IS SELECT e.ename, e.job FROM emp e ; -- composite variables arr typ_typ_cnt; -- scalar variables v_outer emp.job%TYPE; -- outer loop control v_inner emp.ename%TYPE; -- inner loop control BEGIN -- load FOR rec IN cur_load LOOP BEGIN arr (rec.job) (rec.ename) := arr (rec.job) (rec.ename) + 1; -- increment -- DBMS_OUTPUT.PUT_LINE( -- 'hit : (' || rec.job || -- ')(' || rec.ename || ') := ' || -- arr(rec.job)(rec.ename) -- ); EXCEPTION WHEN NO_DATA_FOUND THEN -- miss arr (rec.job) (rec.ename) := 1; -- initialize -- DBMS_OUTPUT.PUT_LINE( -- 'miss: (' || rec.job || -- ')(' || rec.ename || ') := ' || -- arr(rec.job)(rec.ename) -- ); END; END LOOP; DBMS_OUTPUT.put_line ('first=' || arr.FIRST); DBMS_OUTPUT.put_line (' last=' || arr.LAST); DBMS_OUTPUT.put_line ('count=' || arr.COUNT); v_outer := arr.FIRST; WHILE (v_outer IS NOT NULL) LOOP DBMS_OUTPUT.put_line ('(' || v_outer || ').FIRST=' || arr (v_outer).FIRST ); DBMS_OUTPUT.put_line ('(' || v_outer || ').LAST =' || arr (v_outer).LAST); DBMS_OUTPUT.put_line ('(' || v_outer || ').COUNT=' || arr (v_outer).COUNT ); v_inner := arr (v_outer).FIRST; WHILE (v_inner IS NOT NULL) LOOP DBMS_OUTPUT.put_line ( '(' || v_outer || ')(' || v_inner || ')=' || arr (v_outer) (v_inner) ); v_inner := arr (v_outer).NEXT (v_inner); END LOOP; v_outer := arr.NEXT (v_outer); -- next element END LOOP; END; / first=ANALYST last=SALESMAN count=5 (ANALYST).FIRST=FORD (ANALYST).LAST =SCOTT (ANALYST).COUNT=2 (ANALYST)(FORD)=1 (ANALYST)(SCOTT)=1 (CLERK).FIRST=ADAMS (CLERK).LAST =SMITH (CLERK).COUNT=4 (CLERK)(ADAMS)=1 (CLERK)(JAMES)=1 (CLERK)(MILLER)=1 (CLERK)(SMITH)=1 (MANAGER).FIRST=BLAKE (MANAGER).LAST =JONES (MANAGER).COUNT=3 (MANAGER)(BLAKE)=1 (MANAGER)(CLARK)=1 (MANAGER)(JONES)=1 (PRESIDENT).FIRST=KING (PRESIDENT).LAST =KING (PRESIDENT).COUNT=1 (PRESIDENT)(KING)=1 (SALESMAN).FIRST=ALLEN (SALESMAN).LAST =WARD (SALESMAN).COUNT=4 (SALESMAN)(ALLEN)=1 (SALESMAN)(MARTIN)=1 (SALESMAN)(TURNER)=1 (SALESMAN)(WARD)=1 -------------------------------------------------------- 4) ORACLE OPENWORLD 2005 -------------------------------------------------------- Oracle OpenWorld 2005 is quickly approaching! Once again being held at the Moscone Center in San Francisco, September 17–22, Keynote Speakers include Oracle's Larry Ellison, HP's Mark Hurd, Intel's Paul Otellini, and more. And, with the merging of OpenWorld, PeopleSoft Connect, and Retek into a single event, this year's conference has content for Oracle's existing and newly acquired customers, including JD Edwards. Although you've already missed the early registration discount, you can still register and enjoy all the benefits of the conference. For more information, visit: http://www.oracle.com/openworld/sanfrancisco/conference/index.html. -------------------------------------------------------- 5) IN THE NEWS -------------------------------------------------------- The International Oracle Users Group (IOUG) is changing its name to Independent Oracle Users Group. The reason is to better highlight the independent, user-driven viewpoint that the IOUG brings to the Oracle technology and database community. The official launch of the new name and brand, the first such change in the 12-year history of the organization, will occur during Oracle OpenWorld being held in San Francisco September 17-22. * * * * For those of you who are into following the stock market and actually own ORCL stock, you'll be happy to hear that two analysts recently boasted strong ratings for the stock. UBS Investment Research reiterated a "buy 1" rating and set an $18.50 price target for Oracle, while SG Cowen is projecting a 10% to 15% upside on the stock. This is good news for a stock that hasn't climbed above $14 a share more than a couple times in the past 2.5 years. --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- I guess the answer depends on the version of Oracle you're working with. For those who have been working with older versions of Oracle for many years, you would probably answer NO, because prior to Oracle10g, you can create a synonym on objects that don't even exist. For example, on my 8i (ver. 8.1.7.3) database, I can do this: SQL-8i>create synonym t_dummy for dummy_table; Synonym created. SQL-8i>desc t_dummy; ERROR: ORA-00980: synonym translation is no longer valid SQL-8i>select object_type, status 2 from user_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------ ------- SYNONYM VALID 1 row selected. SQL-8i>create table dummy_table (d date); Table created. SQL-8i>select object_type, status 2 from dba_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------ ------- SYNONYM VALID 1 row selected. SQL-8i>drop table dummy_table; Table dropped. SQL-8i>select object_type, status 2 from dba_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------ ------- SYNONYM VALID 1 row selected. Notice that I can create the synonym, then create the table, then drop the table and all throughout, the synonym remains VALID. In Oracle10g, you can still create synonyms on objects that don't exist, but you get a slightly different behavior as you can see here: SQL-10g>create synonym t_dummy for dummy_table; Synonym created. SQL-10g>select object_type, status 2 from user_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------- ------- SYNONYM VALID 1 row selected. SQL-10g>create table dummy_table (d date); Table created. SQL-10g>select object_type, status 2 from user_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------- ------- SYNONYM INVALID 1 row selected. SQL-10g>desc t_dummy Name Null? Type ------------------------------- -------- ---- D DATE SQL-10g>select object_type, status 2 from user_objects where object_name='T_DUMMY'; OBJECT_TYPE STATUS ------------------- ------- SYNONYM VALID 1 row selected. Notice that in Oracle10g, the creation of the table actually invalidated the synonym, so for you Oracle10g users, the answer to this quiz is YES. Now, like with procedures, once you reference the synonym, Oracle recompiles it on the fly for you so that it now becomes VALID. To any casual user, the invalidation of synonyms won't mean much, but it is useful to know that synonyms in Oracle10g can be invalidated if you have automated scripts that look for invalid objects in the database.