Using External Tables…



By Patch ~ November 11th, 2012. Filed under: Oracle, SQL.

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.

VotingOnlineVotingReport

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.

VotingExcel

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)

ExternalFiles

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

ResultsFromSQL

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.

Reader's Comments

  1. thatjeffsmith | November 11th, 2012 at 2:28 pm

    Another SQL Developer Tip & Trick is that you can take your CSV and have it build your SQL*Loader or External Table scripts for you :)

    http://www.thatjeffsmith.com/archive/2012/08/using-oracle-sql-developer-to-setup-sqlloader-runs/

  2. Michael O'Neill | November 11th, 2012 at 5:37 pm

    Hilarious. Patrick makimg an Oracle mole hill out of an Excel mountain and Jeff trying to make the mole hill disappear.

  3. thatjeffsmith | November 11th, 2012 at 7:30 pm

    Yes, I took the bait, and make no apologies!

Leave a Comment