OraNA :: Oracle News Aggregator

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.

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

-- 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;

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

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

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.

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.

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

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...

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

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

END package_pkg ;
/

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.

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.

1. 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;
/

1. 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;
/

1. 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;
/

1. 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.

• 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 #

Powered by Twitter Tools

<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>

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

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

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

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

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".

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!.