OraNA :: Database Applications Development

Lesson learned today

Jeffrey Kemp — Wed, 10 Mar 2010 21:35:39 PST

Comment out all "DROP TABLE" commands in my scripts.(I accidentally hit F5 when the focus was in the wrong window - which happened to contain a "DROP TABLE / CREATE TABLE" script - my Toad session goes and happily drops the table that I'd been gradually accumulating statistics into for the past 3 days - and no, there's no flashback table in 9i)At least I kept all my scripts - rerunning them all

Everything is a Bit Bucket

oraclenerd — Wed, 10 Mar 2010 10:39:23 PST

By: Michael O'Neill
@oraclenude
oraclenude.crisatunity.com

In response to Chet's frustration over yet another encounter with a database agnostic, I wanted to contribute my first article to the oraclenerd franchise. My thoughts seemed too long for the comment stream.

I ascribe the kernel of thought behind "the database is a bit bucket" primarily to each and every database vendor that ever existed. Every database vendor, in an effort to persuade users of competitive products to adopt their product, has participated willingly in espousing some core aspect of how "same as the other guy" their product is in addition to whatever differentiation pitch they have.

Now, the generally weak-minded and lazy developer (yes, I think the majority of developers are in fact weak-minded and lazy) latches on to the vendor's selective "sameness" claims for professional and personal reasons. (full disclosure: I am both an Oracle DBA and .NET developer)

Professionally, because they are financially invested in writing third-party code not database code. To them, the less they spend learning and understanding the particulars of things like databases, operating systems, networks, human beings, etc. the better. Personally, because there is a dominate thread in the culture of developers to dismiss the database as interesting or meaningful. It is a form of heresy to show affection towards any platform in any specificity.

This is why Java's Big Lie of "write once, run any where" swoons so many. Java's Big Lie is analogous to "the database is a bit bucket" by declaring that even the language of software code should be as absolutely interchangeable as possible - even at the expense of being cost-effective or useful. There is an unquestioned faith that decoupling everything from everything is a good thing. This faith gives us code that is as far from the simplest thing that could work from the first moment writing the code is undertaken. It is a faith I reject. That's why I'm an ORACLENERD.

P.S. I know oraclenude and oraclenerd is confusing. It's supposed to be.

Parallel query distribution methods

tonyhasler — Wed, 10 Mar 2010 08:38:58 PST

A while ago I started to investigate the possibility of using parallel query on some large tables. These large tables were partitioned by date and joined together. It struck me that using subpartitioning on the join column I could get good results.

As I had learned from Christian Antognini’s excellent book this would allow a “Full Partition-wise Joins”. Let me describe how this should have helped in my case.

Let us assume that we are joining two tables T1 and T2 both partitioned by range on a date column D1 and sub-partitioned by hash on the join column J1. It should be possible for a parallel query slave to join one sub-partition from T1 to its corresponding subpartition from T2 without any need to communicate with any other parallel query slaves.

So I wrote a test script:


set autotrace off
DROP TABLE T1;

CREATE TABLE T1(D1 DATE, J1 CHAR(2000), C1 CHAR(2000))
PCTFREE 99 -- To make the table a reasonable size
PARTITION BY RANGE (D1)
   SUBPARTITION BY HASH (J1)
      SUBPARTITIONS 8 (PARTITION P1
                          VALUES LESS THAN (DATE '2010-01-02')
                      ,PARTITION P2
                          VALUES LESS THAN (DATE '2010-01-03')
                      ,PARTITION P3
                          VALUES LESS THAN (DATE '2010-01-04')
                      ,PARTITION P4
                          VALUES LESS THAN (DATE '2010-01-05')
                      ,PARTITION P5
                          VALUES LESS THAN (DATE '2010-01-06'))

PARALLEL(DEGREE 4);

DROP TABLE T2;

CREATE TABLE T2(D1 DATE, J1 CHAR(2000), C1 CHAR(2000))
PCTFREE 99 -- To make the table a reasonable size
PARTITION BY RANGE (D1)
   SUBPARTITION BY HASH (J1)
      SUBPARTITIONS 8 (PARTITION P1
                          VALUES LESS THAN (DATE '2010-01-02')
                      ,PARTITION P2
                          VALUES LESS THAN (DATE '2010-01-03')
                      ,PARTITION P3
                          VALUES LESS THAN (DATE '2010-01-04')
                      ,PARTITION P4
                          VALUES LESS THAN (DATE '2010-01-05')
                      ,PARTITION P5
                          VALUES LESS THAN (DATE '2010-01-06'))

PARALLEL(DEGREE 4);

INSERT INTO T1(D1, J1, C1)
       SELECT   DATE '2010-01-03', ROWNUM, ROWNUM
         FROM   DUAL
   CONNECT BY   LEVEL <= 30000;

INSERT INTO T2(D1, J1, C1)
       SELECT   DATE '2010-01-03', ROWNUM, ROWNUM
         FROM   DUAL
   CONNECT BY   LEVEL  <=30000);

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(USER
                                ,'T1'
                                ,estimate_percent => 100);
   DBMS_STATS.GATHER_TABLE_STATS(USER
                                ,'T2'
                                ,estimate_percent => 100);
END;
/

set autotrace traceonly
set timing on
ALTER SESSION SET EVENTS '10053 trace name context forever';
--
-- First let us try without the hint
--

SELECT   COUNT( * )
  FROM   T1, T2
 WHERE       t1.d1 = DATE '2010-01-03'
         AND t2.d1 = DATE '2010-01-03'
         AND t1.j1 = t2.j1;

--
-- And now hinted
--

SELECT /*+ leading(t1, t2) pq_distribute(t2 none none) */
      COUNT( * )
  FROM   T1, T2
 WHERE       t1.d1 = DATE '2010-01-03'
         AND t2.d1 = DATE '2010-01-03'
         AND t1.j1 = t2.j1;

ALTER SESSION SET EVENTS '10053 trace name context forever';
set autotrace off

Although parallel query was deployed I got a sub-optimal distribution method that not only took longer but used twice as many parallel query slaves as necessary unless I added hints. First the execution plan unhinted:


----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| P
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |  4018 |  2090   (1)| 00:00:11 |       |
|   1 |  SORT AGGREGATE               |          |     1 |  4018 |            |          |       |
|   2 |   PX COORDINATOR              |          |       |       |            |          |       |
|   3 |    PX SEND QC (RANDOM)        | :TQ10001 |     1 |  4018 |            |          |       |
|   4 |     SORT AGGREGATE            |          |     1 |  4018 |            |          |       |
|*  5 |      HASH JOIN                |          | 30000 |   114M|  2090   (1)| 00:00:11 |       |
|   6 |       PX RECEIVE              |          | 30000 |    57M|  1044   (0)| 00:00:06 |       |
|   7 |        PX SEND BROADCAST LOCAL| :TQ10000 | 30000 |    57M|  1044   (0)| 00:00:06 |       |
|   8 |         PX BLOCK ITERATOR     |          | 30000 |    57M|  1044   (0)| 00:00:06 |     1 |
|*  9 |          TABLE ACCESS FULL    | T2       | 30000 |    57M|  1044   (0)| 00:00:06 |    17 |
|  10 |       PX BLOCK ITERATOR       |          | 30000 |    57M|  1044   (0)| 00:00:06 |     1 |
|* 11 |        TABLE ACCESS FULL      | T1       | 30000 |    57M|  1044   (0)| 00:00:06 |    17 |
----------------------------------------------------------------------------------------------------

and now hinted:


----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |  4018 |  2090   (1)| 00:00:11 |       |
|   1 |  SORT AGGREGATE                 |          |     1 |  4018 |            |          |       |
|   2 |   PX COORDINATOR                |          |       |       |            |          |       |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000 |     1 |  4018 |            |          |       |
|   4 |     SORT AGGREGATE              |          |     1 |  4018 |            |          |       |
|   5 |      PX PARTITION HASH ALL      |          | 30000 |   114M|  2090   (1)| 00:00:11 |     1 |
|*  6 |       HASH JOIN                 |          | 30000 |   114M|  2090   (1)| 00:00:11 |       |
|   7 |        PX PARTITION RANGE SINGLE|          | 30000 |    57M|  1044   (0)| 00:00:06 |     3 |
|*  8 |         TABLE ACCESS FULL       | T1       | 30000 |    57M|  1044   (0)| 00:00:06 |    17 |
|   9 |        PX PARTITION RANGE SINGLE|          | 30000 |    57M|  1044   (0)| 00:00:06 |     3 |
|* 10 |         TABLE ACCESS FULL       | T2       | 30000 |    57M|  1044   (0)| 00:00:06 |    17 |
----------------------------------------------------------------------------------------------------

The curious thing was that no matter what distribution mechanism I picked the cost was the same!

I asked Christian about this and his full reply came in this blog. Christian was able to explain why the estimated costs in the execution plan were misleading and further suggested that my problem with the wrong distribution mechanism was due to partition elimination. I changed my query to remove the date predicate but this didn’t help.

So I took Christian’s lead and looked at the 10053 trace file. The following is an extract of the relevant section of the trace file for the un-hinted query:


-- Enumerating distribution methods for #Hash Join:
---- cost NONE = 0.00  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 129  #groups: 1
    Cost per ptn: 0.62  #ptns: 8
    hash_area: 16430 (max=82150)       buildfrag: 926  probefrag: 926  passes: 1
  Hash join: Resc: 7524.65  Resp: 2090.04  [multiMatchCost=0.00]
---- cost(Hash Join) = 2090.04 (w/o dist), 2090.04 (w/ dist)
---- cost VALUE = 16.16
---- cost with slave mapping = 6.07
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.74  #ptns: 4
    hash_area: 16430 (max=82150)       buildfrag: 1851  probefrag: 1851  passes: 1
  Hash join: Resc: 7522.65  Resp: 2089.54  [multiMatchCost=0.00]
---- cost(Hash Join) = 2089.54 (w/o dist), 2095.61 (w/ dist)
---- cost PARTITION-RIGHT = 4.04
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 192  #groups: 1
    Cost per ptn: 0.62  #ptns: 8
    hash_area: 16430 (max=82150)       buildfrag: 926  probefrag: 926  passes: 1
  Hash join: Resc: 7524.65  Resp: 2090.04  [multiMatchCost=0.00]
---- cost(Hash Join) = 2090.04 (w/o dist), 2094.08 (w/ dist)
---- cost PARTITION-LEFT = 4.04
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 160  #groups: 1
    Cost per ptn: 0.62  #ptns: 8
    hash_area: 16430 (max=82150)       buildfrag: 926  probefrag: 926  passes: 1
  Hash join: Resc: 7524.65  Resp: 2090.04  [multiMatchCost=0.00]
---- cost(Hash Join) = 2090.04 (w/o dist), 2094.08 (w/ dist)
---- cost BROADCAST-RIGHT = 31.94
---- cost with slave mapping = 0.00
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 8  #groups: 8
    Cost per ptn: 0.69  #ptns: 4
    hash_area: 16430 (max=82150)       buildfrag: 1851  probefrag: 926  passes: 1
  Hash join: Resc: 7522.46  Resp: 2089.50  [multiMatchCost=0.00]
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T1  Alias: T1
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 16  #groups: 8
    Cost per ptn: 0.67  #ptns: 4
    hash_area: 16430 (max=82150)       buildfrag: 926  probefrag: 1851  passes: 1
  Hash join: Resc: 7522.36  Resp: 2089.47  [multiMatchCost=0.00]
---- cost(Hash Join) = 2089.47 (w/o dist), 2089.47 (w/ dist)
---- cost BROADCAST-LEFT = 31.94
---- cost with slave mapping = 0.00
  Outer table:
    resc: 3759.85  card 30000.00  bytes: 2009  deg: 4  resp: 1044.40
  Inner table: T2  Alias: T2
    resc: 3759.85  card: 30000.00  bytes: 2009  deg: 4  resp: 1044.40
    using dmeth: 16  #groups: 8
    Cost per ptn: 0.67  #ptns: 4
    hash_area: 16430 (max=82150)       buildfrag: 926  probefrag: 1851  passes: 1
  Hash join: Resc: 7522.36  Resp: 2089.47  [multiMatchCost=0.00]
---- cost(Hash Join) = 2089.47 (w/o dist), 2089.47 (w/ dist)
(newjo-save)    [1 0 ]
Final - All Rows Plan:  Best join order: 1
  Cost: 2089.5431  Degree: 4  Card: 30000.0000  Bytes: 120540000
  Resc: 7522.6511  Resc_io: 7518.0000  Resc_cpu: 36316296
  Resp: 2089.5431  Resp_io: 2088.3333  Resc_cpu: 9445741

You can see that the difference between the cost without distribution (2090.04) and that with (2090.04) is zero for the desired plan (shown first) and that the difference for other distribution methods is either also zero or positive. However, the total cost for some of the other distribution methods is lower! This is because the cost without the distribution is not constant. This in turn seems to be because in some distribution methods the number of partitions (subpartitions in our case) has been correctly calculated as 8 and in other cases the number of partitions has been set to 4 – the degree of parallelism! I confirmed that when altering the degree of parallelism this number changed accordingly.

One other oddity: Although the selected distribution mechanism has a cost less than 2090.04 (2089.5431) it is not the lowest possible according to the trace (2089.47). But that is a problem for another day.

It struck me that a good workaround for what seems to be a bug is to ensure that the number of sub-partitions and the degree of parallelism is the same. Let us try it:

ALTER TABLE t1 PARALLEL(DEGREE 8);
ALTER TABLE t2 PARALLEL(DEGREE 8);

Voila! The correct plan is produced unhinted.

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |  4018 |  1045   (1)| 00:00:06 |       |
|   1 |  SORT AGGREGATE                 |          |     1 |  4018 |            |          |       |
|   2 |   PX COORDINATOR                |          |       |       |            |          |       |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000 |     1 |  4018 |            |          |       |
|   4 |     SORT AGGREGATE              |          |     1 |  4018 |            |          |       |
|   5 |      PX PARTITION HASH ALL      |          | 30000 |   114M|  1045   (1)| 00:00:06 |     1 |
|*  6 |       HASH JOIN                 |          | 30000 |   114M|  1045   (1)| 00:00:06 |       |
|   7 |        PX PARTITION RANGE SINGLE|          | 30000 |    57M|   522   (0)| 00:00:03 |     3 |
|*  8 |         TABLE ACCESS FULL       | T1       | 30000 |    57M|   522   (0)| 00:00:03 |    17 |
|   9 |        PX PARTITION RANGE SINGLE|          | 30000 |    57M|   522   (0)| 00:00:03 |     3 |
|* 10 |         TABLE ACCESS FULL       | T2       | 30000 |    57M|   522   (0)| 00:00:03 |    17 |
----------------------------------------------------------------------------------------------------

So here is the tip of the day:

Set the number of partitions or sub-partitions and the degree of parallelism to be the same when performing full partition-wise joins.

I have reproduced the results in this blog entry both on 10.2.04 and 11.1.0.6.

Developers Against Inappropriate Concatenation

Gary Myers — Wed, 10 Mar 2010 03:00:01 PST

Tom Kyte is on record as wanting the abolition of "WHEN OTHERS", "Autonomous Transactions" and "Triggers". I think he's also mention COMMIT in procedures too.

For today's rant, I'm going up against the humble concatenation operator. Yup the double pipe ( || ), or concat if you want to be 'portable'.

Not entirely, of course. I'm not a zealot.I recognize that sometimes you'll need to join a State to a Postcode (or Zip code) when outputting an address.

But they should only be in the top-most SELECT. Not in a subselect, or a predicate. And definitely not if you are inserting the combined value into another column. The first rule about normalisation is you don't talk about normalisation. No, sorry that's Fight Club. But First Normal Form does include Atomicity, which means you shouldn't be gluing fields together. This is a data model, not an Airfix model.

If you do "column_a||column_b", you'll get confused about whether 'abc' was 'ab'||'c' or 'a'||'bc' and be lost.

Even if you are smart and stick some delimiter in there (column_a||'-'||column_b), you've lost your columns. Can't use them for Referential Integrity. Can't gather stats on them. The optimizer is going to get lost. If the original columns were numbers or dates, you've added datatype conversion into the mix. If both the original values were null, you've got nothing but a delimiter in the new field. Yuck.

But mostly, at some time in the future some poor blighter is going to have to come along and break them apart with obscure regular expressions. It may be me, and I won't be happy. And then you'll find out what I can do with a double pipe !

MySQL Standard Group By

maclochlainn — Tue, 09 Mar 2010 22:54:12 PST

Teaching SQL is interesting because folks try syntax that should be broken. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL.

You can select a set of non-aggregated columns with an aggregated column, and exclude one, more than one, or all of the non-aggregated columns from the GROUP BY clause. It’s explained in Chapter 11.12.3 of the MySQL 5.1 Reference. Appropriately, the process is called group by hidden columns.

This query actually runs in a generically configured MySQL instance without an error. It returns a meaningless non-grouped by column value. I wonder if there’s code out there that’s returning unintended data sets because of this behavior.

SELECT   key_one
,        key_two
,        SUM(counter)
FROM     grouping
GROUP BY key_one;

You can prevent the default behavior for the GROUP BY clause by adding the ONLY_FULL_GROUP_BY mode variable to your SQL_MODE system variable. After setting this system variable, a GROUP BY without all non-aggregated columns should raise the following exception:

ERROR 1055 (42000): 'sampledb.grouping.key_two' isn't in GROUP BY

You can add the ONLY_FULL_GROUP_BY mode variable during a session with the following syntax:

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));

Or, you can start the mysqld with the following option:

mysqld --sql_mode="sql_mode1,sql_mode2, ... ,sql_mode(n+1)"

A better alternative, is to add it to the my.cnf configuration file on Linux, or my.ini configuration file on Windows. You can add it to this line, which is done at the end of the line.

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"

If you opt for changing it at the server configuration file, you must stop and restart the mysqld process. You can do that on Windows from the command line, like this on Windows provide the service name is mysql. If you’ve set the Windows service to mysql51, then you need to substitute mysql51 for mysql.

NET STOP mysql
NET START mysql

You can also shut the system down with the following command:

mysqladmin -uroot -p shutdown

Complete Code Sample ↓

Expand this section to see the sample working code.

This builds the GROUPING table, inserts nine rows, and tests it before and after setting the variable in the session. The change reverts after you exit and re-enter the database.

-- Conditionally drop sample table.
SELECT 'DROP TABLE IF EXISTS grouping' AS "Statement";
DROP TABLE IF EXISTS grouping;
 
-- Create sample table.
SELECT 'CREATE TABLE grouping' AS "Statement";
CREATE TABLE grouping
( key_one   CHAR(5)
, key_two   CHAR(5)
, counter   INT UNSIGNED );
 
-- Insert nine rows into the sample table.
SELECT 'INSERT INTO grouping' AS "Statement"
INSERT INTO grouping VALUES
('One','Uno-1',1),('Two','Due-1',2),('Three','Tre-1',3)
('One','Uno-2',1),('Two','Due-2',2),('Three','Tre-2',3)
('One','Uno-3',1),('Two','Due-3',2),('Three','Tre-3',3);
 
-- Query with hidden columns, no enforcement on non-aggregate columns.
SELECT 'SELECT non-aggregates FROM grouping with hidden columns' AS "Statement";
SELECT key_one, key_two, SUM(counter)
FROM   grouping
GROUP BY key_one;
 
-- Check current system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;
 
-- Append the mode to the existing system mode variable.
SELECT 'SET SQL_MODE=(SELECT ...)' AS "Statement";
SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',ONLY_FULL_GROUP_BY'));
 
-- Check modified system mode variable assignment.
SELECT 'SELECT @@sql_mode' AS "Statement";
SELECT @@sql_mode;

APEX: Create and Parse Arrays

oraclenerd — Tue, 09 Mar 2010 22:34:17 PST

It's been awhile since I've been able to work with APEX extensively, so I am rusty.

A question came up today whether we could get multiple values into a single variable (Item in APEX).

Yes we can!

APEX_UTILSNeed some data first:

CREATE TABLE t ( some_text VARCHAR2(10) );

INSERT INTO t ( some_text )
SELECT dbms_random.string( 'a', 10 ) some_text
FROM dual
  CONNECT BY LEVEL <= 5;

CJUSTICE@TESTING>SELECT * FROM t;

SOME_TEXT
----------
thrFXviVWJ
kpfGRRwctv
EVxNrcmBHC
gcBlHaKrLa
irYduOZfkS

I want that table data to be in a single item. TABLE_TO_STRING is your function.

VAR C VARCHAR2(100);

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  SELECT some_text
  BULK COLLECT INTO l_table
  FROM t;

  :c := apex_util.table_to_string( p_table => l_table );
END;
/

PL/SQL procedure successfully completed.


C
-----------------------------------------------------------
thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS

Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer.

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_table := apex_util.string_to_table( p_string => :c );
  
  FOR i IN 1..l_table.COUNT LOOP
    d( 'value ' || i || ':  ' || l_table(i) );
  END LOOP;
END;
/

value 1:  thrFXviVWJ
value 2:  kpfGRRwctv
value 3:  EVxNrcmBHC
value 4:  gcBlHaKrLa
value 5:  irYduOZfkS

PL/SQL procedure successfully completed.

Done.

The "Database is a Bucket" Mentality

oraclenerd — Wed, 10 Mar 2010 17:15:38 PST

Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

Why it is a sad thing when project are in the IT domain

Douwe Pieter van den Bos — Tue, 09 Mar 2010 14:47:56 PST

IT projects are in the IT domain, just like Marketing projects are in the Marketing domain. Makes sense right? No, it doesn’t.

When we take a look at IT projects, we need to understand the fact that the only right of existence is the fact that we support business processes. This means that we need to support and suffice the processes that make the business run, makes them tick. Any IT project is only a way to support a goal, we don’t make the goal, we don’t reach it, we just pass the right shot ahead.

The days that we thought that we understand the actual processes, problems and challenges the business is in, need to lay behind us. Think of it as: ‘we just don’t get it’. This isn’t a bad thing, it’s a good thing. We are good at IT, not at running the entire business. Yet we tend to pretend we are.

IT projects that mainly lay within the IT domain usually go bad, this is because the wrong people try to model and fix business problems and support business challenges. If we take out the responsibility out of the IT domain into the actual business problem owners domain, the right decisions can be made and progress is more eminent.

This strategic choice means we need to get focussed on what we do best: IT. And the business can focus on what they do best: business. This results in a way of working that is much more efficient and pragmatic. We can actually help the business to achieve their goals.

Code Comment WTF? Part 209

oraclenerd — Mon, 08 Mar 2010 22:40:57 PST

Found this in a snippet today:

-- ********************************
-- End of Package Body

END package_pkg ;
/

Seriously? Was that necessary? Could I possibly be under the illusion that it is not the end of the package?

Stop it.

Now.

APEX: LDAP Authentication

oraclenerd — Mon, 08 Mar 2010 23:10:10 PST

I got called into a discussion about an existing APEX application. The custom LDAP functionality wasn't working as they expected.

I knew APEX had an LDAP authentication scheme (and don't know the full history of the project so I can't (won't) comment on why it wasn't used). So I fired up my local sandbox just to see how easy or hard it was. Admittedly, I have always avoided anything to do with LDAP...not sure why (plate is full?). I used this as a guide.

Anyway, it was remarkably easy.

Setup
APEX: 3.2.1
Web Server: Apache (OHS)
Database:

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

First I fired up the web server:

C:\oracle\http\opmn\bin>opmnctl start
opmnctl: opmn started

C:\oracle\http\opmn\bin>opmnctl startproc process-type=HTTP_Server
opmnctl: starting opmn managed processes...

Opened up APEX, and created a new application. For authentication schemes I chose "No Authentication."

After I had created the application, I went into Shared Components --> Authentication Schemes --> Create

Select the default and click Next

Select "Show Login Page and Use LDAP Directory Credentials" and click Next

I've already done this so I'm selecting my current Login page, 11, click Next

Enter your LDAP Host and your DN:

Your DN String should look something like this (from article above):

cn=%LDAP_USER%,l=amer,dc=oracle,dc=com

Make sure you use the %LDAP_USER% after the cn= portion of the string.

Name it ldap_test, click Create Scheme:

You will then be redirected back to the list of Authentication Schemes, ldap_test should now be current

To test it just run your application and login using your LDAP (AD) credentials

Success!

March 1-7 is National Procrastination Week

(author unknown) — Mon, 08 Mar 2010 08:16:55 PST

From An Expert's Guide to Oracle Technology March 1-7 is National Procrastination Week (NPW). Happy NPW! I meant to write about it last week. But I was celebrating it instead. ;-) LewisC

Weird exception handling

Rob van Wijk — Mon, 08 Mar 2010 14:07:33 PST

PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my

February Top 10

oraclenerd — Mon, 08 Mar 2010 10:47:01 PST

As the name implies, it's the Top 10 for this past month. Probably the biggest reason I am doing this is the EBS Install series has become a runaway success. I've never had something become so popular so quickly...which of course tells me I'm no good and John Piwowar is the best ever. :)

John deserves quite a bit of recognition so the more I can provide, the better.

Title	Pageviews	Unique Pageviews
EBS Install Guide - Part I	541	394
Learn Oracle Business Intelligency (OBIEE)	444	337
EBS Install Guide - Part 2	347	262
DBMS_CRYPTO: Example	339	295
APEX: Install 3.2.1 on 11gR2	263	225
EBS Install Guide - Part 3	253	202
OBIEE Posts	243	215
OBIEE: How to Migrate Your rpd	234	183
BULK COLLECT and FORALL	187	166

Forced Agility and the Leap Forward

Douwe Pieter van den Bos — Mon, 08 Mar 2010 08:55:39 PST

Last friday and saturday we, at Whitehorses, had our yearly DeveloperDerby. This beautiful and fun race for developers is for our developers, by our developers. This year we had an actual client case of one of our beloved clients, Ricoh. And this is where it all changed…

In previous years we developed an application for a fictional business problem. This year it was an actual problem and the client was there to help. Two Ricoh executives where available for questions, reviews and other interactions. Because the teams only got a minimum of information beforehand and they needed to deliver something that actually worked within two days, they where forced into agility. A beautiful process and very educational.

Using the moments with the clients, our four development teams got all the information that was needed. All teams where made agile because there was no information to be found, not on paper, not on the web, not by experience. We had a client case that was extremely challenging and the teams needed to show their creativity in order to really grasp and solve this problem, by adding value.

And value was found. Although, like everyone else, the client had a solution in mind, every team came up with a different way to solve the problem. By offering self service in a very accessible and fun way to the end clients of Ricoh, they would save money on sending personnel to their end users. Fun and pretty.

The Ricoh executives where taken trough the process by all our teams, they showed solutions and prototypes, talked about different ways of looking at the problem and got new insights in helping their client base. Managing relations and offering solutions to real live problems. In short: adding value.

In the end, a strange thing happened. Two of four teams where called winners. Both these teams interacted with the client on all occasions the had. Therefor had a lot of insight in what the client moved, what they would think would be sexy and attractive. Talking to them certainly helped understanding the complexity of that what really needed to be done and what was beneath it all.

Agility equals success.

SQL Developer: Install Unit Testing Repository

oraclenerd — Mon, 08 Mar 2010 05:53:56 PST

Get the latest SQL Developer release here.

I'm not a big tools guy, I prefer SQL*Plus to anything else. I especially don't like paying for tools (yes, the database is a tool and costs a lot of money...I do realize the hypocrisy).

After Syme Kutz's presentation at SOUG, I've been looking more closely at SQL Developer. I've been using it (and JDeveloper) since they were both made freely available a few years ago. Mostly for the schema browsing, looking around, importing and exporting data. I do use it (SQL Developer) to write reports that I can share with the Business folks as well.

Syme's presentation was primarily on Unit Testing (which I begged for). First step to using Unit Testing is to install the repository, a set of tables the application uses to build and store tests and their results.

You need to have version 2.1 or greater.

First up, go to Tools --> Unit Test --> Select Current Repository

You'll be prompted to select a connection (i.e. database) to use

Would you like to create one now? Select Yes.

You're then told the the required roles do not exist, select OK.

Confirm running SQL

Running...will take just a few seconds

Success!

That's it. Easy right? Future posts will detail managing users and creating tests.

Wrap a cursor function

maclochlainn — Sun, 07 Mar 2010 19:14:55 PST

A Gauss posted a question on my from last year’s Utah Oracle User’s Group Training Days presentation. If I understood his question correctly, this should help him work with his legacy code. Honestly, as I wrote the example something Bryn Llewellyn said kept banging around in my head, “Just because we can, doesn’t mean we should.” He was speaking of writing poorly engineered code.

Sometimes, we don’t get the opportunity to re-factor existing code. That leaves us with writing wrappers that aren’t pretty or effective. A realization and preface to showing everyone how to accomplish these tasks, and perhaps a watch out warning if you choose this path. I suspect that there may be a better way but I don’t know their code tree.

Here’s the question, as I understand it. They’ve got a library function in PL/SQL that returns a system reference cursor and is principally consumed by an external Java program. This type of architecture is more or less an Adapter OOAD pattern that I wrote about here, over a year and a half ago. The question comes to how to you wrap this approach and make it work in PL/SQL natively too.

The answer depends on some earlier posts because I don’t have a great deal of time to write new examples. It uses a COMMON_LOOKUP table, which is more or less a bunch of small tables grouped into a big table for use in user interaction forms. That way the values don’t get lost in a large code base and are always consistently maintained. These types of tables exist in all major ERP and CRM applications.

The base code for the example is found here, where I discussed how you can effectively use object tables – collections of user-defined object types (Oracle 9iR2 forward if I remember correctly). You can grab the full code at the bottom of the page by clicking the Code Script widget to unfold the code. That code also depends on the Oracle Database 11g PL/SQL Programming downloadable code, which you can download by clicking the link to the zip file location.

Here are the steps to wrap a function that returns a PL/SQL reference cursor so that it can also return a PL/SQL associative array.

Create a package specification to hold all the components that are required to manage the process. Assuming that they may have anchored the system reference cursor to something other than a table like a shared cursor, which is a cumbersome implementation design. (I actually chose to exclude this from the book because it’s a stretch as a good coding practice. At least, it is from my perspective. Also, I couldn’t find an example in the Oracle documentation, which led me to believe they didn’t think it’s a great idea either or I could have glossed over it.) You should note that the PL/SQL RECORD, Associative Array (collection), and the REF CURSOR are defined in this package specification.

-- Create a package to hold the PL/SQL record structure.
CREATE OR REPLACE PACKAGE example IS
 
  -- Force cursors to be read as if empty every time.
  PRAGMA SERIALLY_REUSABLE;
 
  -- Package-level record structure that mimics SQL object type.
  TYPE common_lookup_record IS RECORD
  ( common_lookup_id      NUMBER
  , common_lookup_type    VARCHAR2(30)
  , common_lookup_meaning VARCHAR2(255));
 
  -- Package-level collection that mimics SQL object table.
  TYPE common_lookup_record_table IS TABLE OF common_lookup_record
  INDEX BY PLS_INTEGER;
 
  -- Cursor structure to support a strongly-typed reference cursor.
  CURSOR c IS
    SELECT   common_lookup_id
    ,        common_lookup_type
    ,        common_lookup_meaning
    FROM     common_lookup;
 
  -- Package-level strongly-typed system reference cursor.
  TYPE cursor_lookup IS REF CURSOR RETURN c%ROWTYPE;
 
END;
/

Write a function to return a strongly typed system reference cursor that’s anchored to a cursor defined in the package. This is fairly straightforward when the package specification is done right. You should notice right away that anchoring the original cursor in the package was a horrible practice because you must repeat it all again in the function. In my opinion, you shouldn’t anchor any system reference cursor explicitly to anything other than a table. The cursor could have used the generic weak cursor data type – SYS_REFCURSOR. Doing so, saves all the extra lines required by a potential shared cursor.

CREATE OR REPLACE FUNCTION get_common_lookup_cursor
( table_name VARCHAR2, column_name VARCHAR2)
RETURN example.cursor_lookup IS
 
  -- Define a local variable of a strongly-typed reference cursor.
  lv_cursor EXAMPLE.CURSOR_LOOKUP;
 
BEGIN
 
  -- Open the cursor from a static cursor
  OPEN lv_cursor FOR
    SELECT common_lookup_id
    ,      common_lookup_type
    ,      common_lookup_meaning
    FROM   common_lookup
    WHERE  common_lookup_table = table_name
    AND    common_lookup_column = column_name;
 
  -- Return the cursor handle.
  RETURN lv_cursor;
 
END;
/

Write a wrapper function that takes the reference cursor as a formal parameter and returns an Associative Array. You should note that this can’t be called from a SQL context. You must only use it in a PL/SQL context because system reference cursors are PL/SQL only data types.

CREATE OR REPLACE FUNCTION convert_common_lookup_cursor
( pv_cursor EXAMPLE.CURSOR_LOOKUP) 
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Local PL/SQL-only variable.
  out_record  EXAMPLE.COMMON_LOOKUP_RECORD;
  out_table   EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  LOOP
    FETCH pv_cursor INTO out_record;
    EXIT WHEN pv_cursor%NOTFOUND;
 
    -- Assign it one row at a time to an associative array.
    out_table(counter) := out_record;
 
    -- Increment the counter.
    counter := counter + 1;
 
  END LOOP;
 
  -- Return the record collection.
  RETURN out_table;
 
END;
/

You can test the program in an anonymous block, like the one below. It defines a local Associative Array variable and then assigns the system reference cursor through the wrapper.

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Define a local associative array.
  process_table  EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
  -- Print title block.
  dbms_output.put_line('Converting a SYS_REFCURSOR to TABLE');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Run the dynamic variables through the cursor generating function and then convert it.
  process_table := convert_common_lookup_cursor(get_common_lookup_cursor('ITEM','ITEM_TYPE'));
 
  -- Read the content of the Associative array.  
  FOR i IN 1..process_table.COUNT LOOP
    dbms_output.put('['||process_table(i).common_lookup_id||']');
    dbms_output.put('['||process_table(i).common_lookup_type||']');
    dbms_output.put_line('['||process_table(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

I hope this answers Gauss’s question. While writing it, I could envision another question that might pop-up. How do you convert an object table type to a PL/SQL context. It was an omission not to include it in that original post on object table types. Here’s how you wrap an object table type into a PL/SQL scope collection.

You might have guessed. It’s done with another wrapper function. At least this is the easiest way to convert the SQL data type to a PL/SQL data type that I see. If you’ve another approach, a better way, let us know.

CREATE OR REPLACE FUNCTION get_common_lookup_record_table
( table_name  VARCHAR2
, column_name VARCHAR2 )
RETURN example.common_lookup_record_table IS
 
  -- Declare a local counter variable.
  counter INTEGER := 1;
 
  -- Define a dynamic cursor that takes two formal parameters.
  CURSOR c (table_name_in VARCHAR2, table_column_name_in VARCHAR2) IS
    SELECT   *
    FROM     TABLE(get_common_lookup_object_table(UPPER(table_name_in),UPPER(table_column_name_in)));
 
  -- A local PL/SQL-only collection variable.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
BEGIN
 
  -- Grab the cursor wrapper and return values to a PL/SQL-only record collection.
  FOR i IN c(table_name, column_name) LOOP
    list(counter) := i;
    counter := counter + 1;
  END LOOP;
 
  -- Return the record collection.
  RETURN list;
END get_common_lookup_record_table;
/

You can then test this in an anonymous block, like so:

-- Open the session to see output from PL/SQL blocks.
SET SERVEROUTPUT ON
 
DECLARE
 
  -- Declare a local PL/SQL-only collection and assign the value from the function call.
  list EXAMPLE.COMMON_LOOKUP_RECORD_TABLE;
 
  BEGIN
 
  -- Print title block.
  dbms_output.put_line('Converting a SQL Collection to a PL/SQL Collection');
  dbms_output.put_line('---------------------------------------------------');
 
  -- Assign wrapped SQL collection to a PL/SQL-only collection.
  list := get_common_lookup_record_table('ITEM','ITEM_TYPE');
 
  -- Call the record wrapper function.
  FOR i IN 1..list.COUNT LOOP
    dbms_output.put('['||list(i).common_lookup_id||']');
    dbms_output.put('['||list(i).common_lookup_type||']');
    dbms_output.put_line('['||list(i).common_lookup_meaning||']');
  END LOOP;
 
END;
/

As always, I hope this helps somebody without paying a fee for content.

Twitter Weekly Updates for 2010-03-07

Lewi — Sun, 07 Mar 2010 15:23:00 PST

DB Geek says: Twitter Weekly Updates for 2010-02-28: <!–
google_ad_client = "pub-4372398370642959";
/* 468×60, cr… http://bit.ly/a8JhKJ #
Blogged "On Everquest, Emulators and MySQL" http://it.toolbox.com/trd/46/2/37207/3 #
Blogged "Would you like a free pass to ODTUG Kaelidoscope 2010? How about a hotel upgrade?" http://it.toolbox.com/trd/46/2/37247/3 #
Blogged "The NoSQL That Must Not Be Named!" http://it.toolbox.com/trd/46/2/37249/3 #
Wtf. Ice on the car windows again. In march. I live in the freaking tundra. What's next? Wooly mammoths? #
got an email from AARP. just about crapped myself. turns out it was just spam. I thought I had REALLY overslept. #
MySQL Comments: My current employer doesn't use Erwin so I can't t…: My current employer doesn't use Erwin so I … http://bit.ly/9BsoS6 #

Advert: Queensland User Group

Scott Wesley — Sun, 07 Mar 2010 18:05:32 PST

This is really late notice and really only applicable to Brisbane residents - but I'll be in Brisbane over the next few days and the QLD Oracle User Group has lined me up to talk PL/SQL.

I'll be rabbiting on about Conditional Compilation at Oracle House at 5:30pm, Tuesday 9th March 2010.

Please contact Mark Lancaster if you're interested.

OBIEE: Default Answers Template?

oraclenerd — Sun, 07 Mar 2010 13:59:14 PST

After trying out the lazyweb method of search (aka Twitter) and not getting much help, I resorted to help at the OTN OBIEE Forum. It's not Twitter's fault, I think this problem was a bit too complex to describe in 140 characters.

Here's the post on OTN. I started to get nervous too, I posted on Friday and hadn't had a response...until today. 3 days? Man...that's way too long!

Here's the short of it.

Our reports were coming out funny. Dimension column headings had one style and the Fact table column headings had another.

Using Firebug, I could easily isolate the sections.

On the Dimension column, the definition looked like this:

<th  
  class="ColumnHdg" 
  style="background-color: rgb(231, 231, 247); font-size: 9px; color: rgb(0, 51, 102);" 
  scope="col" 
  dir="ltr">Product Desc
</th>

The Fact table column was defined as:

<th 
  class="ColumnHdg" 
  scope="col" 
  dir="ltr">Basis Amount
</th>

Note the style attribute...that overrides any class settings. Very annoying.

I thought it would be relatively simple to fix. I worked with custom messages before, this had to be similar. So I began to "grep" the messages directory

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"background-color" *.*

Nothing.

How about looking for the name of the class, ColumnHdg?

c:\oraclebi\web\msgdb\>findstr /i /m /s /c:"columnhdg" *.*
messages\criteriatemplates.xml
messages\formattemplates.xml
messages\mktgadminuitemplates.xml
messages\mktgcommontemplates.xml
messages\mktglistformattemplates.xml
messages\mktgsegmenttemplates.xml

So I start with criteriatemplates.xml and find the reference to columnHdg (just now realizing that the case doesn't match...oh well). That was part of the WebMessage kuiColumnFormulaEditor. So I searched for that...

You see where I'm going.

That lead me to the javascript files (of which there are tons). Nothing...not a single thing that could possibly be adding this style attribute.

That's when I mapped the dev server drive to my local computer and opened up WinMerge

I then began to compare every single file in both the msgdb and res (javascript) folders. I would then compare the files that were different to see if that could be the cause. Still...nothing.

I had looked in the webcat before, but couldn't find anything of global significance. I was headed back there though since I had lost hope with custom messages and/or javascript.

Then I got a message from the OTN Forum Administrator...could it be? Looked at the name of the person who answered it first, John Minkjan...sweet!

Looks like you forgot to reset the OOB settings when installing OBIEE:
have a look here to reset them:
http://obiee101.blogspot.com/2009/02/obiee-editing-system-wide-defaults.html

Click through, follow his instructions, bounce the server and voila!

As of this writing, I don't know what OOB stands for; I could make something up, but it probably wouldn't be appropriate for this family site.

Thanks John!

jQGrid Integration Kit for PL/SQL and Apex

Morten Braten — Sun, 07 Mar 2010 03:59:01 PST

I started developing applications back in the good (?) old client/server days. I was fortunate enough to discover Delphi quite early. Even from the start, the lowly 16-bit Delphi version 1 had a kick-ass DBGrid control which allowed you to quickly and easily build data-centric applications. Just write a SQL statement in a TDataSet component, connect it to the grid, and voila! Instant multi-row display and editing out of the box, without any coding.

Fast forward a decade. While I do enjoy building web applications (with PL/SQL and Apex) these days, I've always missed the simplicity of that DBGrid in Delphi. Creating updateable grids with Apex is pretty tedious work (not being entirely satisfied with the built-in updateable tabular forms, I've employed a combination of the apex_item API, page processes for updates and deletes, and custom-made Javascript helpers). It doesn't help that you have to refer to the tabular form arrays by number, rather than by name (g_f01, g_f02, etc.), and that you are restricted to a total of 50 columns per page.

Enter jQGrid, "an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web".

jQGrid can be integrated with any server-side technology, so I decided to integrate it with PL/SQL and Apex.

Features

As of version 1.0, the jQGrid for PL/SQL and Apex has the following features:

Single line of PL/SQL code to render grid

Populate data based on REF CURSOR or SQL text (with or without bind variables). The REF CURSOR support is based on my REF Cursor to JSON utility package.

Define display modes (read only, sortable, editable) and edit types (checkbox, textarea, select list) per column

Store grid configuration in database, or specify settings via code (for read-only grids)

Ajax updates (insert, update, delete) based on either automatic row processing (dynamic SQL) or against your own package API

Multiple grids per page

Integrated logging and instrumentation

Usable without Apex (for stand-alone PL/SQL Web Toolkit applications) or with Apex, optionally integrated with Apex session security

The jQGrid Integration Kit for PL/SQL is free and open source. Download and try it now!.