I'm using external table as a passage to load data from a flat file ''postcode.CSV' to respective table - "GBL_POSTCODE_DTL ".
Script:/*
*
* Pump data to table:
* *
*
* @Migration POSTCODEDIR
* @author Nur Fiza Suhati
* @version 1.0
*
*/
/*
1. Directory to database
*/
CREATE OR REPLACE DIRECTORY POSTCODEDIR AS 'C:\oracle\product\10.1.0\oradata\myep';
DROP TABLE POSTCODE_EXT;
create table POSTCODE_EXT (
ADDRESS varchar2(100),
IDTABLE VARCHAR2(1000),
POSTCODE varchar2(100),
POSTOFFICE varchar2(100),
STATE varchar2(100))
Organization external
(type oracle_loader
default directory POSTCODEDIR
access parameters (records delimited by newline
fields terminated by '')
location ('postcode.csv'))
reject limit 100000;
TRUNCATE TABLE GBL_POSTCODE_DTL;
INSERT INTO GBL_POSTCODE_DTL (ADDRESS,POSTCODE,POSTOFFICE,STATE,ID)
(
SELECT postcode_ext.address,postcode_ext.postcode,postcode_ext.postoffice,
CASE WHEN postcode_ext.state = 'JOHOR' THEN 2
WHEN postcode_ext.state = 'KEDAH' THEN 3
WHEN postcode_ext.state = 'KELANTAN' THEN 4
WHEN postcode_ext.state = 'MELAKA' THEN 5
WHEN postcode_ext.state = 'NEGERI SEMBILAN' THEN 6
WHEN postcode_ext.state = 'PAHANG' THEN 7
WHEN postcode_ext.state = 'PERAK' THEN 9
WHEN postcode_ext.state = 'PERLIS' THEN 10
WHEN postcode_ext.state = 'PULAU PINANG' THEN 8
WHEN postcode_ext.state = 'SABAH' THEN 13
WHEN postcode_ext.state = 'SARAWAK' THEN 14
WHEN postcode_ext.state = 'SELANGOR' THEN 11
WHEN postcode_ext.state = 'TERENGGANU' THEN 12
WHEN postcode_ext.state = 'WILAYAH PERSEKUTUAN' THEN 15
ELSE 19
END AS "state",TO_NUMBER(postcode_ext.IDTABLE)
FROM postcode_ext postcode_ext
);
set serveroutput on
declare
CITYID_ INTEGER;
begin
FOR r IN (SELECT distinct(gbl_postcode_dtl.POSTCODE), gbl_postcode_dtl.POSTOFFICE, gbl_postcode_dtl.STATE FROM gbl_postcode_dtl gbl_postcode_dtl)
LOOP
gbl_address_pckg.SP_ADDDTLCITY(CITYID_,r.STATE,r.POSTCODE,r.POSTOFFICE);
dbms_output.put_line('postcode: ' r.POSTCODE );
END LOOP;
end;
