Monday, November 9, 2009

APEXposed 2009

I just arrived in Atlanta for APEXposed. This promises to be an excellent event with a lot of great sessions. It will be nice to see some familiar faces as well...

Monday, November 2, 2009

Free Webinar with Steven Feuerstein

SkillBuilders will be presenting a “Best of PL/SQL” class with Steven Feuerstein. In anticipation of the event, we will be hosting a preview webinar led by Steven, entitled "Look What You Can Do With PL/SQL!" This will be tomorrow, November 3rd, from 11am to 12pm EDT.

Here’s the description of the webinar:

This webinar provides a whirlwind tour of some of the most exciting features of PL/SQL that you all should know about and use, including DBMS_ERRLOG, FORALL, BULK COLLECT and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.
This webinar is a preview of Steven's class, "Best of Oracle PL/SQL," which SkillBuilders will present in Needham, MA on January 26-27.


Audience: Oracle developers and DBAs with a working knowledge of PL/SQL and at least a year's worth of experience with the language. All developers, from beginner to expert, will benefit from Steven's ideas and examples.

 

Oracle ACE Director Steven Feuerstein is an expert on the Oracle PL/SQL language. He is the author or coauthor of 10 books on PL/SQL including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices, is a PL/SQL Evangelist and has been developing software since 1980. His current obsession is improving PL/SQL code quality through automated unit testing. You can find out more about Steven and his PL/SQL-related activities by visiting www.stevenfeuerstein.com and www.ToadWorld.com/SF.

Transposed Report Example

Sometimes the way data is stored in the database can make it difficult to display in a report. Let’s say, for example, I was creating an “Events Tracking” application. If the application was only being designed to track one type of event (concerts) I could probably get away with one table:

With this single table design, I can create a report in Apex with a simple query using a classic report with the type set to SQL Query:

SELECT artist,
   description,
   venue,
   event_date,
   price
FROM events

But what would happen if the application was being designed to track multiple types of events where each event type could have a variable number of event attributes? In this case I might consider a design that uses a few different tables:

With this design I have a lot of flexibility but when I generate a report on the data things get a little tricky. I want a report similar to the simple query where each column is an event attribute and each row represents one event. Of course, I’d only be displaying one type of event at a time…

In order to accommodate the flexible table design, I need to take advantage of a more complex report type in Apex: SQL Query (PL/SQL function body returning SQL Query). At the end of the day, both reports are based on a SQL Query. But in this one I use PL/SQL to dynamically build the query on the fly.

You can check out the demo here. Follow these steps to recreate the demo in your environment:

  1. Set up the demo tables and data - do this in a development environment. 
    CREATE TABLE EVENT_TYPES( 
       ID NUMBER NOT NULL, 
       TITLE VARCHAR2(100 BYTE), 
       CONSTRAINT EVENT_TYPES_PK PRIMARY KEY (ID) 
    )
    /
    
    CREATE TABLE EVENT_TYPE_ATTRIBUTES( 
       ID NUMBER NOT NULL, 
       EVENT_TYPE_ID NUMBER, 
       TITLE VARCHAR2(100 BYTE), 
       DISPLAY_ORDINAL NUMBER, 
       CONSTRAINT EVENT_TYPE_ATTRIBUTES_PK PRIMARY KEY (ID), 
       CONSTRAINT EVNT_TYPE_ATRS_EVNT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
          REFERENCES EVENT_TYPES (ID) 
    )
    /
    
    CREATE TABLE EVENTS ( 
       ID NUMBER NOT NULL, 
       EVENT_TYPE_ID NUMBER NOT NULL, 
       EVENT_DATE DATE NOT NULL, 
       DESCRIPTION VARCHAR2(4000) NOT NULL, 
       CONSTRAINT EVENTS_PK PRIMARY KEY (ID), 
       CONSTRAINT EVENTS_EVENT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
          REFERENCES EVENT_TYPES (ID) 
    )
    /
    
    CREATE TABLE EVENT_DETAILS( 
       ID NUMBER NOT NULL, 
       EVENT_ID NUMBER NOT NULL, 
       EVENT_TYPE_ATTRIBUTE_ID NUMBER NOT NULL, 
       EVENT_TYPE_ATTRIBUTE_VALUE VARCHAR2(4000), 
       CONSTRAINT EVENT_DETAILS_EVENTS_FK FOREIGN KEY (EVENT_ID) 
          REFERENCES EVENTS (ID), 
       CONSTRAINT EVENT_DTLS_EVENT_TYPE_ATRS_FK FOREIGN KEY (EVENT_TYPE_ATTRIBUTE_ID) 
          REFERENCES EVENT_TYPE_ATTRIBUTES (ID) 
    )
    /
    
    INSERT INTO event_types (id, title) VALUES (1, 'Football Game');
    INSERT INTO event_types (id, title) VALUES (2, 'Concert');
    INSERT INTO event_types (id, title) VALUES (3, 'Movie');
    
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (1,1,'Home Team');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (2,1,'Visiting Team');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (3,1,'Line');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (4,2,'Band or Singer');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (5,2,'Venue');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (6,2,'Ticket Cost');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (7,3,'Title');
    INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (8,3,'Genre');
    
    INSERT INTO events (id,event_type_id,event_date,description) VALUES (1,1,TO_DATE('12-NOV-09 6:00 PM','DD-MON-RR HH:MI PM'),'USF back in the top 25 - can they stay there?');
    INSERT INTO events (id,event_type_id,event_date,description) VALUES (2,2,TO_DATE('25-DEC-09 8:00 PM','DD-MON-RR HH:MI PM'),'He''s back for one day only');
    INSERT INTO events (id,event_type_id,event_date,description) VALUES (3,1,TO_DATE('28-NOV-09 3:30 PM','DD-MON-RR HH:MI PM'),'A big rivalry this week!');
    INSERT INTO events (id,event_type_id,event_date,description) VALUES (4,3,TO_DATE('09-DEC-09 11:00 PM','DD-MON-RR HH:MI PM'),'Very scary movie!');
    
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (1,1,1,'USF');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (2,1,2,'Rutgers');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (3,1,3,'-10');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (4,3,1,'Florida Gators');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (5,3,2,'FSU');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (6,3,3,'???');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (7,2,4,'Elvis');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (8,2,5,'NY');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (9,2,6,'$500');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (10,4,7,'Paranormal Activity');
    INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (11,4,8,'Horror');
    
    COMMIT;
  2. Create a new report region on a page. Make sure to select the “generic columns” option below the source. Also, replace PXX_EVENT_TYPE_ID with the appropriate item name (created in the next step).
    DECLARE
    
       l_retval        VARCHAR2(32767);
       l_crlf          CHAR(2) := CHR(13)||CHR(10);
       l_event_type_id EVENT_TYPES.ID%TYPE;
       
       CURSOR event_type_attributes_cur (
          p_event_type_id IN EVENT_TYPE_ATTRIBUTES.EVENT_TYPE_ID%TYPE
       )
       IS
          SELECT *
          FROM event_type_attributes
          WHERE event_type_id = p_event_type_id;
          
       l_event_type_attributes_rec EVENT_TYPE_ATTRIBUTES_CUR%ROWTYPE;
    
    BEGIN
    
       l_event_type_id := nv('PXX_EVENT_TYPE_ID');
    
       l_retval := 'SELECT evt.event_date AS "Event Date", ' || l_crlf
          || ' evt.description AS "Description", ';
       
       OPEN event_type_attributes_cur(l_event_type_id);
       
       LOOP
          FETCH event_type_attributes_cur INTO l_event_type_attributes_rec;
          EXIT WHEN event_type_attributes_cur%NOTFOUND;
          
          l_retval := l_retval ||
                '   (' || l_crlf
             || '      SELECT event_type_attribute_value' || l_crlf
             || '      FROM event_details' || l_crlf
             || '      WHERE event_id = evt.id' || l_crlf
             || '         AND event_type_attribute_id = ' || l_event_type_attributes_rec.id || l_crlf
             || '   ) AS "' || l_event_type_attributes_rec.title || '",' || l_crlf;
       END LOOP;
       
       CLOSE event_type_attributes_cur;
       
       l_retval := RTRIM(l_retval, ',' || l_crlf);
       
       l_retval := l_retval || l_crlf
          || 'FROM events evt' || l_crlf
          || 'WHERE event_type_id = ' || l_event_type_id;
    
       RETURN l_retval;
    
    END;
  3. Add an item to select the event type. Choose an item type of Select List with Submit, set the name to PXX_EVENT_TYPE_ID (replace XX with page number). Note that the report will fail to render if the value of this item is not set. There are various ways to “handle” this  - I used a page process to set the value to 1 if the item’s value was NULL. Use the following for the LOV:
    SELECT title AS display,
       id AS return
    FROM event_types
    ORDER BY display
  4. Add a branch back to the same page.