OraFAQ Forum: Server Utilities » Flat File Reading

Home » RDBMS Server » Server Utilities » Flat File Reading (Oracle10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Flat File Reading [message #558696]

Tue, 26 June 2012 00:38

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Hi,

please find the below attached file(NGF.dat).
I need to insert this data into multiple tables(NGF_REC_LINK,POLES_7 and SUPPORT_8).

 
 CREATE TABLE NGF_REC_LINK
(
  AREA_NAME  VARCHAR2(40),
  NGFID      NUMBER(20),
  TABLENAME  VARCHAR2(40),
  PARENT     VARCHAR2(200),
  CHILD      VARCHAR2(200)
)
 
 CREATE TABLE POLES_7
(
  AREA_NAME       VARCHAR2(50),
  NGFID           NUMBER(20),
  GRFID           NUMBER(20),
  ADDRESS         VARCHAR2(75),
  TMP_DEC2        NUMBER(10,2),
  TMP_DEC1        NUMBER(10,2),
  TMP_STR5        VARCHAR2(75 ),
  TMP_STR4        VARCHAR2(75),
  TMP_STR3        VARCHAR2(50),
  TMP_STR2        VARCHAR2(50),
  TMP_STR1        VARCHAR2(50),
  STATUS          VARCHAR2(30),
  NO_PLAN         VARCHAR2(8),
  AN_INSTALL      VARCHAR2(20),
  TYPE_DEM        VARCHAR2(20),
  TMP_DEC5        NUMBER(10,2),
  TMP_DEC4        NUMBER(10,2),
  TMP_DEC3        NUMBER(10,2),
  HOUSE_COUNT     NUMBER(4),
  PROJECT_NUMBER  VARCHAR2(12),
  MAP_NUMBER      VARCHAR2(10),
  LOCATION        VARCHAR2(15),
  POLE_TYPE       VARCHAR2(10),
  DISTANCE        NUMBER(8),
  PARC_NUMBER     VARCHAR2(10),
  POLE_NUMBER     VARCHAR2(10),
  POLE_USE_BY     VARCHAR2(10),
  HEIGHT          NUMBER(4),
  CLASS           NUMBER(4),
  PERMIT_NUMBER   VARCHAR2(15),
  POLE_OWNERSHIP  VARCHAR2(15),
  TEL_ADDRESS     VARCHAR2(20)
);

CREATE TABLE SUPPORT_8
(
  AREA_NAME            VARCHAR2(50),
  NGFID                NUMBER(20),
  GRFID                NUMBER(20),
  ATTACHMENT_TYPE      VARCHAR2(15 ),
  ANCHOR_SIZE_OR_TYPE  VARCHAR2(8 ),
  MANUFACTURER         VARCHAR2(20 ),
  FIXTURE_SIZE         VARCHAR2(8 ),
  PULL                 NUMBER(4),
  YEAR_PLACED          NUMBER(4),
  PROJECT_NUMBER       VARCHAR2(12 ),
  STATUS               VARCHAR2(30 ),
  TMP_STR2             VARCHAR2(50 ),
  AN_INSTALL           VARCHAR2(20 ),
  TMP_DEC5             NUMBER(10,2),
  TMP_DEC4             NUMBER(10,2),
  TMP_DEC3             NUMBER(10,2),
  TMP_DEC2             NUMBER(10,2),
  TMP_DEC1             NUMBER(10,2),
  TMP_STR5             VARCHAR2(75 ),
  TMP_STR4             VARCHAR2(75 ),
  TMP_STR3             VARCHAR2(50 ),
  TMP_STR1             VARCHAR2(50 ),
  NO_PLAN              VARCHAR2(8 ),
  MAP_NUMBER           VARCHAR2(12),
  GUY_SIZE             VARCHAR2(10),
  HEIGHT               NUMBER(4),
  LEAD                 NUMBER(4)
);

File Format :-

1)FIELDS TERMINATED BY ";" and space

2) 1st record start's with 25 and end's with an_install;;
2nd record start's with 817 and end's with an_install;;..etc

Note:-PARENT,CHILD,address,tel_address,pole_use_by ..etc are field names
For exp:address;; --address is field name need to skip and between to ;; is value

3)expecting output

1st record:-

INSERT INTO NGF_REC_LINK
(AREA_NAME,NGFID,TABLENAME,PARENT,CHILD)
values('ngf',25,POLES_7,null,'1401;9845075;2020')

WHEN RECNAME_RECTYPE='POLES_7' then

INSERT INTO POLES_7
(area_name,ngfid,grfid,address,tel_address,pole_use_by,service_pole,height,class,pole_ownership,permit_number,pole_number,parc_number ,distance,pole_type,location,map_number,project_number,house_count,no_plan,status,tmp_str1,tmp_str2,tmp_str3,tmp_str4,tmp_str5,
tmp_dec1,tmp_dec2,tmp_dec3,tmp_dec4,tmp_dec5,type_dem,an_install)
VALUES('ngf',25,NULL,NULL,COMMUN,NULL,0,0,HYDRO,NULL,R1W4X,NULL,0,BOIS,P1001,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,0 ,NULL)

2nd record:-

INSERT INTO NGF_REC_LINK
(AREA_NAME,NGFID,TABLENAME,PARENT,CHILD)
values('ngf',817,SUPPORT_8,NULL,NULL)

WHEN RECNAME_RECTYPE='SUPPORT_8' then

INSERT INTO SUPPORT_8
(area_name,ngfid,grfid,attachment_type,anchor_size_or_type,fixture_size,manufacturer,lead,height,pull,guy_size,spanguy,year_placed,ma p_number,project_number,no_plan, status,tmp_str1,tmp_str2,tmp_str3,tmp_str4,tmp_str5,tmp_dec1,tmp_dec2,tmp_dec3,tmp_dec4,tmp_dec5,an_install)
VALUES('ngf',817,NULL,GUY,NULL,NULL,COGECO,0,0,0,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,0,NULL)

Can you please help me.

[mod-edit: some code tags added and smilies disabled by bb, due to converting code to smilies]

Attachment: NGF.dat
(Size: 1.30KB, Downloaded 2502 times)

[Updated on: Wed, 27 June 2012 01:23] by Moderator

Report message to a moderator

Re: Flat File Reading [message #558828 is a reply to message #558696]

Tue, 26 June 2012 14:55

dragam
Messages: 31
Registered: May 2012
Location: Ireland

Member

[quote title=naveendara wrote on Tue, 26 June 2012 06:38]Hi,

> please find the below attached file(NGF.dat).

Do you have a control file for this data or do you need one?
It looks complex - maybe PL/SQL is necessary here?

Paul...

Report message to a moderator

Re: Flat File Reading [message #558858 is a reply to message #558828]

Wed, 27 June 2012 00:19

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Hi Paul,

Thanks for reply.

I need Control File.

Report message to a moderator

Re: Flat File Reading [message #558892 is a reply to message #558858]

Wed, 27 June 2012 02:42

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

In the following, I populated all of the columns in one table and some of them in the other two tables. You should be able to finish the rest.

-- ngf.dat:

NGFID;RECTYPE;RECNAME
25;7;POLES
	PARENT
	CHILD;1401;9845075;2020
	address;;
	tel_address;;
	pole_use_by;COMMUN;
	service_pole;;
	height;0;
	class;0;
	pole_ownership;HYDRO;
	permit_number;;
	pole_number;R1W4X;
	parc_number;;
	distance;0;
	pole_type;BOIS;
	location;P1001;
	map_number;;
	project_number;;
	house_count;0;
	no_plan;;
	status;;
	tmp_str1;;
	tmp_str2;;
	tmp_str3;;
	tmp_str4;;
	tmp_str5;;
	tmp_dec1;0;
	tmp_dec2;0;
	tmp_dec3;0;
	tmp_dec4;0;
	tmp_dec5;0;
	type_dem;;
	an_install;;
817;8;SUPPORT
	PARENT
	CHILD
	attachment_type;GUY;
	anchor_size_or_type;;
	fixture_size;;
	manufacturer;COGECO;
	lead;0;
	height;0;
	pull;0;
	guy_size;;
	spanguy;;
	year_placed;0;
	map_number;;
	project_number;;
	no_plan;;
	status;;
	tmp_str1;;
	tmp_str2;;
	tmp_str3;;
	tmp_str4;;
	tmp_str5;;
	tmp_dec1;0;
	tmp_dec2;0;
	tmp_dec3;0;
	tmp_dec4;0;
	tmp_dec5;0;
	an_install;;
1401;8;SUPPORT
	PARENT;25
	CHILD
	attachment_type;GUY;
	anchor_size_or_type;;
	fixture_size;;
	manufacturer;HYDRO;
	lead;0;
	height;0;
	pull;0;
	guy_size;;
	spanguy;;
	year_placed;0;
	map_number;;
	project_number;;
	no_plan;;
	status;;
	tmp_str1;;
	tmp_str2;;
	tmp_str3;;
	tmp_str4;;
	tmp_str5;;
	tmp_dec1;0;
	tmp_dec2;0;
	tmp_dec3;0;
	tmp_dec4;0;
	tmp_dec5;0;
	an_install;;

-- test.ctl:

options(skip=1)
load data
infile ngf.dat
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
fields
trailing nullcols
(area_name constant 'ngf',
ngfid terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent terminated by whitespace "ltrim (:parent, 'PARENT;')",
child terminated by whitespace "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table poles_7
when (filler1 = '7') and (filler2 = 'POLES')
fields
trailing nullcols
(area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
filler3 filler terminated by whitespace,
filler4 filler terminated by whitespace,
filler5 filler terminated by whitespace,
filler6 filler terminated by whitespace,
pole_use_by terminated by whitespace "rtrim (ltrim (:pole_use_by, 'pole_use_by;'), ';')")
into table support_8
when (filler1 = '8') and (filler2 = 'SUPPORT')
fields
trailing nullcols
(area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace)

-- tables:

SCOTT@orcl_11gR2> CREATE TABLE NGF_REC_LINK
  2  (
  3    AREA_NAME  VARCHAR2(40),
  4    NGFID	  NUMBER(20),
  5    TABLENAME  VARCHAR2(40),
  6    PARENT	  VARCHAR2(200),
  7    CHILD	  VARCHAR2(200)
  8  )
  9  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE POLES_7
  2  (
  3    AREA_NAME       VARCHAR2(50),
  4    NGFID	       NUMBER(20),
  5    GRFID	       NUMBER(20),
  6    ADDRESS	       VARCHAR2(75),
  7    TMP_DEC2        NUMBER(10,2),
  8    TMP_DEC1        NUMBER(10,2),
  9    TMP_STR5        VARCHAR2(75 ),
 10    TMP_STR4        VARCHAR2(75),
 11    TMP_STR3        VARCHAR2(50),
 12    TMP_STR2        VARCHAR2(50),
 13    TMP_STR1        VARCHAR2(50),
 14    STATUS	       VARCHAR2(30),
 15    NO_PLAN	       VARCHAR2(8),
 16    AN_INSTALL      VARCHAR2(20),
 17    TYPE_DEM        VARCHAR2(20),
 18    TMP_DEC5        NUMBER(10,2),
 19    TMP_DEC4        NUMBER(10,2),
 20    TMP_DEC3        NUMBER(10,2),
 21    HOUSE_COUNT     NUMBER(4),
 22    PROJECT_NUMBER  VARCHAR2(12),
 23    MAP_NUMBER      VARCHAR2(10),
 24    LOCATION        VARCHAR2(15),
 25    POLE_TYPE       VARCHAR2(10),
 26    DISTANCE        NUMBER(8),
 27    PARC_NUMBER     VARCHAR2(10),
 28    POLE_NUMBER     VARCHAR2(10),
 29    POLE_USE_BY     VARCHAR2(10),
 30    HEIGHT	       NUMBER(4),
 31    CLASS	       NUMBER(4),
 32    PERMIT_NUMBER   VARCHAR2(15),
 33    POLE_OWNERSHIP  VARCHAR2(15),
 34    TEL_ADDRESS     VARCHAR2(20)
 35  )
 36  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE SUPPORT_8
  2  (
  3    AREA_NAME	    VARCHAR2(50),
  4    NGFID		    NUMBER(20),
  5    GRFID		    NUMBER(20),
  6    ATTACHMENT_TYPE	    VARCHAR2(15 ),
  7    ANCHOR_SIZE_OR_TYPE  VARCHAR2(8 ),
  8    MANUFACTURER	    VARCHAR2(20 ),
  9    FIXTURE_SIZE	    VARCHAR2(8 ),
 10    PULL		    NUMBER(4),
 11    YEAR_PLACED	    NUMBER(4),
 12    PROJECT_NUMBER	    VARCHAR2(12 ),
 13    STATUS		    VARCHAR2(30 ),
 14    TMP_STR2 	    VARCHAR2(50 ),
 15    AN_INSTALL	    VARCHAR2(20 ),
 16    TMP_DEC5 	    NUMBER(10,2),
 17    TMP_DEC4 	    NUMBER(10,2),
 18    TMP_DEC3 	    NUMBER(10,2),
 19    TMP_DEC2 	    NUMBER(10,2),
 20    TMP_DEC1 	    NUMBER(10,2),
 21    TMP_STR5 	    VARCHAR2(75 ),
 22    TMP_STR4 	    VARCHAR2(75 ),
 23    TMP_STR3 	    VARCHAR2(50 ),
 24    TMP_STR1 	    VARCHAR2(50 ),
 25    NO_PLAN		    VARCHAR2(8 ),
 26    MAP_NUMBER	    VARCHAR2(12),
 27    GUY_SIZE 	    VARCHAR2(10),
 28    HEIGHT		    NUMBER(4),
 29    LEAD		    NUMBER(4)
 30  )
 31  /

Table created.

-- load:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

-- results:

SCOTT@orcl_11gR2> column area_name format a9
SCOTT@orcl_11gR2> column tablename format a9
SCOTT@orcl_11gR2> column parent    format a6
SCOTT@orcl_11gR2> column child	   format a20
SCOTT@orcl_11gR2> select * from ngf_rec_link
  2  /

AREA_NAME      NGFID TABLENAME PARENT CHILD
--------- ---------- --------- ------ --------------------
ngf               25 POLES_7          1401;9845075;2020
ngf              817 SUPPORT_8
ngf             1401 SUPPORT_8 25

3 rows selected.

SCOTT@orcl_11gR2> select area_name, ngfid, pole_use_by
  2  from   poles_7
  3  /

AREA_NAME      NGFID POLE_USE_B
--------- ---------- ----------
ngf               25 COMMUN

1 row selected.

SCOTT@orcl_11gR2> select area_name, ngfid
  2  from   support_8
  3  /

AREA_NAME      NGFID
--------- ----------
ngf              817
ngf             1401

2 rows selected.

Report message to a moderator

Re: Flat File Reading [message #558903 is a reply to message #558892]

Wed, 27 June 2012 03:42

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Thank you very much Barbara

This ctl file exactly match my requirement.

I Have one more doubt Barbara

I need to insert file name in area_name field (area_name constant 'ngf')Insted of constant 'ngf'.

Can you please help me.

Report message to a moderator

Re: Flat File Reading [message #558943 is a reply to message #558903]

Wed, 27 June 2012 11:26

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

Although you can use data=ngf.dat in your sqlldr command line instead of infile=ngf.dat in your control file, I don't know of any way to extract or pass that as a parameter value to be loaded into a column of the table. The only method that I know of is to dynamically create your SQL*Loader control file to put that value in as a constant. How you do that depends on your operating system. Alternatively, if you can find some way of getting that value into your data file, then you can read and load it from there.

Report message to a moderator

Re: Flat File Reading [message #559047 is a reply to message #558943]

Thu, 28 June 2012 04:22

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Hi barbara

ngf.Dat File

9784247;1;TAP
PARENT;9788193
CHILD;9785079
input_low_freq_high_temp;35.18;
input_low_freq_low_temp;38.69;
input_high_freq_high_temp;42.01;
input_high_freq_low_temp;40.52;
input_pilot_freq_high_temp;20.82;
input_pilot_freq_low_temp;20.62;
insertion_loss_low_freq;0.36;
insertion_loss_high_freq;0.68;
insertion_loss_pilot_freq;0.44;
output_low_freq_high_temp;34.82;
output_low_freq_low_temp;38.33;
output_high_freq_high_temp;41.33;
output_high_freq_low_temp;39.84;
output_pilot_freq_high_temp;21.26;
output_pilot_freq_low_temp;21.06;
output_tap_low_freq;12.3;
output_tap_high_freq;19.1;
display_value;23;71854559
tap_db_value;22.9;
offset_db;1;
spiggots;2;
frequency;17.7;
location;25 M;
map_number;;
manufacturer;REGAL;
model_number;RMT2-23;
project_number;EXIE;
power_block;;
calculate_db_value;;
mdu_feeding;N;
accumulated_length;0;
no_plan;;
status;E;63379063
tmp_str1;REGAL;
tmp_str2;EXIE;
tmp_str3;;
tmp_str4;;
tmp_str5;;
tmp_dec1;23;
tmp_dec2;2;
tmp_dec3;0;
tmp_dec4;0;
tmp_dec5;43.72;
nb_drop;0;
an_install;;

Table description TAP_1

CREATE TABLE TAP_1
(
AREA_SRNO NUMBER(20),
AREA_NAME VARCHAR2(50 ),
NGFID NUMBER(20),
GRFID NUMBER(20),
INPUT_LOW_FREQ_HIGH_TEMP NUMBER(10,2),
INPUT_LOW_FREQ_LOW_TEMP NUMBER(10,2),
INPUT_HIGH_FREQ_HIGH_TEMP NUMBER(10,2),
INPUT_HIGH_FREQ_LOW_TEMP NUMBER(10,2),
INPUT_PILOT_FREQ_HIGH_TEMP NUMBER(10,2),
INPUT_PILOT_FREQ_LOW_TEMP NUMBER(10,2),
INSERTION_LOSS_LOW_FREQ NUMBER(10,2),
INSERTION_LOSS_HIGH_FREQ NUMBER(10,2),
INSERTION_LOSS_PILOT_FREQ NUMBER(10,2),
OUTPUT_LOW_FREQ_HIGH_TEMP NUMBER(10,2),
OUTPUT_LOW_FREQ_LOW_TEMP NUMBER(10,2),
OUTPUT_HIGH_FREQ_HIGH_TEMP NUMBER(10,2),
OUTPUT_HIGH_FREQ_LOW_TEMP NUMBER(10,2),
OUTPUT_PILOT_FREQ_HIGH_TEMP NUMBER(10,2),
OUTPUT_PILOT_FREQ_LOW_TEMP NUMBER(10,2),
OUTPUT_TAP_LOW_FREQ NUMBER(9,1),
OUTPUT_TAP_HIGH_FREQ NUMBER(9,1),
DISPLAY_VALUE VARCHAR2(25 ),
TAP_DB_VALUE NUMBER(9,1),
OFFSET_DB NUMBER(10,2),
SPIGGOTS NUMBER(2),
FREQUENCY NUMBER(9,1),
LOCATION VARCHAR2(15 ),
MAP_NUMBER VARCHAR2(10 ),
MANUFACTURER VARCHAR2(20 ),
MODEL_NUMBER VARCHAR2(25 ),
PROJECT_NUMBER VARCHAR2(30 ),
POWER_BLOCK VARCHAR2(30 ),
ACCUMULATED_LENGTH VARCHAR2(10 ),
NO_PLAN VARCHAR2(20 ),
STATUS VARCHAR2(30 ),
TMP_STR1 VARCHAR2(50 ),
TMP_STR2 VARCHAR2(50 ),
TMP_STR3 VARCHAR2(50 ),
TMP_STR4 VARCHAR2(75 ),
TMP_STR5 VARCHAR2(75 ),
TMP_DEC1 NUMBER(10,2),
TMP_DEC2 NUMBER(10,2),
TMP_DEC3 NUMBER(10,2),
TMP_DEC4 NUMBER(10,2),
TMP_DEC5 NUMBER(10,2),
NB_DROP NUMBER(10,2),
AN_INSTALL VARCHAR2(15 ),
CALCULATE_DB_VALUE VARCHAR2(5 ),
MDU_FEEDING VARCHAR2(5 )
)

CTL File :-

options(skip=1)
load data
infile ngf.dat
replace
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.nextval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent terminated by whitespace "ltrim (:parent, 'PARENT;')",
child terminated by whitespace "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table poles_7
when (filler1 = '7') and (filler2 = 'POLES')
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.currval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
filler3 filler terminated by whitespace,
filler4 filler terminated by whitespace,
address terminated by whitespace "rtrim (ltrim (:address, 'address;'), ';')")
into table support_8
when (filler1 = '8') and (filler2 = 'SUPPORT')
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.currval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
filler3 filler terminated by whitespace,
filler4 filler terminated by whitespace,
attachment_type terminated by whitespace "rtrim (ltrim (:attachment_type, 'attachment_type;'), ';')",
anchor_size_or_type terminated by whitespace "rtrim (ltrim (:anchor_size_or_type, 'anchor_size_or_type;'), ';')")
into table tap_1
when (filler1 = '1') and (filler2 = 'TAP')
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.currval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
filler3 filler terminated by whitespace,
filler4 filler terminated by whitespace,
input_low_freq_high_temp terminated by whitespace "rtrim(ltrim(:input_low_freq_high_temp,'input_low_freq_high_temp;'),';')")

Record 1: Rejected - Error on table TAP_1, column INPUT_LOW_FREQ_HIGH_TEMP.
ORA-01722: invalid number
I am getting this error while executing ctl file

I have tried like this also

input_low_freq_high_temp decimal external terminated by whitespace "rtrim(ltrim(:input_low_freq_high_temp,'input_low_freq_high_temp;'),';')"

Can you please help me.

Report message to a moderator

Re: Flat File Reading [message #559050 is a reply to message #559047]

Thu, 28 June 2012 04:33

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Flat File Reading [message #559054 is a reply to message #559050]

Thu, 28 June 2012 04:51

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Sorry for that

Report message to a moderator

Re: Flat File Reading [message #559055 is a reply to message #559054]

Thu, 28 June 2012 04:51

naveendara
Messages: 11
Registered: June 2012
Location: Hyderabad

Junior Member

Hi barbara

ngf.Dat File

9784247;1;TAP
PARENT;9788193
CHILD;9785079
input_low_freq_high_temp;35.18;

Table description TAP_1

CREATE TABLE TAP_1
(
AREA_SRNO NUMBER(20),
AREA_NAME VARCHAR2(50 ),
NGFID NUMBER(20),
GRFID NUMBER(20),
INPUT_LOW_FREQ_HIGH_TEMP NUMBER(10,2),)

CTL File :-

options(skip=1)
load data
infile ngf.dat
replace
continueif next preserve (1:1) = x'09'
into table ngf_rec_link
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.nextval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
parent terminated by whitespace "ltrim (:parent, 'PARENT;')",
child terminated by whitespace "ltrim (:child, 'CHILD;')",
tablename ":filler2 || '_' || :filler1")
into table tap_1
when (filler1 = '1') and (filler2 = 'TAP')
fields
trailing nullcols
(area_srno "ngf_area_srno_seq.currval",
area_name constant 'ngf',
ngfid position (1) terminated by ';',
filler1 boundfiller terminated by ';',
filler2 boundfiller terminated by whitespace,
filler3 filler terminated by whitespace,
filler4 filler terminated by whitespace,
input_low_freq_high_temp terminated by whitespace "rtrim(ltrim(:input_low_freq_high_temp,'input_low_freq_high_temp;'),';')")

Record 1: Rejected - Error on table TAP_1, column INPUT_LOW_FREQ_HIGH_TEMP.
ORA-01722: invalid number
I am getting this error while executing ctl file

I have tried like this also

input_low_freq_high_temp decimal external terminated by whitespace "rtrim(ltrim(:input_low_freq_high_temp,'input_low_freq_high_temp;'),';')"

input_low_freq_high_temp terminated by whitespace "to_number(rtrim(ltrim(:input_low_freq_high_temp,'input_low_freq_high_temp;'),';'),'999999999.99')"

Can you please help me.

Report message to a moderator

Re: Flat File Reading [message #559058 is a reply to message #559055]

Thu, 28 June 2012 05:29

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you really sorry?

Michel Cadot wrote on Thu, 28 June 2012 11:33

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Flat File Reading [message #559092 is a reply to message #559055]

Thu, 28 June 2012 13:22

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

There are a lot of things that are missing or don't match or are posted without preserving formatting, so that I cannot tell what they actually are.

When you post your ngf.dat file, you need to either post it within code tags or as an attachment, otherwise it destroys the spacing. Without preserving the formatting, I cannot tell if there are tabs at the beginnings of the lines or not. If there are no tabs, then the following will not work:

continueif next preserve (1:1) = x'09'

Your control file does not match your previous definition of the ngf_rec_link table. For example, it does not contain any area_srno column. So, you need to provide a new create table statement.

Your control file uses options(skip=1) to skip the first (header) line, but in your new ngf.dat file, there is no header line to skip.

Your control file uses a sequence, but you have not provided that.

You may be getting the error because it is attempting to read the wrong column, due to other problems.

Please try to post a complete correct test case, providing all of the missing items. It may be better to post the whole actual thing, as you did before, than try to extract pieces.

Report message to a moderator

Re: Flat File Reading [message #559113 is a reply to message #559092]

Thu, 28 June 2012 15:23

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

UNRECOVERABLE Load Data
Append
INTO TABLE SCOTT.TAP_1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
AREA_SRNO decimal external nullif AREA_SRNO=blanks
,AREA_NAME char(50) nullif AREA_NAME=blanks
,NGFID decimal external nullif NGFID=blanks
,GRFID decimal external nullif GRFID=blanks
,INPUT_LOW_FREQ_HIGH_TEMP decimal external nullif INPUT_LOW_FREQ_HIGH_TEMP=blanks
,INPUT_LOW_FREQ_LOW_TEMP decimal external nullif INPUT_LOW_FREQ_LOW_TEMP=blanks
,INPUT_HIGH_FREQ_HIGH_TEMP decimal external nullif INPUT_HIGH_FREQ_HIGH_TEMP=blanks
,INPUT_HIGH_FREQ_LOW_TEMP decimal external nullif INPUT_HIGH_FREQ_LOW_TEMP=blanks
,INPUT_PILOT_FREQ_HIGH_TEMP decimal external nullif INPUT_PILOT_FREQ_HIGH_TEMP=blanks
,INPUT_PILOT_FREQ_LOW_TEMP decimal external nullif INPUT_PILOT_FREQ_LOW_TEMP=blanks
,INSERTION_LOSS_LOW_FREQ decimal external nullif INSERTION_LOSS_LOW_FREQ=blanks
,INSERTION_LOSS_HIGH_FREQ decimal external nullif INSERTION_LOSS_HIGH_FREQ=blanks
,INSERTION_LOSS_PILOT_FREQ decimal external nullif INSERTION_LOSS_PILOT_FREQ=blanks
,OUTPUT_LOW_FREQ_HIGH_TEMP decimal external nullif OUTPUT_LOW_FREQ_HIGH_TEMP=blanks
,OUTPUT_LOW_FREQ_LOW_TEMP decimal external nullif OUTPUT_LOW_FREQ_LOW_TEMP=blanks
,OUTPUT_HIGH_FREQ_HIGH_TEMP decimal external nullif OUTPUT_HIGH_FREQ_HIGH_TEMP=blanks
,OUTPUT_HIGH_FREQ_LOW_TEMP decimal external nullif OUTPUT_HIGH_FREQ_LOW_TEMP=blanks
,OUTPUT_PILOT_FREQ_HIGH_TEMP decimal external nullif OUTPUT_PILOT_FREQ_HIGH_TEMP=blanks
,OUTPUT_PILOT_FREQ_LOW_TEMP decimal external nullif OUTPUT_PILOT_FREQ_LOW_TEMP=blanks
,OUTPUT_TAP_LOW_FREQ decimal external nullif OUTPUT_TAP_LOW_FREQ=blanks
,OUTPUT_TAP_HIGH_FREQ decimal external nullif OUTPUT_TAP_HIGH_FREQ=blanks
,DISPLAY_VALUE char(25) nullif DISPLAY_VALUE=blanks
,TAP_DB_VALUE decimal external nullif TAP_DB_VALUE=blanks
,OFFSET_DB decimal external nullif OFFSET_DB=blanks
,SPIGGOTS decimal external nullif SPIGGOTS=blanks
,FREQUENCY decimal external nullif FREQUENCY=blanks
,LOCATION char(15) nullif LOCATION=blanks
,MAP_NUMBER char(10) nullif MAP_NUMBER=blanks
,MANUFACTURER char(20) nullif MANUFACTURER=blanks
,MODEL_NUMBER char(25) nullif MODEL_NUMBER=blanks
,PROJECT_NUMBER char(30) nullif PROJECT_NUMBER=blanks
,POWER_BLOCK char(30) nullif POWER_BLOCK=blanks
,ACCUMULATED_LENGTH char(10) nullif ACCUMULATED_LENGTH=blanks
,NO_PLAN char(20) nullif NO_PLAN=blanks
,STATUS char(30) nullif STATUS=blanks
,TMP_STR1 char(50) nullif TMP_STR1=blanks
,TMP_STR2 char(50) nullif TMP_STR2=blanks
,TMP_STR3 char(50) nullif TMP_STR3=blanks
,TMP_STR4 char(75) nullif TMP_STR4=blanks
,TMP_STR5 char(75) nullif TMP_STR5=blanks
,TMP_DEC1 decimal external nullif TMP_DEC1=blanks
,TMP_DEC2 decimal external nullif TMP_DEC2=blanks
,TMP_DEC3 decimal external nullif TMP_DEC3=blanks
,TMP_DEC4 decimal external nullif TMP_DEC4=blanks
,TMP_DEC5 decimal external nullif TMP_DEC5=blanks
,NB_DROP decimal external nullif NB_DROP=blanks
,AN_INSTALL char(15) nullif AN_INSTALL=blanks
,CALCULATE_DB_VALUE char(5) nullif CALCULATE_DB_VALUE=blanks
,MDU_FEEDING char(5) nullif MDU_FEEDING=blanks
)

Report message to a moderator

Previous Topic:	impdp and database open mode (split from Moving from one Windows environment to another Linux environment)
Next Topic:	ARGUMENT segment taken more space out of total system space

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:36:27 CST 2025