Inbound Interface

1. Create a flat file using OUTBOUND or manually. (‘txt’ or ‘dat’ file)


(E:\vbbk\emp.txt)

SMITH$7369$800


ALLEN$7499$1600

WARD$7521$1250

JONES$7566$2975

MARTIN$7654$1250

BLAKE$7698$2850

CLARK$7782$2450

SCOTT$7788$3000

KING$7839$5000

TURNER$7844$1500

ADAMS$7876$1100

JAMES$7900$950

FORD$7902$3000

MILLER$7934$1300

KAARTHIK$$5565

$000$1515

RAMU$5454$

2. Create a staging table with




CREATE TABLE MY_EMP_STAG(ENAME VARCHAR2(10),EMPNO NUMBER(4),SAL NUMBER(7,2),FLAG VARCHAR2(2));



3. Create control file

(E:\vbbk\myin.ctl)



LOAD DATA

INFILE 'E:\VBBK\EMP.TXT'

INTO TABLE "MY_EMP_STAG"

INSERT

FIELDS TERMINATED BY '$'

TRAILING NULLCOLS

(ENAME,EMPNO,SAL)



4. Run SQLLDR to load the data into the table



D:\oracle\prodora\8.0.6\BIN>SQLLDR scott/tiger@prod control='e:\vbbk\myin.ctl'

SQL*Loader: Release 9.2.0.6.0 - Production on Sat Nov 24 11:44:58 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 17

D:\oracle\prodora\8.0.6\BIN>


5. Check the table

select * from my_emp_stag


6. To validate the table create a procedure

(EMP_VALIDATE)



CREATE OR REPLACE PROCEDURE EMP_VALIDATE IS

CURSOR MYCUR IS SELECT * FROM MY_EMP_STAG FOR UPDATE OF FLAG;

MYREC MYCUR%ROWTYPE;

BEGIN

FOR MYREC IN MYCUR LOOP

IF MYREC.ENAME IS NULL OR MYREC.EMPNO IS NULL OR MYREC.SAL IS NULL THEN

UPDATE MY_EMP_STAG SET FLAG='N' WHERE CURRENT OF MYCUR;

ELSE

UPDATE MY_EMP_STAG SET FLAG='Y' WHERE CURRENT OF MYCUR;

END IF;

END LOOP;

END;

7. Run the validation program

BEGIN

EMP_VALIDATE;

END;

8. Check the table

select * from my_emp_stag