(Information is Beautiful)

Populating Data

Create a stored procedure to load the data (this one creates a row in an Oracle DB for each scan):

create or replace 
PROCEDURE FILL_SCANS(
 event_key IN NUMBER
, scan_date_key in NUMBER
, lvl IN VARCHAR2
, loctn IN VARCHAR2
, scans_count IN NUMBER 
) IS
 l_rows NUMBER := 1;
BEGIN
LOOP
 EXECUTE IMMEDIATE 
'insert into CORE.FACT_TICKET_SCAN(FACT_TKT_SCAN_ID, EVENT_KEY, SCAN_DATE_KEY, LVL, LOCTN) VALUES (:l_rows,:event_key,:scan_date_key,:lvl,:loctn)' USING l_rows, event_key, scan_date_key, lvl, loctn;
 
 l_rows := l_rows + 1;
 EXIT WHEN l_rows > scans_count;
 END LOOP;
 
END FILL_SCANS;

 

Once you have built your data-set in Excel, create a column with a formula to call the procedure:

="exec core.fill_scans("&[@[EVENT_KEY]]&","&[@[SCAN_DATE_KEY]]&",'"&[@LVL]&"','"&[@LOCTN]&"',"&[@[scans_count]]&");"

Then, copy the entire column as a set of commands into SQL Developer and run!

[go make some tea]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s