{"id":575,"date":"2012-11-11T14:08:34","date_gmt":"2012-11-11T14:08:34","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=575"},"modified":"2012-11-11T14:08:34","modified_gmt":"2012-11-11T13:08:34","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=575","title":{"rendered":"Using External Tables&#8230;"},"content":{"rendered":"<style type=\"text\/css\">\n<p>.code, .code pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<p align=\"left\">I have been appointed Track Leader for the Developer&#8217;s Toolkit for <a title=\"KScope13\" href=\"http:\/\/www.kscope13.com\" rel=\"nofollow\" target=\"_blank\">KScope13<\/a>. 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&#8230;) and 5 is very good (Highlight of the conference&#8230;). After all the members of the group have voted, my task is to figure out which abstracts have the highest rating.<\/p>\n<div align=\"left\">\n<p><!--more--><\/p>\n<\/div>\n<p align=\"left\">The website shows the different abstract, your own rating and the average voting over all voters.<\/p>\n<p align=\"left\"><a href=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingOnline.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"VotingOnline\" border=\"0\" alt=\"VotingOnline\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingOnline.png\" width=\"644\" height=\"329\" \/><\/a><a href=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingReport.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"VotingReport\" border=\"0\" alt=\"VotingReport\" align=\"right\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingReport.png\" width=\"244\" height=\"65\" \/><\/a><\/p>\n<p align=\"left\">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.<\/p>\n<p align=\"left\"><a href=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingExcel.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"VotingExcel\" border=\"0\" alt=\"VotingExcel\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/VotingExcel.png\" width=\"644\" height=\"221\" \/><\/a><\/p>\n<p align=\"left\">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&#8217;t want to do.<\/p>\n<p align=\"left\"><a title=\"Oracle\" href=\"http:\/\/www.oracle.com\/\" rel=\"nofollow\" target=\"_blank\">Oracle<\/a> provides the possibility to use a file with Comma Separated Values as an <a title=\"External Tables Concepts\" href=\"http:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14215\/et_concepts.htm\" rel=\"nofollow\" target=\"_blank\">External Table<\/a>. 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.<\/p>\n<p align=\"left\">To create the external table I have to take these steps:<\/p>\n<p align=\"left\">1) create a directory<\/p>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"rem\">-- Create directory <\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   2:  <\/span><span class=\"kwrd\">create or replace directory<\/span> EXTERNALDATA<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>  <span class=\"kwrd\">as<\/span> <span class=\"str\">'C:\\MySources\\ExternalData'<\/span>;<\/pre>\n<\/div>\n<p align=\"left\">2) save the file to this folder on the database server (in this case my own machine)<\/p>\n<p align=\"left\"><a href=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/ExternalFiles.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"ExternalFiles\" border=\"0\" alt=\"ExternalFiles\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/ExternalFiles.png\" width=\"644\" height=\"368\" \/><\/a><\/p>\n<p align=\"left\">3) create an External Table:<\/p>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"rem\">-- Create table<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   2:  <\/span><span class=\"kwrd\">create table<\/span> KSCOPE13_EXT_CSV<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>(<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   4:  <\/span>  session_num       <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span>  session_title     <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   6:  <\/span>  session_submitter <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   7:  <\/span>  primary_presenter <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   8:  <\/span>  co_presenter      <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   9:  <\/span>  voter             <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  10:  <\/span>  total             <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  11:  <\/span>  vote              <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>),<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  12:  <\/span>  session_comment   <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  13:  <\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  14:  <\/span><span class=\"kwrd\">organization external<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  15:  <\/span>(<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  16:  <\/span>  <span class=\"kwrd\">type<\/span> ORACLE_LOADER<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  17:  <\/span>  <span class=\"kwrd\">default directory<\/span> EXTERNALDATA<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  18:  <\/span>  <span class=\"kwrd\">access parameters<\/span> <\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  19:  <\/span>  (<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  20:  <\/span>    <span class=\"kwrd\">records<\/span> delimited <span class=\"kwrd\">by<\/span> newline<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  21:  <\/span>    NODISCARDFILE<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  22:  <\/span>    NOBADFILE<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  23:  <\/span>    NOLOGFILE<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  24:  <\/span>    <span class=\"kwrd\">SKIP<\/span> 1<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  25:  <\/span>    fields terminated <span class=\"kwrd\">by<\/span> <span class=\"str\">','<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  26:  <\/span>    optionally enclosed <span class=\"kwrd\">by<\/span> <span class=\"str\">'&quot;'<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  27:  <\/span>    missing field <span class=\"kwrd\">values<\/span> are <span class=\"kwrd\">null<\/span><\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  28:  <\/span>    (  Session_Num       <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  29:  <\/span>      ,Session_title     <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  30:  <\/span>      ,Session_Submitter <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  31:  <\/span>      ,Primary_Presenter <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  32:  <\/span>      ,Co_Presenter      <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  33:  <\/span>      ,Voter             <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  34:  <\/span>      ,Total             <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  35:  <\/span>      ,Vote              <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  36:  <\/span>      ,Session_Comment   <span class=\"kwrd\">char<\/span>(<span class=\"str\">4000<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  37:  <\/span>    )<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  38:  <\/span>  )<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  39:  <\/span>  <span class=\"kwrd\">location<\/span> (EXTERNALDATA:<span class=\"str\">'kscope2013.csv'<\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">  40:  <\/span>)<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">  41:  <\/span><span class=\"kwrd\">reject limit UNLIMITED<\/span>;<\/pre>\n<\/div>\n<p align=\"left\">4) Create the query:<\/p>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   1:  <\/span><span class=\"kwrd\">SELECT<\/span> <span class=\"kwrd\">DISTINCT<\/span> t.session_title<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   2:  <\/span>               ,t.session_submitter<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   3:  <\/span>               ,<span class=\"kwrd\">round<\/span>(<span class=\"kwrd\">avg<\/span>(t.total) <span class=\"kwrd\">over<\/span> (<span class=\"kwrd\">partition by<\/span> t.session_title, t.session_submitter),4) average<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"lnum\">   4:  <\/span>  <span class=\"kwrd\">FROM<\/span> kscope13_ext_csv t<\/pre>\n<\/div>\n<div class=\"code\" align=\"left\">\n<pre class=\"alt\"><span class=\"lnum\">   5:  <\/span> <span class=\"kwrd\">order<\/span> <span class=\"kwrd\">by<\/span> 3 <span class=\"kwrd\">desc<\/span>;<\/pre>\n<\/div>\n<p align=\"left\">5) Check the results<\/p>\n<p align=\"left\"><a href=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/ResultsFromSQL.png\"><img loading=\"lazy\" decoding=\"async\" style=\"background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px\" title=\"ResultsFromSQL\" border=\"0\" alt=\"ResultsFromSQL\" src=\"http:\/\/bar-solutions.com\/weblog\/img\/ExternalTables\/ResultsFromSQL.png\" width=\"644\" height=\"100\" \/><\/a><\/p>\n<p align=\"left\">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&#8217;s easier to decide which session get selected and which don&#8217;t and, maybe most important, I got to use my Oracle skills on a non-Oracle related issue.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I have been appointed Track Leader for the Developer&#8217;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&#8230;) and 5 is very good (Highlight of the conference&#8230;). After all the members of the group [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-575","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=575"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/575\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}