Using External Tables…

I have been appointed Track Leader for the Developer’s Toolkit for KScope13. This involves voting on abstracts. Everyone in the group should rate the abstracts on a scale of 1 to 5. Where 1 is not good (Not appropriate…) and 5 is very good (Highlight of the conference…). After all the members of the group have voted, my task is to figure out which abstracts have the highest rating.

The website shows the different abstract, your own rating and the average voting over all voters.

Coming up with the top sessions involves a lot of scrolling, writing stuff down and trying to make out the rankings. Luckily the site also provides the possibility to download the voting in a Comma Separated Values format so you can open it in Excel.

Since I am not (yet) an Excel Wizard this is not what I am looking for. Still takes a lot of work, which I don’t want to do.

Oracle provides the possibility to use a file with Comma Separated Values as an External Table. Taking this approach gives me the opportunity to apply my SQL skills to the data and have the database do the heavy lifting on figuring out which abstracts get the highest rating.

To create the external table I have to take these steps:

1) create a directory

   1:  -- Create directory

   2:  create or replace directory EXTERNALDATA

   3:    as 'C:\MySources\ExternalData';

2) save the file to this folder on the database server (in this case my own machine)

3) create an External Table:

   1:  -- Create table

   2:  create table KSCOPE13_EXT_CSV

   3:  (

   4:    session_num       VARCHAR2(4000),

   5:    session_title     VARCHAR2(4000),

   6:    session_submitter VARCHAR2(4000),

   7:    primary_presenter VARCHAR2(4000),

   8:    co_presenter      VARCHAR2(4000),

   9:    voter             VARCHAR2(4000),

  10:    total             VARCHAR2(4000),

  11:    vote              VARCHAR2(4000),

  12:    session_comment   VARCHAR2(4000)

  13:  )

  14:  organization external

  15:  (

  16:    type ORACLE_LOADER

  17:    default directory EXTERNALDATA

  18:    access parameters

  19:    (

  20:      records delimited by newline

  21:      NODISCARDFILE

  22:      NOBADFILE

  23:      NOLOGFILE

  24:      SKIP 1

  25:      fields terminated by ','

  26:      optionally enclosed by '"'

  27:      missing field values are null

  28:      (  Session_Num       char(4000)

  29:        ,Session_title     char(4000)

  30:        ,Session_Submitter char(4000)

  31:        ,Primary_Presenter char(4000)

  32:        ,Co_Presenter      char(4000)

  33:        ,Voter             char(4000)

  34:        ,Total             char(4000)

  35:        ,Vote              char(4000)

  36:        ,Session_Comment   char(4000)

  37:      )

  38:    )

  39:    location (EXTERNALDATA:'kscope2013.csv')

  40:  )

  41:  reject limit UNLIMITED;

4) Create the query:

   1:  SELECT DISTINCT t.session_title

   2:                 ,t.session_submitter

   3:                 ,round(avg(t.total) over (partition by t.session_title, t.session_submitter),4) average

   4:    FROM kscope13_ext_csv t

   5:   order by 3 desc;

5) Check the results

This makes my job a lot easier. Well, maybe not a lot, just a bit. Still had to build the schedule for the top abstracts. But at least it’s easier to decide which session get selected and which don’t and, maybe most important, I got to use my Oracle skills on a non-Oracle related issue.