simple and elegant..something new from 10g

One more time It happened..I was browsing within Oracle documentation for something else and found something else (of course, got deviated from intended search :))



Single quotes within a string literal is always a messy job in Oracle because you need to escape every single single quote within your string to make Oracle understand its a part of data.

SQL> conn venkat/venkat@ORCL

Connected.

SQL> select * from v$version;


BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production




here is the tradtional way I had known ever since

SQL> ed
Wrote file afiedt.buf

1 select
2 'it''s sunday night '||
3 to_char(sysdate,'hh:MI AM')||
4 '. I am testing single quoting ("''") in Oracle' as TestOutput
5* from dual
SQL> /

TESTOUTPUT
------------------------------------------------------------------------
it's sunday night 09:15 PM. I am testing single quoting ("'") in Oracle

Now here is the same using 10g's Q quote delimiter

SQL> ed
Wrote file afiedt.buf

1 select q'(it's sunday night )' ||
2 to_char(sysdate,'hh:MI AM')||
3 q'(. I am testing single quoting ("'") in Oracle)' TestOutput
4* from dual

SQL> /

TESTOUTPUT
----------------------------------------------------------------------------
it's sunday night 09:17 PM. I am testing single quoting ("'") in Oracle

No more messy escaping quotes.. Neat!! (and am I the only one to catch up with this 10g feature this late? Better late than never!!:))

Pivot or Unpivot? no big deal from 11g

pivoting and unpivoting resultsets - a kids game from 11g onwards

you dont need to be a sql guru to figure out how to transform rows into columns and vice versa.

here comes PIVOT and UNPIVOT clause of 11g.

I dont think I need to explain more..the below text is straight copy & paste from documentation.

Using PIVOT and UNPIVOT: Examples

The oe.orders table contains information about when an order was placed (order_date), how it was place (order_mode), and the total amount of the order (order_total), as well as other information. The following example shows how to use the PIVOT clause to pivot order_mode values into columns, aggregating order_total data in the process, to get yearly totals by order mode:

CREATE TABLE pivot_table AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));

SELECT * FROM pivot_table ORDER BY year;

     YEAR      STORE   INTERNET
---------- ---------- ----------
     1990    61655.7
     1996     5546.6
     1997        310
     1998   309929.8   100056.6
     1999  1274078.8  1271019.5
     2000   252108.3   393349.4

6 rows selected.

The UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the INCLUDE NULLS clause.

SELECT * FROM pivot_table
 UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1996 direct       5546.6
     1997 direct          310
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4
9 rows selected.

SELECT * FROM pivot_table
 UNPIVOT INCLUDE NULLS
   (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
 ORDER BY year, order_mode;

     YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
     1990 direct      61655.7
     1990 online
     1996 direct       5546.6
     1996 online
     1997 direct          310
     1997 online
     1998 direct     309929.8
     1998 online     100056.6
     1999 direct    1274078.8
     1999 online    1271019.5
     2000 direct     252108.3
     2000 online     393349.4

12 rows selected.

thats neat!!

11g: One more to remember - CASE matters!!

until now , I always had the luxury of just remembering just my passwords and not its case. Because I think Oracle always took supplied password and apply some hashing function together with userid to generate a new passcode which is what gets stored under password column of dba_users or all_users.

Starting from 11g, oracle passwords are going to be case-sensitive. So its going to take a while for people like me to remember "OracleIsFun" is different than "oracleisFUN".

Another nice addition to Oracle 11g is new view called users_with_defpwd (or something similar)..this view list all the users whose password is supplied default one (scott/tiger). Now, I can already see - this is going to be extremely easy for database auditors (SOX ,HPAA whatever else ) to list the such user accounts ..I remember creating a script for 9i before, where I have to built my own array of known userid+password combinations and then trying to connect for every single known combination.

Oracle is really thinking ahead.

sequence of errors

I encountered this at one of my client's .. I usually get to see my inbox full of unread messages running for atleast 3 or 4 pages every time I get to check my account set up with this client because I usually get to read them only once in a week and one day I happened to see some mails with some ORA- error (4068 error to be exact) and that caught my attention.

what was happening was a few packages in Production were bombing with ORA-4068 error and there were back and forth emails between development and dba team on how to resolve it. The development team thought it was due to packages in INVALID state but they didnt know how does a package suddenly change the status to INVALID, they simply put the ball on DBA's court asking them to ensure & maintain the production objects with VALID status.
DBA team responded back they had to do DDL changes but replied back saying it may be due to partition maintenance operations which was all coded by DEV team..Eventually they were about to agree on creating a database job to check invalid objects every 15 minutes and automatically compile them..

what surprised me at the time I read all this long email thread was that no one cared to really dig in what the real cause of the problem but was ready to throw in suggestions to whatever problems they thought was creating the issue.




ORA-04068: existing state of packages has been discarded
is pretty simple and straight forward..clearly states your existing state of package has been discarded.. if you change a packag with global variables and some sessions has already stored the prev code, they had to flushed and reloaded because you changed the source code. Just simple as that..
I developed a test case and proved to DBAs and Development team that this can still happen if the status of packages are perfectly valid ..and actually the error was happening because the production releases were pushed in without bringing down client (web clients with connection pooling)..simply bringing down the clients before pushing new PLSQL code & then bringing up new connections would fix the issue without any single line of coding effort.

I didn't get any questions back from any team ..neither did I get any feedback on if they accepted my theory..but it was interesting that two teams were ready to solve an inexistent problem :)