TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) AppsWorld 2004 4) SQL Tip 5) Industry News 6) ANSWER TO THE POP QUIZ -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Is the CASE statement more efficient than the IF statement in PL/SQL? -------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- You can grant a role to a user who doesn’t exist in the database and have Oracle create the user within a single statement? Here’s the simple statement to perform this: SQL> grant dba to gchan identified by gchan; Grant succeeded. Notice that Oracle tells you the GRANT succeeded without returning anything relating to the user being created, but indeed the user was created as you can see below: SQL> conn gchan/gchan@orcl Connected. -------------------------------------------------------- 3) AppsWorld 2004 --------------------------------------------------------- Don’t forget that Oracle AppsWorld 2004 is being held Jan. 26-29, 2004, at the San Diego Convention Center. Even if you don’t plan on attending personally, you can still catch the action online. For more information, visit: http://www.oracle.com/appsworld/online/index.html. --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- This tip came to me from a colleague, Andrew Okimi, Managing Partner of Procase Consulting Incorporated. He can be reached at aokimi@procaseconsulting.com. You know that you can see someone's encrypted password in dba_users, but you can't do much with it. So, even if you are a DBA you can change someone's password, access the account, etc. but then you can't switch it back to the old value; although I suppose one could directly update the SYS table where it is stored? - but that’s dangerous. Anyhow, there is another syntax to ALTER USER IDENTIFIED BY... that can be used for this purpose: alter user ako identified by values 'E5DCA945A22B4B47'; in this case the keyword VALUES means do NOT encrypt the password, but just take the value as shown and update the user account. ie. query DBA_USERS, get the encrypted password then alter user to some new, known password. access the account, etc etc etc then ALTER USER ... VALUES to reset the password to the original password, and no one is the wiser! This is not too good from the security point of view, but useful nonetheless. I decided to test Andrew’s theory out on my own test database and you can see below that it works as advertised. LEO_GX> alter user ako identified by values 'E5DCA945A22B4B47'; User altered. LEO_GX> alter user ako identified by newako; User altered. LEO_GX> conn ako/newako@leo_invest1; Connected. Here I could have done something within the account now that I’m logged in as the AKO user with the new password I set. Once done, I set it back as Andrew suggests so that the user can access the account with the original password. LEO_GX> alter user ako identified by values 'E5DCA945A22B4B47'; User altered. LEO_GX> conn ako/ako@leo_invest1; Connected. LEO_GX> conn ako/newako@leo_invest1; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. Notice that once you reset the password back with the VALUES clause, you can only access the account with the original password. Pretty cool feature isn’t it? -------------------------------------------------------- 5) Industry News -------------------------------------------------------- Oracle recently reported an 8% gain in second quarter revenues and 15% increase in profit compared to the same period last year. This resulted in earnings per share to increase to $.12, up from $.10 last year. CFO Jeff Henley was enthusiastic as all software categories and geographic regions showed improvements over the prior year’s results. He also stated that the 27% growth in Applications exceeded the growth rates of Oracle’s competitors, such as PeopleSoft, SAP, Siebel and Lawson. For more information, please review the press release at http://www.oracle.com/corporate/press/2635188.html. * * * * IBM fired 3 Korean Executives after a Seoul District Prosecutor indicted 48 individuals and companies on bid-rigging and bribery. These allegations were connected to the sale of computers and services to Korean government agencies and businesses. The 2,500-person Korean unit reported sales last year of $1.37 trillion and is a major market for IBM. For more information, check out the story at: http://biz.yahoo.com/djus/040105/1200000702_2.html. * * * * Oracle stated December 30th that it had enough funding to finance its $7.5 billion takeover of PeopleSoft Inc. Even though Oracle secured a $1.5 billion credit line from Credit Suisse First Boston, ABN Amro Bank and other syndicated lenders, it doesn’t feel it needs the funding since it had reported $8.1 billion in cash as of Nov 30. Oracle’s offer to purchase PeopleSoft has been extended to Feb 13th, although PeopleSoft remains confident that the offer will be rejected on antitrust grounds. -------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- In short, YES. With the introduction of Oracle 9i, you can now use the CASE statement instead of the IF statement within PL/SQL. But is it really worthwhile to switch from the comfort of using IF/THEN/ELSIF? Oracle says that using CASE is more efficient than using IF, so I thought I’d prove it myself. I ran a loop with 50,000 iterations and tested it using an IF statement against both a SELECTOR and SEARCHED CASE expression. Under both implementations, the CASE statement proved to be at least as fast if not faster than using the IF statement. In fact, my example ran 60% faster using of the SELECTOR CASE expression instead of the IF statement. My example using the SEARCHED CASE expression ran in exactly the same time as the IF statement. So, if you are using 9i and have to perform conditional logic in your PL/SQL code, then consider using the CASE expression rather than the IF statement if you want to improve your performance.