TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) SQL Tip 4) IOUG Survey – Win an iPod – act fast! 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the Oracle supplied package, DBMS_PREPROCESSOR used for? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the February issue of Oracle Professional, Steven Feuerstein continues his discussion on Conditional Compilation along with other new features added to the Oracle 10g PL/SQL compiler. Gregory Williams explains how to set up and send e-mail in the database using two Oracle-supplied PL/SQL packages, UTL_SMTP and UTL_MAIL. And, Roy Gomes continues explaining how we can use Oracle Text technology to perform powerful and high quality text searches on documents using SQL. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) SQL Tip --------------------------------------------------------- Have you ever wanted to know the time when a particular row in your table has changed and you don’t have a modify_date column defined on your table? Well, in Oracle 10g, you now have access to a new pseudocolumn called ORA_ROWSCN, which displays the last System Change Number (SCN) of each row. For tables created with the ROWDEPENDENCIES option, this number should be quite accurate; otherwise, the SCN is updated on all rows of the data block. Let’s take a look at how this works on a temporary table I created with NOROWDEPENDENCIDE, which is the default: SAMD2> insert into pin_temp values(1); 1 row created. SAMD2> insert into pin_temp values(2); 1 row created. SAMD2> insert into pin_temp values(3); 1 row created. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 1 2 3 3 rows selected. Notice that you don’t see the ORA_ROWSCN! That’s because I need to COMMIT first before you can see it. So, let’s do that: SAMD2> commit; Commit complete. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 55553055953 1 55553055953 2 55553055953 3 3 rows selected. Now, we see the SCN. Let me modify a record and see what happens to the SCN then: SAMD2> update pin_temp set dummy=4 where dummy=1; 1 row updated. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 4 55553055953 2 55553055953 3 3 rows selected. Oops, I forgot to COMMIT, so need to do that to see the new SCN for the record I modified: SAMD2> commit; Commit complete. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 55553056005 4 55553056005 2 55553056005 3 Notice that the SCN changed for all 3 records. Well, that’s because the SCN in my table is being maintained at the block level, but as mentioned above, you can change that. I won’t go there just yet, as I’m more interested in finding out the TIMESTAMP of when the SCN changed. I do that by calling the new SCN_TO_TIMESTAMP function and pass it the ORA_ROWSCN value as you can see below: SAMD2> select ora_rowscn , scn_to_timestamp(ora_rowscn) chg , dummy from pin_temp; ORA_ROWSCN CHG DUMMY ---------------- ---------------------- ---------- 55553056005 08-FEB 12:30:24.000000 4 55553056005 08-FEB 12:30:24.000000 2 55553056005 08-FEB 12:30:24.000000 3 Now, if I ever have a procedure that needs to read data, perform some logic, then update the data in the database if the row hasn’t changed, I could use this new function SCN_TO_TIMESTAMP to compare the value when I selected the data with the value at the time I’m about to update it. If my table is defaulted to update the SCN at the block level, my code won’t be precise, but I will save some potential processing by not issuing an update of the record if any row in the block has changed. If I need precision, then I’d create my table with ROWDEPENDENCIES and then the SCN will be maintained at the row level. Here’s a quick example to prove that point. SAMD2> create table pin_temp (dummy number(5)) tablespace mf_data1 rowdependencies; Table created. SAMD2> insert into pin_temp values(1); 1 row created. SAMD2> insert into pin_temp values(2); 1 row created. SAMD2_mfund> commit; Commit complete. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 55553057284 1 55553057284 2 2 rows selected. SAMD2> update pin_temp set dummy=4 where dummy=1; 1 row updated. SAMD2> commit; Commit complete. SAMD2> select ora_rowscn, dummy from pin_temp; ORA_ROWSCN DUMMY ---------------- ---------- 55553057292 4 55553057284 2 Notice now that the SCN is now different for each record due to the ROWDEPENDENCIES option set on the table. Hope you like this new feature. -------------------------------------------------------- 4) IOUG Survey – Win and iPod -------------------------------------------------------- The Independent Oracle Users Group (IOUG) is sponsoring a survey called “Technologies for 2006 and Beyond” and respondents have the chance to win an Apple iPod Shuffle. You’ll also receive the Executive Summary of the "Technologies for 2006 and Beyond" survey free in addition to being entered into the draw for one of 3 iPods. I wouldn’t normally inform people of filling out surveys, but this only takes about 5 minutes to complete and is an easy way to have a chance to win an iPod. To complete the survey, visit http://www.mckendrickresearch.com/ioug/newtech.htm. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Oracle has officially completed the purchase of Siebel Systems Inc. valuing the company at $5.85 billion. The former Siebel stockholders electing to receive Oracle common stock will receive a combination of stock and cash. The deal makes Oracle the leader in the CRM software market. Let’s hope this will help them consolidate some of their product lines and create leading edge solutions for clients. * * * * Oracle just won a Copyright infringement lawsuit ruling in Germany. The court found that usedSoft GmbH's practice of selling "used" software licenses to third parties had infringed upon copyrights held by Oracle Corporation subsidiary, Oracle International Corporation. No information was given in the press release as to monetary compensation, but nevertheless, the ruling sets an important precedent in the Industry. * * * * SAP recently announced it was entering the Web-based Software market challenging Salesforce.com and Oracle. SAP executive vice president Peter Graf said they will initially offer an Internet-based service that allows sales staff to manage contacts and accounts and analyze the sales pipeline. It will add marketing support in the second quarter and service-center capabilities in the third quarter. A subscription to the SAP Sales On-Demand Solution will cost $75 a month per user. Salesforce.com's service starts at $65 a user per month, while Siebel CRM On Demand, which is now owned by Oracle Corp., starts at $70 a user per month. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- DBMS_PREPROCESSOR is a package provided by Oracle to help make conditional compilation more accessible and manageable. The package contains several overloaded versions of basically two program units: * GET_POST_PROCESSED_SOURCE Functions that return the post-processed source text. * PRINT_POST_PROCESSED_SOURCE Procedures Prints post-processed source text. You can either pass in the name of an existing stored program unit or pass in a block of PL/SQL code to find out what the pre_processor would do to it. In the result of GET_POST_PROCESSED_SOURCE, all unselected lines of code are replaced with blank lines. For more information about conditional compilation and the DBMS_PREPROCESSOR package, you can read Steven Feuerstein’s article in the February edition or Oracle Professional.