How Do I Handle Existing PL/SQL Code with Warehouse Builder? 6-1
6
How Do I Handle Existing PL/SQL Code with
Warehouse Builder?
Scenario
A movie rental company periodically updates the customer rental activity in its
CUST_RENTAL_ACTIVITY table, where it stores the rental sales and overdue charges
data per customer. This table is used for different mailing campaigns. For example, in
their latest mailing campaign, customers with high overdue charges are offered the
company's new pay-per-view service.
Currently, the movie rental company uses a PL/SQL package to consolidate their data.
The existing PL/SQL package needs to maintained manually by accessing the
database. This code runs on an Oracle8i Database.
CREATE OR REPLACE PACKAGE RENTAL_ACTIVITY AS
PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE);
END RENTAL_ACTIVITY;
/
CREATE OR REPLACE PACKAGE BODY RENTAL_ACTIVITY AS
PROCEDURE REFRESH_ACTIVITY(SNAPSHOT_START_DATE IN DATE) IS
CURSOR C_ACTIVITY IS
SELECT
CUST.CUSTOMER_NUMBER CUSTOMER_NUMBER,
CUST.CUSTOMER_FIRST_NAME CUSTOMER_FIRST_NAME,
CUST.CUSTOMER_LAST_NAME CUSTOMER_LAST_NAME,
CUST.CUSTOMER_ADDRESS CUSTOMER_ADDRESS,
CUST.CUSTOMER_CITY CUSTOMER_CITY,
CUST.CUSTOMER_STATE CUSTOMER_STATE,
CUST.CUSTOMER_ZIP_CODE CUSTOMER_ZIP_CODE,
SUM(SALE.RENTAL_SALES) RENTAL_SALES,
SUN(SALE.OVERDUE_FEES) OVERDUE_FEES
FROM CUSTOMER CUST, MOVIE_RENTAL_RECORD SALE
WHERE SALE.CUSTOMER_NUMBER = CUST.CUSTOMER_NUMBER AND
SALE.RENTAL_RECORD_DATE >= SNAPSHOT_START_DATE
GROUP BY
CUST.CUSTOMER_NUMBER,
CUST.CUSTOMER_FIRST_NAME,
CUST.CUSTOMER_LAST_NAME,
CUST.CUSTOMER_ADDRESS,
CUST.CUSTOMER_CITY,
CUST.CUSTOMER_STATE,
6-2 Oracle Warehouse Builder Case Book
CUST.CUSTOMER_ZIP_CODE;
V_CUSTOMER_NUMBER NUMBER;
V_CUSTOMER_FIRST_NAME VARCHAR2(20);
V_CUSTOMER_LAST_NAME VARCHAR2(20);
V_CUSTOMER_ADDRESS VARCHAR(50);
V_CUSTOMER_CITY VARCHAR2(20);
V_CUSTOMER_STATE VARCHAR2(20);
V_CUSTOMER_ZIP_CODE VARCHA