TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) SQL Tip 4) Oracle Applications Users Group meeting 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the NLS_COMP parameter used for? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the October issue of Oracle Professional, Steven Feuerstein offers an implementation of how to check whether your PL/SQL collections contain a specific value, with emphasis on following best practices in both construction and testing. Andrew Batishchev introduces us to the XML-SQL Utility for PL/SQL, which is a powerful tool for generating XML out of select statements. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) SQL Tip --------------------------------------------------------- This month’s tip comes from my colleague, Thomas Al-Hamad (talhamad@procaseconsulting.com). A new feature introduced in Oracle Database 10g-release 2 is Transparent Data Encryption. Let’s look how it works with an example of how to encrypt 1 or more columns on a table: create table persons ( Person_no number not null, first_name varchar2(30) not null, last_name varchar2(30) not null, SIN varchar2(9) ENCRYPT USING 'AES128', Birth_Date number ENCRYPT USING 'AES128', ) - Data is encrypted using 128 bit - The table key is encrypted with the master key, which is required to obtain the table key. - The master key as well as the table key is required to decrypt a column - The master key is stored outside the database in a location known as a "wallet"—by default in $ORACLE_BASE/admin/$ORACLE_SID/wallet - The database comes with a preconfigured wallet. To set the wallet password, use the command: alter system set encryption key authenticated BY "mypass"; - This command creates the wallet with password "mypass". If wallet is already created, it will change the password to "mypass". Password is case sensitive. You can start using encryption in column definitions during table creation and modification. Select last_name||','||first_name PersName ,SIN ENCRYPT IDENTIFIED BY "mypass" ,Birth_Date ENCRYPT IDENTIFIED BY "mypass", from persons; - Usernames and passwords can be stored in Oracle Wallets to avoid hard-coding usernames / passwords in scripts and other programs. -------------------------------------------------------- 4) Oracle Applications Users Group meeting -------------------------------------------------------- The Oracle Applications Users Group (OAUG) is hosting the Oracle Applications and Technology Symposium (OATS) in Chicago on October 27 at the Hyatt Regency Chicago. OATS-Chicago is a one-day training event allowing attendees to learn more about the Oracle Applications product suite. At the event, more than 20 sessions will focus on Customer Relationship Management (CRM) and the use of technology applications in government, the public sector and higher education. Session topics include integration solutions between PeopleSoft and Oracle E-Business Suite, Release 11i security and developing a successful CRM strategy. For more information, visit: http:// www.oaug.org -------------------------------------------------------- 5) In the News -------------------------------------------------------- Former PeopleSoft Inc. CEO Craig Conway recently joined the board of directors at Salesforce.com Inc., an online software pioneer whose rapidly growing market is being targeted by Oracle Corp. Recall that Oracle bought PeopleSoft last year, so is familiar with Conway’s business knowledge in the CRM industry. Conway is a former Oracle executive and put up a year long battle while at Peoplesoft to thwart Oracle’s bid, only to lose after being fired by Peoplesoft’s Board. With Oracle’s recent purchase of Sieble, they are openly planning to attack Salesforce’s expected $300 million in revenues. * * * * Oracle has announced major upgrades for three modules in the PeopleSoft Enterprise 8.9 suite; the Supply Chain Management, Supplier Relationship Management and Financial Management modules. The upgrades mark the first major changes to the product line in almost two years. It is also the first release of the Enterprise product line since Oracle acquired PeopleSoft back in January of this year. The move to upgrade rather than replace the modules is contrary to earlier speculation that the company would fold the applications together with Oracle’s comparable modules. But, the reason is likely to address customers that were hesitant to upgrade their PeopleSoft applications because they were unsure if the products would be replaced. Perhaps this is part of a larger Strategy by Oracle and more upgrades could be forthcoming in other modules. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- NLS_COMP parameter enables case insensitive queries. Possible values are BINARY (default) which makes queries case sensitive and ANSI, which will make your comparisons based on NLS_SORT. Here’s how you can use it. SQL-10g>select symbol, company_short_name, prev_price 2 from wsl 3 where company_short_name='oracle'; no rows selected Note that my table is storing the company names in mixed case. So if I want this query to work without having to use an UPPER or LOWER function, I do the following: SQL-10g>alter session set nls_sort=binary_ci; Session altered. SQL-10g>alter session set nls_comp=ANSI; Session altered. SQL-10g>select symbol, company_short_name, prev_price 2 from wsl 3 where company_short_name='oracle'; SYMBOL COMPANY_SHORT_NAME PREV_PRICE -------------------- ------------------------------ ---------- ORCL Oracle 12.17 I now see the record. In fact, I could use variations of cases in the word ‘oracle’ and I still get the record I’m looking for, for example: SQL-10g>select symbol, company_short_name, prev_price 2 from wsl 3 where company_short_name='ORACLE'; SYMBOL COMPANY_SHORT_NAME PREV_PRICE -------------------- ------------------------------ ---------- ORCL Oracle 12.17 SQL-10g>select symbol, company_short_name, prev_price 2 from wsl 3 where company_short_name='oRACLE'; SYMBOL COMPANY_SHORT_NAME PREV_PRICE -------------------- ------------------------------ ---------- ORCL Oracle 12.17 SQL-10g>select symbol, company_short_name, prev_price 2 from wsl 3 where company_short_name='oRaClE'; SYMBOL COMPANY_SHORT_NAME PREV_PRICE -------------------- ------------------------------ ---------- ORCL Oracle 12.17 A neat new feature isn’t it? Let me know if you find some interesting uses for this new feature (or any others) and I may include in an upcoming newsletter.