Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.9 May 2, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle's Strategy for Java 3) SQL Tip: Autonomous Transactions 4) News: Clouds Over Oracle 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you generate a random number in Oracle? --------------------------------------------------------- 2) ORACLE'S STRATEGY FOR JAVA --------------------------------------------------------- Jim Skehill is taking a short break from the Java series. He's been working very hard to roll out his Java applications at GlobeInteractive. I'm sure he'll have more "war stories" to share regarding the use of Java on Oracle when he resumes the series, hopefully in two to four weeks' time. --------------------------------------------------------- 3) SQL TIP: Autonomous Transactions --------------------------------------------------------- Transaction processing is a big topic with any database software, and Oracle is no exception. To set the context, let's define what constitutes a transaction. In Oracle, a transaction is a logical unit of work, made up of one or more SQL (or PL/SQL) statements between commit/rollback points. This implies that a program that contains an explicit commit or rollback statement will commit or rollback all data changes within the current transaction. This may pose a problem for error handling, as illustrated below: CREATE OR REPLACE PROCEDURE p_update_employee IS BEGIN -- Application logic -- ... -- COMMIT; -- EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO error_logs VALUES (20101, sqlerrm); COMMIT; END; / In the p_update_employee procedure, the WHEN OTHERS clause is designed to handle any Oracle error. The intent is to record the error message in an application table -- namely, error_logs. Since we wish to separate the error logging from the application logic which caused the problem, we have to perform an explicit rollback prior to the INSERT statement, and then commit afterwards. This implementation seems a bit awkward because the program really contains two tracks of processing -- the application logic and error logging. An autonomous transaction can be used to address this issue. An autonomous transaction is separate from the initiating transaction, making it possible for the application program to commit/rollback these transactions independently. Autonomous transactions must be declared, using the PRAGMA AUTONOMOUS_TRANSACTION clause in a PL/SQL program. We've included the declaration for an error handling routine below: CREATE OR REPLACE PROCEDURE p_error(i_error IN NUMBER, i_text IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO error_logs (error_code, error_text) VALUES (i_error, i_text); -- COMMIT; -- END; / The p_error procedure now operates independently of the calling routine at runtime. Here's how we can "retool" the p_update_employee routine: CREATE OR REPLACE PROCEDURE p_update_employee IS lx_test EXCEPTION; BEGIN -- Application logic -- ... -- RAISE lx_test; -- COMMIT; -- EXCEPTION WHEN OTHERS THEN ROLLBACK; p_error(20101, sqlerrm); END; / Note that in the EXCEPTION section, the WHEN OTHERS clause issues a rollback to undo application data changes, whereas the p_error routine issues a commit to save the newly inserted error log record. --------------------------------------------------------- 4) NEWS: Clouds Over Oracle --------------------------------------------------------- Oracle's executive vice president of North American sales, George Roberts, said at a technology conference in San Francisco recently that the sales outlook for Oracle remained cloudy. With the scheduled release of Oracle9i - - the latest database version -- in May, Roberts was optimistic that Oracle's revenues should get a boost. But analysts have recently raised concerns that Oracle is losing its hold on the database market, particularly to IBM. For more information, go to: http://dailynews.yahoo.com/h/nm/20010430/tc/tech_oracle_software_dc_1.html --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- The Oracle sequence comes to mind; however, it only generates sequentially-valued unique numbers. Oracle provides a supplied package, dbms_random, which allows generation of random numbers. This package generates a 38-digit random number. To install this supplied package, connect as the SYS database user and run $ORACLE_HOME/rdbms/admin/dbmsrand.sql. The script creates the dbms_random package. After installation, the package must first be initialized. Invoke the "seed" procedure to initialize the random number generator, as shown below: SQL> execute dbms_random.seed(12345678); or SQL> execute dbms_random.seed(1234567890); or SQL> execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')); Note that the seed value should be a large number, with eight to 10 digits. A small value might not return sufficiently random numbers. Next, run the "random" function to obtain the random number. SQL> select dbms_random.random from dual; RANDOM ----------- 267274964 SQL> select dbms_random.random from dual; RANDOM ----------- -600255872 Note that the random number can be a negative value. Apply the ABS function if you wish to obtain a positive value: SQL> select abs(dbms_random.random) from dual; ABS(DBMS_RANDOM.RANDOM) ----------------------- 142159993 SQL> / ABS(DBMS_RANDOM.RANDOM) ----------------------- 1642453775 With this option, however, you're effectively cutting the range of random numbers by half. In any case, you should check for possible collisions. The easiest way is to create a unique key on the column for which you're obtaining a random number through this function. --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2001 http://www.pinnaclepublishing.com All rights reserved.