Taking STOCK

A feature of Oracle that I use a lot is EXTERNAL tables.   An external table leverages SQL*Loader  to read a file and present it as a table.  In early versions of EXTERNAL table the file needed to be a regular file.   With current releases the there is now an option to use a PREPROCESSOR.  This allows for converters like unzip to extract a file.  I will show how to pull stock quotes into an external table.

In this example I will use a shell script that pulls quotes from yahoo finance by using curl,  curl is a tool to transfer data from or to a server.   I have only tested on Linux but should work on any Unix like system.  If you do not have curl use your package manager to get curl.

The first thing needed is a directory for external tables to work with.  This is done with CREATE DIRECTORY.  I am going to create two for this example.   One for normal operations and one for executables.    As SYS or SYSTEM do:

CREATE DIRECTORY logdir as '/tmp' ;
CREATE DIRECTORY exec_dir  as '/usr/local/bin' ;

GRANT read,write ON DIRECTORY logdir TO stocks; 
GRANT read,execute ON DIRECTORY  exec_dir TO stocks;

This has created two directories, logdir and exec_dir.   Then assigned  appropriate access to each directory, read and write on logdir and read and execute on exec_dir to the existing user ‘stocks’,  but you user may be different.   I use two directories because I don’t want to write the directory /usr/local/bin  for two reasons.  The first is clutter, there are various log files that are generated with external files.  The other is I do not want to open the file permissions on the directory to allow writing by the process running oracle.    I have the logdir created for those files and for files I want to read in common usage of external files.  This is sent to /tmp so the OS will clean them up at some point.   We also need a table to hold the stock symbols we wish to track.  Log in as the user stocks and create the needed table.

CREATE TABLE stocks ( symbol VARCHAR2( 32 ) ) ; 

INSERT INTO stocks ( 'ORCL' ) ;

This is a trivial table you can expand depending on your needs.   You can add additional symbols if you would like

Now to create the file that will be used to get the list and populate the table, save as  yahoo_quote.sh.   So do the following:

sudo vi /usr/local/bin/yahoo_quote.sh

go into insert move with an i then paste in the following.  Change the location of sqlplus if yours is different.   Also if you have a different schema password changes those.

#!/bin/bash 
CURL=/usr/bin/curl
TR=/usr/bin/tr

## Make sure this is where sqlplus resides on you system! 
SQLPLUS=/u01/app/oracle/product/11.2.0/xe/bin/sqlplus

## These are octal values for their namesake
SPACE="\40"
QUOTE="\42"
TAB="\11"

## Change the user/pass if needed
lst=$( ${SQLPLUS} -s stocks/stocks  << EOF 
set head off
set feed off
set timing off
select listagg(symbol,',') WITHIN GROUP (ORDER BY symbol) FROM stocks;
exit
EOF
)

base_url='http://download.finance.yahoo.com/d/quotes.csv?s='
params='&f=sl1d1t1c1ghvpp2wern'

endpoint=$(echo ${base_url}${lst}${params}|${TR} -d  $SPACE )

${CURL} -s  "${endpoint}"|${TR} -d $QUOTE | ${TR} ',' $TAB

Save and exit (‘Esc’ :wq)  then  change the permissions and test.  A brief explanation of the script.   I establish names for the fully qualified executables I will need.  Then set some values that will be used to aid in formatting the output.  Next I pull the list of stocks using sqlplus and a query.    I then concatenate the parts of the URL also using tr to strip any spaces.   Lastly I call curl to pull the data, stripping quotes and converting commas to tabs

$> sudo chmod 755 /usr/local/bin/yahoo_quote.sh
$> yahoo_quote.sh

If you have done all the steps you should see something like:

ORCL    34.86    1/11/2013    4:00pm    -0.05    34.78    35.00    15105158    34.91    -0.14%    25.33 - 35.00    2.127    16.41    Oracle Corporatio

Now we re ready to put into the database, in sqlplus paste the following.

drop table  yahoo_quotes_ext 
/

CREATE TABLE yahoo_quotes_ext (
   symbol varchar2(16) ,
   last_trade varchar2(32), 
   last_trade_dt VARCHAR2(20),
   last_trade_tm VARCHAR2(20),
   change VARCHAR2(20),
   day_lo VARCHAR2(20),
   day_hi VARCHAR2(20),
   volume VARCHAR2(20),
   previous VARCHAR2(20),
   change_pc VARCHAR2(20),
   Year_range  varchar2(32),
   eps   number ,
   pe    number ,
   co_name  varchar2( 24 ) )  
ORGANIZATION EXTERNAL(
 TYPE ORACLE_LOADER
  DEFAULT DIRECTORY LOGDIR
 ACCESS PARAMETERS(
         records delimited BY newline
 preprocessor exec_dir:'yahoo_quote.sh' 
         FIELDS terminated BY X'09'
            MISSING FIELD VALUES ARE NULL)
                          LOCATION (exec_dir:'yahoo_quote.sh')
                          )
   PARALLEL
   REJECT LIMIT UNLIMITED
/

To briefly explain the create table statement, the first part is like any other giving names and data types.    Then the ORGANIZATION EXTERNAL describes the format.   In this case the name and location of the preprocessor to run then each record is on a line, fields separated by a tab character.

If all went well we can now get  the quote with a select statement

SELECT    SYMBOL ,
      LAST_TRADE,
      to_date( LAST_TRADE_DT
         ||LAST_TRADE_TM, 'MM/DD/YYYYHH:MIpm' ) last_trade_dt ,
      CHANGE ,
      CASE
         WHEN DAY_HI ='N/A'
         THEN LAST_TRADE
         ELSE DAY_HI
      END AS day_hi,
      CASE
         WHEN DAY_LO = 'N/A'
         THEN last_trade
         ELSE day_lo
      END AS day_lo ,
      VOLUME ,
      PREVIOUS ,
      CHANGE_PC ,
      YEAR_RANGE ,
      EPS ,
      PE ,
      CO_NAME
 FROM yahoo_quotes_ext ;

I convert the ‘N/A’ that may get stored to numbers to keep the data  type consistent with what I want to store

 

 

Bookmark the permalink.

2 Responses to Taking STOCK

  1. vintih says:

    Do we need anything specific to be installed in oracle side to invoke curl ?

Leave a Reply