Magic of Oracle Externel Table

I want to share one experience regarding the Oracle External Table

One day I was working 24 X 7 in my office
My junior ask me that how to load the csv data in table
the purpose of load the csv data in table is to afterward process the table data and generate the report using third party tool.

challenge is that we have multiple csvs which is generated every hour by applications
and we have around four diff application servers which continuously generate the csv files

how to manage the multiple csv files
how to load the csv files data in single table?

These are all challenges so first thing came in my mind is to use SQLLDR but sqlldr is not easy to manage these all multiple csv files
one by one and it’s also difficult to review sqlldr log and bad files in case if any generated, I believe that no one going to review the
logs at least.

finally we came to one idea regarding external table

I suggest and explain the concept of external table to my junior

He really came with some good table design with external table

good part of this story is that we can easily write stored procedure on External table and process the data for generate the application
data

advantages of using external table in our case as belows
there is no data save in database only table ddl save in database
we can process the data within the expected time and generated the good report as well
no need to generate the cdr directly in oracle table in production setup

-bash-3.2$ pwd
/home/oracle/u01/DUMP_DIR

$ ls -ltr
-rw-r--r-- 1 oracle oinstall 2720960061 Apr 26 17:32 MY_CDR.csv

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME	DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            DUMP_DIR       	/home/oracle/u01/DUMP_DIR

CREATE TABLE "XTERNAL"."MYCDRKL"
   (	"USERNAME" VARCHAR2(264 BYTE),
	"GATEWAYADDRESS" VARCHAR2(32 BYTE),
	"FRAMEDIPADDRESS" VARCHAR2(32 BYTE),
	"REQUESTTYPE" VARCHAR2(30 BYTE),
	"MONITORINGKEY" VARCHAR2(64 BYTE),
	"IMSI" VARCHAR2(100 BYTE),
	"APN" VARCHAR2(25 BYTE),
	"PARAM1" VARCHAR2(100 BYTE),
	"PARAM2" VARCHAR2(100 BYTE),
	"PARAM3" VARCHAR2(100 BYTE),
	"PARAM4" VARCHAR2(100 BYTE),
	"PARAM5" VARCHAR2(100 BYTE),
	"LAC" VARCHAR2(10 BYTE),
	"USEREQUIPMENT" VARCHAR2(25 BYTE),
	"ZONE" VARCHAR2(100 BYTE),
	"AGGREGATEKEY" VARCHAR2(128 BYTE),
	"INPUTOCTETS" NUMBER(32,0),
	"OUTPUTOCTETS" NUMBER(32,0),
	"TOTALOCTETS" NUMBER(32,0),
	"USAGETIME" VARCHAR2(64 BYTE),
	"CDRTIMESTAMP" VARCHAR2(64 BYTE)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DUMP_DIR"
      ACCESS PARAMETERS
      ( records delimited by newline
           fields terminated by ','
        )
      LOCATION
       ( 'MY_CDR.csv'
       )
    );

-bash-3.2$ sqlplus xternal/xternal

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 28 16:55:54 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT COUNT(1) FROM TBLCDRKL;

  COUNT(1)
----------
  13639762

Elapsed: 00:00:27.24

SQL> set timing on
SQL> set colsep "|"
SQL> set pagesize 10000
SQL> COLUMN USERNAME    FORMAT A25
SQL> COLUMN CNT         FORMAT 999999
SQL> SELECT USERNAME,COUNT(1) CNT FROM TBLCDRKL
  2                GROUP BY USERNAME
  3                HAVING COUNT(1) > 500;

USERNAME                 |    CNT
-------------------------|-------
917278233269             |    815
919859728913             |    640
917687875827             |    679
918751808719             |    551
919615190434             |   1334
919577731446             |    509
919854353922             |    618
919615537618             |    950
917685905745             |    680
917688076510             |    515
917686059866             |    691
918751851412             |    701
918014167126             |    768
917686069305             |    519
919804558727             |    993
919856812692             |   1070
918575938577             |    589
919856786393             |    534
919856706867             |    694
917278141625             |    523
919613105578             |    561
919853945452             |    511
917399157793             |    563
917540829143             |    560
917537917862             |    639
919615464661             |    535
918753055197             |    560
917399597568             |    917
919859707693             |    563
919854524727             |    603
918575240522             |   1087
919854559832             |    642
917688057964             |    509
917538925080             |    512
917685844343             |    601
919854606734             |    529
919577252410             |    583
917685878232             |    965
919615002340             |    546
917687951974             |    771
917278248807             |    711
919804049853             |    507
918908960285             |    587
918751884230             |    606
919615397790             |    546
917688026888             |    536
919615691846             |    596
918013741464             |    676
918013958396             |   1494
919804349491             |    559
919613918738             |    585
919856114225             |    602
917278323264             |    554
919856629305             |    884
919856741873             |    513
918013799737             |    638
919804394342             |    673
919804236047             |    870
917278037273             |    522
918575289415             |    682
919856547343             |    523
919577543093             |    675
918013512926             |    600
919615597188             |    782
919804642983             |   1310
917539074056             |    736
917540873786             |    543
919859304788             |    521
919854552533             |    634
919859129976             |    723
917278255126             |    618
919615453580             |    717
919615442691             |    504
917685849565             |    740
919854413398             |    521
917399650740             |    610
919615164630             |    532
917688042132             |    506
919615656684             |    528
917278433587             |    758
917687033120             |    501
919804644976             |    711
919856539507             |    756
919856969805             |    640
917278557917             |    723
918752064970             |    622
919859898134             |    681
918575177980             |    514
918908135409             |    661
917504711515             |    612
919615938504             |    501
919856292131             |    570
917504697054             |    567
918014364619             |    678
917278236533             |    552
918014698756             |    610
918014971666             |    729
919804477344             |    552
919615084183             |    619
918575145195             |    779
918575203965             |    522
919658129327             |    557
919615326309             |    641
919658021622             |    569
919615386235             |    516
918575974515             |    873
918575994856             |    560
918013183853             |    530
919856428163             |    546

109 rows selected.

Elapsed: 00:00:32.61

create or replace PROCEDURE CDR_Rpt
       IS
        fhdl UTL_FILE.FILE_TYPE;
        BEGIN

         fhdl := UTL_FILE.FOPEN('DUMP_DIR' , 'CDR_Rpt_' ||to_char((sysdate-1),'mm-dd-yyyy')|| '.csv', 'W', 32767);
         FOR rec IN (select username||','||param2||','||sum(inputoctets)||','||sum(outputoctets)||','||sum(totaloctets)||','||param1||','||param4||','||param5 as txt
         from TBLCDRKL group by param2, username, param1, param4, param5)
         loop
          utl_file.put_line(fhdl, rec.txt);
         end loop;
        UTL_FILE.FCLOSE(fhdl);
end;
/

-bash-3.2$ pwd
/home/oracle/u01/DUMP_DIR
-bash-3.2$ ll
total 4542032
-rw-r--r-- 1 oracle oinstall   86387615 Apr 28 13:04 CDR_Rpt_04-27-2014.csv
-rw-r--r-- 1 oracle oinstall 2720960061 Apr 26 17:32 MY_CDR.csv

Advantages

  • No need to load the data in Oracle
  • CDR generation in file e.g. csv instead of database
  • we can process the data without load the data in oracle

Disadvantages

  • We cannot create index on external table
  • External Table are read only table

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.