TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) 21st Century PL/SQL 3) Oracle Professional 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the function SYS_GUID used for? --------------------------------------------------------- 2) 21st Century PL/SQL --------------------------------------------------------- World renowned PL/SQL expert and regular contributor to our own Oracle Profession newsletter, Steven Feuerstein, has setup a website to provide developers with the opportunity to share ideas and thoughts about what they’d like to see added or changed in the PL/SQL language. This initiative is in conjunction with his upcoming conference this Fall to celebrate the tenth anniversary of his first book (more details to follow later). To join in on this discussion, visit his “idea collector” at the following url: http://www.uh-clem.org/OPP10/site/ --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the March issue of Oracle Professional, Peter Hamilton investigates the advantages to be gained from creating collections of Oracle object types and introduces a generic vector collection, which allows mixed type lists to be created and manipulated within PL/SQL applications. Steven Feuerstein explains what a GUID is, how they can be very useful, and how you can work with them inside Oracle. And Darryl Hurley continues his discussion on Oracle Streams and how data first gets detained via a capture process. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- I’ve recently had the opportunity/need to use analytic functions to solve a business requirement. I needed to calculate various moving averages of a time series of data – more specifically, stock price and volume data. So I began to write a query using the Windowing features of SQL. Here’s the portion of the query I needed to calculate the 50 day and 200 day moving averages of the stock price and volumes: SELECT ... ,avg(volume) OVER ( partition by listing_id order by rownum range 49 preceding ) vol_50 ,avg(volume) OVER ( partition by listing_id order by rownum range 199 preceding ) vol_200 ,avg(price/split) OVER ( partition by listing_id order by rownum range 49 preceding ) avg_50 ,avg(price/split) OVER ( partition by listing_id order by rownum range 199 preceding ) avg_200 ... You’ll see in a moment the full query, but I’d like to explain what’s happening here first. I’m querying the data based on my primary key, which is listing_id and the OVER keyword allows me to create a window of data. The ‘range...preceding’ clause tells me how many rows to look at inside my window of data. Thus I can get an average of all values within my specified windows. Now, this was good and I was able to calculate this data using simple SQL instead of writing a bunch of PL/SQL arrays to manipulate the data. The issue is that the database version I was using is 8i (8.1.7.3) and I needed to run this as part of a larger nightly process. Thus, I had to put this query into my PL/SQL package, but this feature wasn’t supported within 8i PL/SQL. Not a big deal – I just hid the query inside a Dynamic SQL statement and used a REF CURSOR to run it. You can see the full code below to do this. Note that there is a bit of complexity in our specific application to handle stock splits as you can deduce from the query, but it doesn’t really impact the main point of this example: procedure upd_mov_avg is type m_rec_type is record (l_id NUMBER(8) ,l_date date ,mov_avg50 NUMBER(24,8) ,mov_avg200 NUMBER(24,8) ,vol_avg50 NUMBER(24,8) ,vol_avg200 NUMBER(24,8) ); m_rec m_rec_type; type t_spec is ref cursor; cur_mov_avg t_spec; v_sql varchar2(32000) := 'select a.listing_id,a.last,avg_50, avg_200, vol_50, vol_200 from ( select rownum,listing_id, effective_date, price/split price, change_price ,avg(volume) OVER ( partition by listing_id order by rownum range 49 preceding ) vol_50 ,avg(volume) OVER ( partition by listing_id order by rownum range 199 preceding ) vol_200 ,avg(price/split) OVER ( partition by listing_id order by rownum range 49 preceding ) avg_50 ,avg(price/split) OVER ( partition by listing_id order by rownum range 199 preceding ) avg_200 ,first_value(effective_date) OVER ( partition by listing_id order by effective_Date desc range 30 preceding ) last ,split from (select --+ index(d DP_TMP_IDX) d.listing_id, d.effective_date,volume, nvl(price,0)*decode(split_factor,null,1,0,1,split_factor) *decode(spinoff_split_factor,null, 1,0,1,spinoff_split_factor) price , d.change_price ,first_value(d.split_factor) OVER ( partition by d.listing_id order by d.effective_Date desc ) split from daily_price d, wsl w where d.listing_id=w.listing_id and d.effective_date > to_date(:dt)-290 order by d.listing_id, d.effective_date ) ) a, wsl w where a.listing_id=w.listing_id and a.effective_Date=a.last'; begin begin open cur_mov_avg for v_sql using l_date; fetch cur_mov_avg into m_rec; while cur_mov_avg%found loop update temp_tech_analysis set VOLUME_AVG_50DAY = round(m_rec.vol_avg50,8) ,VOLUME_AVG_200DAY = round(m_rec.vol_avg200,8) ,MOVING_AVG_50DAY = round(m_rec.mov_avg50,8) ,MOVING_AVG_200DAY = round(m_rec.mov_avg200,8) where listing_id=m_rec.l_id; fetch cur_mov_avg into m_rec; end loop; close cur_mov_avg; exception when others then dbms_output.put_line(m_rec.l_id||'-'||sqlerrm); end; end upd_mov_avg; So, there you have it! I accomplish my requirement using SQL, but the issue is that my procedure takes about 8 or 9 minutes to run on my development machine. Next month, I’ll tackle trying to make this more efficient, but in the meantime, if any of you out there have any ideas, I’d love to hear them and may even publish it in the next eNewsletter. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Oracle has jumped into the Mergers and Acquisitions market again, just months after they successfully completed an 18 month battle for PeopleSoft Inc. This time, Oracle pursued a long time business partner, Retek Inc., provider of inventory management software. The $631 million offer ($11.25 per share) was slightly higher than previous bids that SAP AG made for the company back in February and early March. Oracle was determined to acquire Retek, stating that they were in acquisition talks last October, but had to slow down in order to focus on the PeopleSoft integration. Oracle also announced recently that they will buy Oblix, a Cupertino, Calif., computer security management company for an undisclosed price. Thomas Kurian, Oracle’s Senior VP, Server Technologies, stated that Oblix's technology complements the identity and access management solutions currently available in Oracle Identity Management and included as part of Oracle Application Server 10g. The price of the acquisition was not disclosed. * * * * Bill gates recently announced the introduction of their new virtual meeting software called Microsoft Office Live Meeting. This web conferencing software is one of 3 products that Microsoft plans to launch in the next few months in the “real-time collaboration” market. Pricing and licensing will be announced in the coming months: * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The SYS_GUID is a function defined in the STANDARD package provided by Oracle. It returns a RAW datatype value and it used in applications to generate globally unique values across the database. As you can see below, when I select this value from DUAL, it is different each time; it is hard to tell at first, but the value in the 12th position changes from a zero in the first result to a 2 in the third result. DBDGAM1_GX>select sys.standard.sys_guid 2 from dual; SYS_GUID --------------------------------------- F1CE67CBB1F008C7E034080020D202A1 DBDGAM1_GX>/ SYS_GUID --------------------------------------- F1CE67CBB1F108C7E034080020D202A1 DBDGAM1_GX>/ SYS_GUID --------------------------------------- F1CE67CBB1F208C7E034080020D202A1 This function is not new to Oracle, as it has been around since Oracle 8i; I tried to find it in an Oracle 8.0.5.1 database with no luck. If you want more information on how to use this function for generating unique key values in your application, then you should read Steven Feuerstein’s article in the March version or Oracle Professional. Visit http://www.oracleprofessionalnewsletter.com for more details.