Wednesday, June 24, 2009

Oracle 10g: Flush Data To External Table - Using For Loops & Calling Stored Procedures

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;

No comments: