Re: dbf --> Oracle Tables

From: Valery Yourinsky <vsu_at_softexpress.ru>
Date: Thu, 14 Jan 1999 13:23:29 +0300
Message-ID: <369dc58a.0@mail>

Rex writes...
>
>I must load Oracle Tables from dbf files, and I have little program for
>that. I have approx. 250 files in dbf format and the same Oracle tables.

>...
>
>Can I do it from Oracle Loader ( I suppose yes)?
>What is the control file for loader?

 I think it helps you:

CREATE TABLE CT(
   N_TR NUMBER(8,0),
   G_CODE NUMBER(5,0),
   CODE_PR VARCHAR2(7),
   G_ORD NUMBER(8,0),
   N_ORD NUMBER(8,0),

   AFF NUMBER(2,0),
   P_RBL NUMBER(11,2),
   A_RBL NUMBER(11,2),

   P_USD NUMBER(9,2),
   A_USD NUMBER(9,2),
   DATE_TR DATE,
   P_RB NUMBER(11,2),
   P_REC NUMBER(11,2),

   PAGE NUMBER(5,0),
   P_US NUMBER(9,2),
   P_REC_U NUMBER(9,2),
   COMPL NUMBER(1)  -- Ex LOGICAL
      CONSTRAINT CT#COMPL#TF CHECK (COMPL IN (0, 1)),

   PC NUMBER(5,0),
   I_TR NUMBER(1) -- Ex LOGICAL

      CONSTRAINT CT#I_TR#TF CHECK (INSHOR_TAR IN (0, 1)),    INV NUMBER(1) -- Ex LOGICAL

      CONSTRAINT CT#INV#TF CHECK (INV IN (0, 1)) )
/

OPTIONS (ROWS=1000, ERRORS=0)
LOAD DATA CHARACTERSET your_dbf_files_Óode_page /* WE8ISO8859P1 for example) */
INFILE "CT.DBF" "DB3 156"
REPLACE
INTO TABLE CT
WHEN(1)=' ' /* No deleted rows loading */ (
 N_TR POSITION(2:9) INTEGER EXTERNAL
,G_CODE POSITION(10:14) INTEGER EXTERNAL
,CODE_P POSITION(15:21) CHAR
,G_ORD POSITION(22:29) INTEGER EXTERNAL
,N_ORD POSITION(30:37) INTEGER EXTERNAL
,AFF POSITION(38:39) INTEGER EXTERNAL
,P_RBL POSITION(40:50) DECIMAL EXTERNAL
,A_RBL POSITION(51:61) DECIMAL EXTERNAL
,P_USD POSITION(62:70) DECIMAL EXTERNAL
,A_USD POSITION(71:79) DECIMAL EXTERNAL
,DATE_TR POSITION(80:87) DATE 'YYYYMMDD' NULLIF (DATE_TR =
BLANKS)
,P_RB POSITION(88:98) DECIMAL EXTERNAL
,P_REC POSITION(99:109) DECIMAL EXTERNAL
,PAGE POSITION(110:114) INTEGER EXTERNAL
,P_US POSITION(115:123) DECIMAL EXTERNAL
,P_REC_U POSITION(124:132) DECIMAL EXTERNAL
,COMPL POSITION(133:133) CHAR "DECODE(:COMPL,'T',1,'F',0,NULL)"
,PC POSITION(134:138) INTEGER EXTERNAL
,I_TR POSITION(139:139) CHAR "DECODE(:I_TR,'T',1,'F',0,NULL)"
,INV POSITION(140:140) CHAR "DECODE(:INV,'T',1,'F',0,NULL)"
)

Valery Yourinsky Received on Thu Jan 14 1999 - 04:23:29 CST