External Organized table example

Use Cage: Read Files from Oracle locally and use it Or Store It.

----Visitor Log
CREATE TABLE "DILIP"."DILIP_LOG_REPORT_EXT" 
   ( "C1" NUMBER, 
"C2" VARCHAR2(150),
added_on date
 ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DATA_DIR_VISITOR_LOG"
      ACCESS PARAMETERS
      ( records delimited by newline
     BADFILE 'DILIP_log.bad'
     LOGFILE 'DILIP_log.log'
     fields terminated by '|'
     missing field values are null
      ( c1 CHAR(255),
c2 CHAR(255),
added_on CHAR(25)  DATE_FORMAT DATE MASK "DDMMYYhh24miss"
       )
      )
      LOCATION
       ( 'VISITOR_LOG_BIG'
       )
    )
   REJECT LIMIT UNLIMITED;


CREATE PROCEDURE DILIP_LOG_PROC
BEGIN
  FOR I IN (SELECT TRUNC(SYSDATE -79)+ROWNUM ADDED_ON1 FROM ALL_OBJECTS WHERE ROWNUM<=78 )
  BEGIN
    FOR J IN (SELECT rowid, t.* FROM DILIP_LOG_REPORT t where trunc(added_on) = ADDED_ON1)
   BEGIN
INSERT INTO DILIP_LOG_COUNT(C1,C2,ADDED_ON,VISIT_COUNT)
SELECT J.C1,J.C2,J.ADDED_ON,1 FROM DILIP_LOG_REPORT_EXT 
WHERE ROWID = I.ROWID;
    WHEN DUP_VAL_ON_INDEX THEN
UPDATE DILIP_LOG_COUNT SET VISIT_COUNT:=VISIT_COUNT+1
WHERE ROWID = I.ROWID;
    END LOOP;  
END ;



YESTERDAY=$(date --date "1 days ago" +%d%m%y)
DATAFILE=visitor_log_$YESTERDAY.txt
#!/usr/bin/expect --
#cat $DATAFILE > VISITOR_LOG_BIG
cat DILIP_log.bad >> ALL_DILIP_log.bad
awk -F '|' '{if((length($1)==12)&&($1>910000000000)&&($1<=919999999999)) print $1,"|",$2,"|",$9}' $DATAFILE | sort | uniq >  VISITOR_LOG_BIG



create table log_report_summary
PARTITION BY RANGE (added_on) 
SUBPARTITION BY HASH (client_ID)
SUBPARTITIONS 15
(
PARTITION MAY2007 
VALUES LESS THAN (TO_DATE('01-JUN-2007' ,'DD-MON-YYYY')),
PARTITION JUN2007
VALUES LESS THAN (TO_DATE('01-JUL-2007','DD-MON-YYYY')),
PARTITION JUL2007
VALUES LESS THAN (TO_DATE('01-AUG-2007','DD-MON-YYYY')),
PARTITION AUG2007
VALUES LESS THAN (TO_DATE('01-SEP-2007','DD-MON-YYYY')),
PARTITION SEP2007
VALUES LESS THAN (TO_DATE('01-OCT-2007','DD-MON-YYYY')),
PARTITION OCT2007
VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')),
PARTITION NOV2007
VALUES LESS THAN (TO_DATE('01-DEC-2008','DD-MON-YYYY')),
PARTITION DEC2007
VALUES LESS THAN (MAXVALUE)
) 
AS
select  rownum ID, t.* from
(select c1,c2,Remote_add,c3,
client_id,type,level_id,pg_no,trunc(added_on)added_on ,count(*) count
from LOG_REPORT_2007 
group by c1,c2,Remote_add,c3,client_id,type,level_id,pg_no,trunc(added_on) )t