TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 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 a CURSOR expression? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... Oracle OpenWorld is taking place in San Francisco from December 5 to the 9th. This year, Oracle is combining its business applications conference (Oracle AppsWorld) and technology conference (OracleWorld) into 1 single event. You can choose from over 400 technical sessions and hands-on labs as well as attend Oracle University lesions and cram sessions. If you register before Dec 3rd, you’ll save $300 off the full registration price. For more information, visit online at: http://www.oracle.com/openworld/index.html --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, Oracle expert Steven Feuerstein shows us why developers should be taking more advantage of collections in their pl/sql programming. John Hetzel introduces us to the Multi-table insert feature and shows us how and where to use this great new feature. And, Anunaya Shrivastava describes techniques for working with alphanumeric sequences. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- In the last eNL, I introduced you to the fact that in Oracle 9i, a DBA can change the password of a user with the SYSDBA role? Now, let’s take a look at an example of this: %> sqlplus SQL*Plus: Release 9.0.1.0.0 - Production on Sat Mar 30 01:05:45 2002 (c) Copyright 2001 Oracle Corporation. All rights reserved. Enter user-name: / as sysdba Connected to: Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option JServer Release 9.0.1.0.0 - Production SQL> CREATE USER PIN_SYSDBA IDENTIFIED BY P1; User created. SQL> GRANT SYSDBA TO PIN_SYSDBA; Grant succeeded. SQL> CREATE USER PIN_DBA IDENTIFIED BY P2; User created. SQL> GRANT DBA TO PIN_DBA; Grant succeeded. SQL> CONNECT PIN_DBA/P2 Connected. SQL> ALTER USER PIN_SYSDBA IDENTIFIED BY P3; User altered. SQL> CONNECT PIN_SYSDBA/P3 AS SYSDBA Connected. SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> So, as you can see, a DBA can change the password of a SYSDBA and use the changed password to gain access to a higher level of privilege. This is true as long as the init.ora parameter, remote_login_passwordfile is equal to “EXCLUSIVE”. If it is set to “NONE”, then you aren’t allowed, which is probably what a security- minded DBA would set it to. -------------------------------------------------------- 5) In the News -------------------------------------------------------- As expected, the European Union Commission has cleared the way for Oracle’s takeover bid for PeopleSoft Inc. to continue. They stated that there was insufficient evidence to stop the deal, which follows suit with the U.S. Department of Justice’s decision last month. * * * * Oracle has formally extended the offer to purchase PeopleSoft Inc. to November 19, 2004 and has also increased the price to $24 per share. This represents an approximate value of $8.8 Billion for PeopleSoft Inc. Although Oracle states this is their final offer, you have to wonder with all of the dealings that have gone in since this ordeal began over a year ago. * * * * Some Industry experts in the U.S. were expecting that the outsourcing of IT services to India would play a role in the U.S. election. Indian business leaders were quick to point out that outsourcing play little, if no role in the election as Bush appeared to take the presidency for another term despite many job losses due to outsourcing. India estimates that two-thirds of their $12.5-billion (US) each year comes from U.S. companies farming out software development and other IT services to save costs. To read more about this, visit: http://www.globetechnology.com/servlet/story/RTGAM.20041103.gtindianov3 /BNStory/Technology/ * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- A CURSOR expression returns a nested cursor. It is equivalent to a PL/SQL REF CURSOR and thus can be passed as a REF CURSOR parameter to a function. Below is an example of what you see when using a cursor expression in SQL. I have 2 tables in my example - asset_class_category(parent) and asset_class(chile) – a simple parent/child relationship. In my example, I want to get a list of each asset_class_category record, with corresponding child records from asset_class, but I want to restrict the output based on a column in asset_class. Let’s see what I get: SQL> select category, create_date, cursor(select description 2 from asset_class ac 3 where ac.category=acc.category 4 and ac.globe_class='CDN EQ') class_description 5 from asset_class_category acc; CATEGORY CREATE_DA CLASS_DESCRIPTION -------- --------- -------------------- GIS 20-MAY-97 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 DESCRIPTION ------------------------------------------------------------ Canadian Equity Dividend Sector Equity Precious Metals/Resources Canadian Small-Mid Cap IFSC 12-APR-99 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 no rows selected CATEGORY CREATE_DA CLASS_DESCRIPTION -------- --------- -------------------- POOLED 09-OCT-02 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 no rows selected CATEGORY CREATE_DA CLASS_DESCRIPTION -------- --------- -------------------- ULIFE 30-NOV-02 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 no rows selected CATEGORY CREATE_DA CLASS_DESCRIPTION -------- --------- -------------------- USFUND 08-JUN-04 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 DESCRIPTION ------------------------------------------------------------ Domestic Equity Multi-Cap Equity Domestic Equity (Russell 2500 Growth) Domestic Equity (Russell Mid Cap) Domestic Equity (Russell 2000 Growth) Domestic Equity (Russell 1000 Value) Domestic Equity (Russell 2000) Domestic Equity (Russell 1000 Growth) Domestic Equity (Russell 2000 Value) Domestic Equity (Russell Mid Cap Value) Domestic Equity (Lehman Global US TIPS) Domestic Equity (Russell Mid Cap Growth) Domestic Equity (Lehman Brothers Gov/Credit Bond) Domestic Equity (Citigroup U.S. Domestic 3-Month T-Bill) 14 rows selected. USS&P 06-JUL-04 CURSOR STATEMENT : 3 CURSOR STATEMENT : 3 no rows selected 6 rows selected. So, you can see that I had 6 records in my parent table, and for each corresponding record in the child table, the results were displayed as a REF CURSOR. Note that you can only try this on an Oracle 9i compatible version of SQL*Plus otherwise you get: ERROR: ORA-00932: inconsistent datatypes: expected NUMBER got CURSER Now the advantage of this is that you can create a function in the database to accept the REF CURSOR and call that function to display out your results. In the next eNewsletter, I’ll show you how to do that.