Mike's PL/SQL Notes

Performing a JOIN on groups of values using nested tables

2008-07-08T10:35:00.000-07:00

-- Join on a nested table example:

-- this example takes 2 tables (c & d). On each table it does

-- a group by, and the details of that group are collected into a nested table.

-- tables c & d are then joined on that nested table

-- I probably would never do this, but I do think it is interesting.

--DROP TABLE D CASCADE CONSTRAINTS;

CREATE TABLE d (animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO d (animal, grp) VALUES ('cat', 4);

INSERT INTO d (animal, grp) VALUES ('cat', 7);

INSERT INTO d (animal, grp) VALUES ('toad', 7);

INSERT INTO d (animal, grp) VALUES ('frog', 4);

INSERT INTO d (animal, grp) VALUES ('frog', 7);

COMMIT ;

-- DROP TABLE c CASCADE CONSTRAINTS;

CREATE TABLE c( animal VARCHAR2(5 BYTE), grp NUMBER(6));

SET DEFINE OFF;

INSERT INTO c (animal, grp) VALUES ('cat', 1);

INSERT INTO c (animal, grp) VALUES ('frog', 1);

INSERT INTO c (animal, grp) VALUES ('frog', 66);

INSERT INTO c (animal, grp) VALUES ('cat', 33);

INSERT INTO c (animal, grp) VALUES ('cat', 44);

INSERT INTO c (animal, grp) VALUES ('cat', 66);

INSERT INTO c (animal, grp) VALUES ('frog', 44);

INSERT INTO c (animal, grp) VALUES ('toad', 44);

COMMIT ;

SELECT grp, animal FROM c ORDER BY grp, animal;

SELECT grp, animal FROM d ORDER BY grp, animal;

CREATE OR REPLACE TYPE string20_table IS TABLE OF VARCHAR2(20);

DECLARE

mystr VARCHAR2(80);

BEGIN

FOR cur1 IN

(SELECT *

FROM (SELECT grp grp1, CAST(COLLECT(animal) AS string20_table) tab1

FROM c GROUP BY grp)

JOIN

(SELECT grp grp2, CAST(COLLECT(animal) AS string20_table) tab2

FROM d GROUP BY grp)

ON tab1 = tab2

)

LOOP

DBMS_OUTPUT.put_line( TO_CHAR(cur1.grp1)

|| '='

|| TO_CHAR(cur1.grp2));

END LOOP;

END;

-- final output

-- 1=4

-- 44=7

-- 66=4

SELECT as many rows as indicated by column value

2007-10-23T10:15:00.000-07:00

This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.

CREATE TABLE S
(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;

Insert into S
(RNUM, TPART, SQ)
Values
(101, 'TEST_PART1', 1);
Insert into S
(RNUM, TPART, SQ)
Values
(102, 'TEST_PART2', 2);
Insert into S
(RNUM, TPART, SQ)
Values
(103, 'TEST_PART3', 3);
Insert into S
(RNUM, TPART, SQ)
Values
(105, 'TEST_PART5', 5);
COMMIT;

SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);

ORA-06502: PL/SQL: numeric or value error: host bind array too small

2007-10-12T14:12:00.000-07:00

ORA-06502: PL/SQL: numeric or value error: host bind array too small

With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.

So, it's probably not a good idea to drop those string chunking procedures until you are sure that everybody is off of the older clients.

2007-10-06T16:40:00.000-07:00

Incremental elimination using predicate negation

Application requirements sometimes include the need to match consumers with products. A common solution is to create a consumer table having a varchar2 column which will contain a string of predicates or a where clause. The consumer’s where clause is tested against the attributes of a product. The process of matching might be implemented in any of the following modes:

Tell me if this consumer matches this product
Tell me all of the consumers that match this product
Tell me all of the products that match this consumer

In any case, if the where clause evaluation results in TRUE, the matching row will be returned in the result set. In other words the steps are;

execute a SELECT statement containing the consumer’s where clause
determine FOUND or NOT FOUND
and if FOUND, consider it to be a match

This approach works fine in some situations, but what if the product is built gradually over a period of time? For example, assume we have an adoption agency for children. The child is the product. The Prakesh family ( the consumer ) is waiting to receive a child, but they have a set of ‘child matching’ requirements which we have expressed as a where clause. It looks like this:

(ths = 'N' and hair_color = 'blond' and autosomal_recessive = 'No')

Now babyX is born bald as a billiard ball, so we don’t yet know the hair_color. The results of the THS test will be known in one week and the autosomal_recessive test in 2 weeks. The Prakesh family thinks that babyX might be right for them but they won’t know for sure until all of the child data is available. A few days passes and babyX has enough fuzz on her head to see that she has blond hair. So, we have one piece of data, and this one piece of data has the potential to eliminate the Prakesh family as parents of babyX. In the database we have a record for babyX that looks like

Baby_ID	THS	Hair Color	Autosomal Recessive
123		blond

We want to see if our new data value for hair color eliminates the Prakesh family as candidates for babyX, so we build our SELECT statement like this:

SELECT 'matched'
FROM   TbaBy
WHERE Baby_Id = 123
       AND ths = 'N'
       AND Hair_Color = 'blond'
       AND AutoSoMal_Recessive = 'No';

Of course, the result will be no match. The problem is clear; if we are going to apply the where_clause before all of the data is gathered, then we need to allow for NULL values in our where clause. We-code the where_clause so that it looks like this:

SELECT 'matched'
FROM   TbaBy
WHERE (ths = 'N'
         OR ths IS NULL )
       AND (Hair_Color = 'blond'
             OR Hair_Color IS NULL )
       AND (AutoSoMal_Recessive = 'No'
             OR AutoSoMal_Recessive IS NULL );

Now, given this new where clause, as soon as a new data item is written to the baby record, we can run the where clause each time we receive more data on the baby .and we will be able to tell the Prakesh family the moment that any one of their criteria is not met.

In essence, by adding the “or is null” to the where_clause, we have changed the question from

Does this baby match these parents?
to
Does this baby NOT match these parents?

Adding the IS NULL test is simple as long as either the left-hand side predicate or the right-hand side predicate is a constant. It becomes a little trickier when both left-hand and right-hand sides are both variables.

Using the predicate negation technique

Predicate negation reverses the logic of the where clause so that any row that would not be returned is now returned and visa versa.

CREATE TABLE PRODUCT
(
PRODID    NUMBER(5),
PRODSIZE INTEGER,
COLOR     VARCHAR2(10 BYTE),
FABRIC    VARCHAR2(10 BYTE),
CONSTRAINT PRODUCT_PK
PRIMARY KEY
(PRODID)
);

SET DEFINE OFF;
INSERT INTO PRODUCT   (PRODID, PRODSIZE, COLOR, FABRIC)
VALUES   (10, 14, NULL, 'cotton');
INSERT INTO PRODUCT   (PRODID, PRODSIZE, COLOR, FABRIC)
VALUES   (20, 14, 'red', 'wool');
INSERT INTO PRODUCT   (PRODID, PRODSIZE, COLOR, FABRIC)
VALUES   (30, 15, 'white', 'cotton');
COMMIT;

-- the following selects assumes that all attribute
-- values have been provided ( no nulls ). A NULL value in any of the criteria
-- will result in no_data_found, hence no match.
SELECT *
FROM   Product
WHERE ProdSize = 14
       AND ((Color = 'red'
             AND Fabric = 'cotton')
             OR (Fabric = 'wool'));

-- by reversing the logic of the where clause and reversing
-- the interpretation of FOUND vs NOT FOUND, NULL values will not
-- be considered a reason for rejecting a match. If a record is returned
-- it means the product does not match the criteria.
SELECT ProdId
FROM   Product
WHERE (ProdSize != 14
         OR ((Color != 'red'
               OR Fabric != 'cotton')
             AND (Fabric != 'wool')));

-- to prove that the result is the inverse of the prior result
-- we anti join to the complete table.
SELECT *
FROM   Product
WHERE ProdId NOT IN (SELECT ProdId
                      FROM   Product
                      WHERE (ProdSize != 14
                               OR ((Color != 'red'
                                     OR Fabric != 'cotton')
                                   AND (Fabric != 'wool'))));

Predicate negation can be accomplished by changing

OR into AND
= into !=
> into <=
< into >=
IN into NOT IN
BETWEEN into NOT BETWEEN
AND into OR

To learn more about the formal rules of predicate negation see

De_Morgan’s theorem

2007-10-06T13:22:00.000-07:00

This code will generate a list of integers from 1 to 50
10g and above

SELECT mynum
FROM (SELECT 1 mynum FROM dual)
MODEL
DIMENSION BY (mynum )
MEASURES ('mynote' AS note)
RULES (note[FOR mynum FROM 1 TO 50 INCREMENT 1] = '' )
ORDER BY mynum;

This code will also generate a list of integers from 1 to 50;
8i and above

SELECT LEVEL num
FROM   Dual
CONNECT BY LEVEL <= 50;
If you want to use either of these examples, make sure you test the performance. The MODEL example may be quite costly when generating very long sequences. The only advantage to the MODEL example is that it is ANSII compliant. The following example illustrates that point.

Connected.
SQL> alter session set FLAGGER = FULL;
Session altered.
SQL> SELECT mynum
2            FROM (SELECT 1 mynum FROM dual)
3           MODEL
4       DIMENSION BY (mynum )
5        MEASURES ('mynote' as note)
6           RULES (note[FOR mynum FROM 1 to 5 INCREMENT 1] = '' )
7           ORDER BY mynum;
MYNUM
———-
1
2
3
4
5
SQL> SELECT LEVEL num
2 FROM   Dual
3 CONNECT BY LEVEL <= 5;
CONNECT BY LEVEL <= 5
*
ERROR at line 3:
ORA-00097: use of Oracle SQL feature not in SQL92 Full Level
ORA-06550: line 4, column 1:
PLS-01425: Connect-by clause

2007-10-06T11:53:00.001-07:00

Powermultiset to find combinations

Part 1 of 2

This article consists of a statement of the problem to be solved, and then walks through the steps of solving it. There could be mistakes. There could be a better way to do this. Hopefully the reader can find some benefit regardless of the imperfections.

Statement of the Problem
Each department has it’s own products. Each product has a price.
The SQL query in this example answers the question:
Given that a customer can purchase, from a single department, any number of products, but not more than one of a specific product, what are the possible dollar amounts that the customer could potentially pay.

For example, Department 1 has 3 products:

product 1   cost $3
product 2   cost $5
product 3   cost $2

A customer of Department 1 could purchase one of the following 7 combinations.

1. product 1 - cost = $3
2. product 2 - cost = $5
3. product 3 - cost = $2
4. product 1 and product 2 - total cost = $8
5. product 1 and product 3 - total cost = $5
6. product 2 and product 3 - total cost = $7
7. product 1 and product 2 and product 3 - total cost = $10

So, given this data the answer would be (2,3,5,8,7 and 10)

CREATE TABLE DEMO
(
DEPARTMENT NUMBER(3)                         NOT NULL,
PRODUCT     NUMBER                            NOT NULL,
UNIT_PRICE NUMBER(8,2)                       NOT NULL
);

SET DEFINE OFF;
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (1, 10, 1200);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (2, 22, 99);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (2, 23, 50);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (2, 24, 101);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (2, 25, 150);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (3, 30, 1999);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (4, 41, 500);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (4, 45, 250);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (5, 56, 40);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (6, 63, 250);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (7, 77, 1200);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (7, 73, 50);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (8, 81, 40);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (9, 91, 200);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (9, 94, 250);
Insert into DEMO   (DEPARTMENT, PRODUCT, UNIT_PRICE) Values   (10, 77, 500);
COMMIT;

CREATE OR REPLACE type         demo_prod_typ as object(
department integer,
product integer
)
/

CREATE OR REPLACE type demo_prod_tab_typ as table of demo_prod_typ
/

In the HTML tables in this example were generated from SQLPlus using the set mark html on command. I used SQLPLUS because it has some useful pretty printing features. Specifically, when the output column is a nested table, SQLPlus will display the entire structure as a string. When you encounter examples of output data that look like this “DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10))”, you should understand that this is not the actual data. Instead, this is SQLPlus’s way of telling you that - the content of this column is a nested table named DEMO_PRD_TAB and that table contains a single object named DEMO_PROD_TYP and the object contains 2 variables which have the values of 1 and 10. If you look at the HTML table’s header for this column it will say “OID(DEPARTMENT, PRODUCT). This tells you that 1 represents a department and 10 represents a product.
The final solution is shown at the bottom of this article. You might want to take a look so that you can understand the context of the sub queries as I work my way from the inner most select to the outer select.

SELECT department, CAST
           ( COLLECT ( demo_prod_typ ( department
                                      , product ) ) AS demo_prod_tab_typ )
                                                           OID
FROM demo
GROUP BY department

The innermost sub-query is shown above. In order to get the desired final result set, we will need to use the powermultiset function. This function operates on a table of objects, so we know that we first need to create these collections. We will have one collection per each grouping of department. COLLECT is a aggregating function just like MAX and MIN in so much as they produce a single result per group. The COLLECT will result in a single nested table of demo_product_typ per department. However, the table that contains these objects will be system defined. The name of the table will be something like SYSTPL16fhcAkBaDgQAEKXAp4Gg. The powermultiset function will not be able use these ’system named’ nested tables unless we first CAST them to a type that powermultiset “knows” about. The result of this SELECT is shown below.

DEPAR TMENT	OID(DEPARTMENT, PRODUCT)
1	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10))
2	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2,22),DEMO_PROD_TYP(2,24),DEMO_PROD_TYP(2,25),DEMO_PROD_TYP(2,23))
3	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(3, 30))
4	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45))
5	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(5, 56))
6	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(6, 63))
7	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73))
8	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(8, 81))
9	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94))
10	DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(10, 77))

10 rows selected.

SELECT VALUE(t2) xx, t3.department department_t3, t3.product product_t3
FROM (SELECT   department,
              CAST
                (COLLECT(demo_prod_typ(department, product)) AS demo_prod_tab_typ)
                                                                        OID
            FROM demo
        GROUP BY department) t1,
       TABLE(POWERMULTISET(t1.OID)) t2;

Now, having constructed and identified our nested tables, we are ready for POWERMULTISET to do it’s magic. But first, let’s talk about the JOIN in the above SELECT statement. You can see that the outer select is joining 3 tables; the first table is defined by a sub query and named t1. The second and third tables are the results of a TABLE functions and named t2 and t3 respectively. Unless you are already familiar with this type of join, you might falsely conclude that this is a 3 way Cartesian join because no join criteria have been defined. Instead, this is called a left correlated join and this specific join does goes like this:
For each row returned in the sub query known as t1, pass this row’s demo_prod_tab_typ (OID) to the POWERMULTISET function. Then, join each row from the current t1 to each row returned by the POWERMULTISET function. Then, for each row that results from the T1,T2 join, join to that the VALUE of t2. The value of t2 will always be a single row, and the output shown in the HTML table below might tell you why we are joining row.

XX(DEPARTMENT, PRODUCT)	DEPART MENT_T3	PRODU CT_T3
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(1, 10))	1	10
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 23))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2, 23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2,22), DEMO_PROD_TYP(2,24),DEMO_PROD_TYP(2,25),DEMO_PROD_TYP(2,23))	2	22
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23))	2	24
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23))	2	25
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24), DEMO_PROD_TYP(2, 25), DEMO_PROD_TYP(2,23))	2	23
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(3, 30))	3	30
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41))	4	41
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 45))	4	45
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45))	4	41
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(4, 41), DEMO_PROD_TYP(4, 45))	4	45
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(5, 56))	5	56
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(6, 63))	6	63
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77))	7	77
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 73))	7	73
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73))	7	77
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(7, 77), DEMO_PROD_TYP(7, 73))	7	73
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(8, 81))	8	81
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91))	9	91
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 94))	9	94
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94))	9	91
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(9, 91), DEMO_PROD_TYP(9, 94))	9	94
DEMO_PROD_TAB_TYP(DEMO_PROD_TYP(10, 77))	10	77

50 rows selected.
continued in part 2 posting

2007-10-06T11:53:00.000-07:00

Powermultiset to find combinations

Part 2 of 2

If you haven’t figured it out, the reason for the xx column is to identify the set, which was produced by the powermultiset function. Starting from row 1 of the html table above, read it like this:

product 10 of department 1 is a member of the set (DEMO_PROD_TYP(1, 10))
product 24 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 24))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))
product 24 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))
product 25 of department 2 is a member of the set (DEMO_PROD_TYP(2, 25))
product 22 of department 2 is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 25))

and so on
Unfortunately, we can not use GROUP BY on a nested table such as DEMO_PROD_TAB_TYP. Also, we have the groupings for the products, but we don’t have the unit_price of the products. We’ll take care of this problem in the next outward SELECT.
Getting the unit_price is no problem because we have the department and product from the lower level SELECT. In the below example, “SELECT unit_price … ” does the job.
As for the nested table column, we will change it into a string so that we can perform GROUP BY on it. I don’t want to address the XML functions, so I’ll let the code speak for itself. The result of the SELECT is shown in the HTML table below the code

SELECT DISTINCT department_t3, SUM(price) possible
           FROM (SELECT department_t3, product_t3,
                        (SELECT unit_price
                           FROM demo
                          WHERE department = department_t3
                            AND product = product_t3) price,
                        CAST
                           ((SELECT XMLELEMENT
                                       ("D",
                                        XMLAGG(XMLELEMENT("E", e.product))).getclobval
                                                                              ()
                                                                AS "department"
                               FROM TABLE(xx) e) AS VARCHAR2(500)) myxml
                   FROM (SELECT VALUE(t2) xx, t3.department department_t3,
                                t3.product product_t3
                           FROM (SELECT   department,
                                          CAST
                                             (COLLECT
                                                     (demo_prod_typ(department,
                                                                            OID
                                     FROM demo
                                 GROUP BY department) t1,
                                TABLE(POWERMULTISET(t1.OID)) t2,
                                TABLE(VALUE(t2)) t3))
       GROUP BY department_t3, myxml
       ORDER BY 1, 2;

and this is some more normal web

DEPART MENT_T3	PRODUC T_T3	PRICE	MYXML
1	10	1200	<D><E>10</E></D>
2	22	99	<D><E>22</E></D>
2	24	101	<D><E>24</E></D>
2	22	99	<D><E>22</E><E>24</E></D>
2	24	101	<D><E>22</E><E>24</E></D>
2	25	150	<D><E>25</E></D>
2	22	99	<D><E>22</E><E>25</E></D>
2	25	150	<D><E>22</E><E>25</E></D>
2	24	101	<D><E>24</E><E>25</E></D>
2	25	150	<D><E>24</E><E>25</E></D>
2	22	99	<D><E>22</E><E>24</E><E>25</E></D>
2	24	101	<D><E>22</E><E>24</E><E>25</E></D>
2	25	150	<D><E>22</E><E>24</E><E>25</E></D>
2	23	50	<D><E>23</E></D>
2	22	99	<D><E>22</E><E>23</E></D>
2	23	50	<D><E>22</E><E>23</E></D>
2	24	101	<D><E>24</E><E>23</E></D>
2	23	50	<D><E>24</E><E>23</E></D>
2	22	99	<D><E>22</E><E>24</E><E>23</E></D>
2	24	101	<D><E>22</E><E>24</E><E>23</E></D>
2	23	50	<D><E>22</E><E>24</E><E>23</E></D>
2	25	150	<D><E>25</E><E>23</E></D>
2	23	50	<D><E>25</E><E>23</E></D>
2	22	99	<D><E>22</E><E>25</E><E>23</E></D>
2	25	150	<D><E>22</E><E>25</E><E>23</E></D>
2	23	50	<D><E>22</E><E>25</E><E>23</E></D>
2	24	101	<D><E>24</E><E>25</E><E>23</E></D>
2	25	150	<D><E>24</E><E>25</E><E>23</E></D>
2	23	50	<D><E>24</E><E>25</E><E>23</E></D>
2	22	99	<D><E>22</E><E>24</E><E>25</E><E>23</E></D>
2	24	101	<D><E>22</E><E>24</E><E>25</E><E>23</E></D>
2	25	150	<D><E>22</E><E>24</E><E>25</E><E>23</E></D>
2	23	50	<D><E>22</E><E>24</E><E>25</E><E>23</E></D>
3	30	1999	<D><E>30</E></D>
4	41	500	<D><E>41</E></D>
4	45	250	<D><E>45</E></D>
4	41	500	<D><E>41</E><E>45</E></D>
4	45	250	<D><E>41</E><E>45</E></D>
5	56	40	<D><E>56</E></D>
6	63	250	<D><E>63</E></D>
7	77	1200	<D><E>77</E></D>
7	73	50	<D><E>73</E></D>
7	77	1200	<D><E>77</E><E>73</E></D>
7	73	50	<D><E>77</E><E>73</E></D>
8	81	40	<D><E>81</E></D>
9	91	200	<D><E>91</E></D>
9	94	250	<D><E>94</E></D>
9	91	200	<D><E>91</E><E>94</E></D>
9	94	250	<D><E>91</E><E>94</E></D>
10	77	500	<D><E>77</E></D>

50 rows selected.
We only have one more step which is addressed by the outer most SELECT. The only part of this SELECT that might not be obvious is the DISTINCT. Imagine the powerset of {1,2,3}. It would be:
{1},{2},{3},{1,2},{1,3}{2,3}{1,2,3}, now, add together the elements of each group. The results are
1,2,3,3,4,5,6.
As you can see, 3 occurs twice. We don’t want to state twice that 3 is a possible result of adding the elements, hence DISTINCT.

SELECT DISTINCT department_t3, SUM(price) possible
    FROM (SELECT department_t3, product_t3,
                (SELECT unit_price
                   FROM demo
                  WHERE department = department_t3
                    AND product = product_t3) price,
                CAST
                   ((SELECT XMLELEMENT
                               ("D",
                                XMLAGG(XMLELEMENT("E", e.product))).getclobval()
                                                        AS "department"
                       FROM TABLE(xx) e) AS VARCHAR2(500)) myxml
           FROM (SELECT VALUE(t2) xx, t3.department department_t3,
                        t3.product product_t3
                   FROM (SELECT   department,
                                  CAST
                                     (COLLECT (demo_prod_typ(department,
                                           product)) AS demo_prod_tab_typ) OID
                             FROM demo
                         GROUP BY department) t1,
                        TABLE(POWERMULTISET(t1.OID)) t2,
                        TABLE(VALUE(t2)) t3))
       GROUP BY department_t3, myxml
       ORDER BY 1, 2;

The final result set looks like:

DEPARTMENT_T3	POSSIBLE
1	1200
2	50
2	99
2	101
2	149
2	150
2	151
2	200
2	249
2	250
2	251
2	299
2	301
2	350
2	400
3	1999
4	250
4	500
4	750
5	40
6	250
7	50
7	1200
7	1250
8	40
9	200
9	250
9	450
10	500

29 rows selected.

MERGE and Database Link Problem

2007-10-05T09:27:00.000-07:00

Using 10g R2 database.

When trying to do a MERGE where the target table is on the remote database and the source table is a sub-select on the local database, the following error was raised:

ORA-01008: not all variables bound

The MERGE statement in question had no bind variables.

Furthermore, if I change the target table to an identical local table it works just fine, so it's not a syntax problem.

I searched all over the web and all over technet and oracle.com but found only a few scant references to this problem. I was unable to find any restriction documented under DISTRIBUTED TRANSACTIONS in the Oracle documentation. I now believe that MERGE will only work over a DB Link when the target is on the local database and the source table is on the remote database.

If you have information about this restriction, please leave a comment.

2007-10-02T17:14:00.000-07:00

Call-Back Trigger

--Assume you have many application servers running EHCache. You need to make
--sure that these caches have the latest data from the database. Doing a
--total cache refresh on a periodic basis is not feasible because there
--is a large amount of data and only a small percentage of it gets updated.

--Instead, you want the database to send the latest data to the
--caches upon updating corresponding database data.

Create Or Replace Trigger Call_back
   Before Update Of Prod_limit, Current_prod_cnt
   On Prod_total
   Referencing New As New Old As Old
   For Each Row
   When(New.Current_prod_cnt >= New.Prod_limit)
Declare
   Pragma Autonomous_transaction;
   L_web_page     Clob;
   Err_sqlerrm    Varchar2(150);
   L_host_name    Qsn_app.App_server.Host_name%Type;
   L_port_name    Qsn_app.App_server.Port_name%Type;
   L_uri_string   Varchar2(1999);
   L_xml_string   Varchar2(1999);
Begin
   -- Build the url string which includes the primary key and new data
   -- The jsp will know what to do when it gets this URL data
   -- Obviously ,your url must match the specification
   -- that your Java programmer give you.
   L_xml_string    :=
         '/proj1-admin/manageCache_new.jsp?'
      || 'action=update&cacheName=com.myco.product.'
      || 'prod.common.entity.ProdKey&cacheKey='
      || :New.Prod_total_key
      || '&cacheData='
      || '<?xml version = 1.0><ROWSET><ROW num=1?><PROD_LIMIT>'
      || :New.Prod_limit
      || '</PROD_LIMIT><CURRENT_PROD_CNT>'
      || :New.Current_prod_cnt
      || '</CURRENT_PROD_CNT></ROW></ROWSET>';
    -- if the web page does not come back within 5 seconds, stop waiting
   -- and report the problem
   UTL_HTTP.Set_transfer_timeout(5);

    -- app_servers is a table with the names of hosts which are hosting the
   -- caches which need to be updated.
   For Cur1 In (Select Host_name, Port_name
                  From App_servers)
   Loop
      -- assign variables to scope outside of loop so
      -- exception handling can use them
      L_host_name     := Cur1.Host_name;
      L_port_name     := Cur1.Port_name;
      -- prefix your URL with the Protocal, HOST, PORT that you are currently
      -- going to call
      L_uri_string    :=
         Utl_url.Escape(   'http://'
                        || Cur1.Host_name
                        || ':'
                        || Cur1.Port_name
                        || L_xml_string);

      -- Send the data via HTTP GET to the target server
      Select Httpuritype.Createuri(L_uri_string).Getclob() Web_page
        Into L_web_page
        From DUAL;

      -- inspect the resulting web page to see if things went good or bad
      -- if the HTTP call timed-out, (more than 5 seconds),
      -- l_web_page will be null
      --
      If INSTR(L_web_page, 'SUCCESS') = 0
      Then
         My_util.Dautonomous_exception
                        (Pprogramname          => 'call_back',
                         Pprogramlocation      => 'cache refresh failed ',
                         Perrormsg             => 'error',
                         Pmsg_line1            =>    'ProductKey = '
                                                  || :New.Prod_total_key,
                         Pmsg_line2            =>    'Host_name = '
                                                  || L_host_name,
                         Pmsg_line3            =>    'Port_name = '
                                                  || L_port_name,
                         Pmsg_line4            => SUBSTR(L_web_page,
                                                         1,
                                                         200));
      End If;
   End Loop;
Exception
   When Others
   Then
      Err_sqlerrm    := SUBSTR(SQLERRM, 1, 150);
      My_util.Dautonomous_exception
                                (Pprogramname          => 'call_back',
                                 Pprogramlocation      => 'Serious Error',
                                 Perrormsg             => Err_sqlerrm,
                                 Pmsg_line1            =>    'ProductKey = '
                                                          || :New.Prod_total_key,
                                 Pmsg_line2            =>    'Host_name = '
                                                          || L_host_name,
                                 Pmsg_line3            =>    'Port_name = '
                                                          || L_port_name,
                                 Pmsg_line4            => Null);
End Call_back;
/