loading data
my bad, i look like lost my knowledge in oracle, its because i worked in it a year ago. now i seem lost my skills!
theres a flat file that has data like,
32,'a',43,'bakre'
32,'a',43,'bakrerea'
32,'a',43,'bakreare'
sql loader loads this data and life is happy. but the problem is when an unprintable character comes in the feed in those varchar2 portions and if there is a line feed whole thing is screwed. how do we avoid it?
the feed contains lot of tab and new line and character returns.
please note that,
1. you should not suggest me to strip off the characters while exporting itself (because i dont do that, its coming from the db2 by a third party)
2. sometimes the data for a record comes in two lines because of the line feed.
how should i approach this please.
theres a flat file that has data like,
32,'a',43,'bakre'
32,'a',43,'bakrerea'
32,'a',43,'bakreare'
sql loader loads this data and life is happy. but the problem is when an unprintable character comes in the feed in those varchar2 portions and if there is a line feed whole thing is screwed. how do we avoid it?
the feed contains lot of tab and new line and character returns.
please note that,
1. you should not suggest me to strip off the characters while exporting itself (because i dont do that, its coming from the db2 by a third party)
2. sometimes the data for a record comes in two lines because of the line feed.
how should i approach this please.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
guys please give me the code snippet if you dont consider it as a homework. Just to brush up my coding skill.
yes line feed and chariage returns are pretty much everywhere in the comments field. when this comment is being inserted to the table, it fails. i cant replace also for your kind consideration.
yes line feed and chariage returns are pretty much everywhere in the comments field. when this comment is being inserted to the table, it fails. i cant replace also for your kind consideration.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>yes line feed and chariage returns are pretty much everywhere in the comments field. when this comment is being inserted to the table
Ok. I got it ram_0218. Here is the solution for you:
Ok. I got it ram_0218. Here is the solution for you:
use "stream record format".
Assume:
1. you are working on windows platform and each line ends with "\r\n". If you are in unix and line ends with "\n", you only need to change it a bit.
2. your last column is comment column enclosed by '.
here is a small example for you:
///test table///
create table loadertest
(col1 number,
col2 varchar2(1000)
)
/
///////////////////control
LOAD DATA
INFILE loadertest.dat "str '\'\r\n'"
REPLACE
INTO table loadertest
fields terminated by ","
(
col1 integer external,
col2 "ltrim(:col2,'''')"
)
//////////loadertest.dat//
43,'bakreteste
bakrenext
thirdline'
44,'bakrerea'
45,'bakreare'
Assume:
1. you are working on windows platform and each line ends with "\r\n". If you are in unix and line ends with "\n", you only need to change it a bit.
2. your last column is comment column enclosed by '.
here is a small example for you:
///test table///
create table loadertest
(col1 number,
col2 varchar2(1000)
)
/
///////////////////control
LOAD DATA
INFILE loadertest.dat "str '\'\r\n'"
REPLACE
INTO table loadertest
fields terminated by ","
(
col1 integer external,
col2 "ltrim(:col2,'''')"
)
//////////loadertest.dat//
43,'bakreteste
bakrenext
thirdline'
44,'bakrerea'
45,'bakreare'
after execuing sql*loader, you will see:
SQL> select * from loadertest;
COL1
----------
COL2
--------------------------
43
bakreteste
bakrenext
thirdline
44
bakrerea
45
COL1
----------
COL2
--------------------------
bakreare
3 rows selected.
SQL> select * from loadertest;
COL1
----------
COL2
--------------------------
43
bakreteste
bakrenext
thirdline
44
bakrerea
45
COL1
----------
COL2
--------------------------
bakreare
3 rows selected.
if you want to know more details, go to look:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1004653
search for "Stream Record Format"
Enjoy:)
Acton
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1004653
search for "Stream Record Format"
Enjoy:)
Acton
btw: if you are in unix box, you might need to replace:
INFILE loadertest.dat "str '\'\r\n'"
by
INFILE loadertest.dat "str '\'\n'"
Acton
INFILE loadertest.dat "str '\'\r\n'"
by
INFILE loadertest.dat "str '\'\n'"
Acton
Two solutions are
1) correct the flat file using C program of using UTL_FILE in PL/SQL
2) Load data to a temporary table and using PL/SQL parse the delimited fields and insert to table
1) correct the flat file using C program of using UTL_FILE in PL/SQL
2) Load data to a temporary table and using PL/SQL parse the delimited fields and insert to table
>>1) correct the flat file using C program of using UTL_FILE in PL/SQL
no error in flat file. How do you fix?
>>2) Load data to a temporary table and using PL/SQL parse the delimited fields and insert to table
No need. you face the same problem.
MK, see my solution. It is already there.
Acton
no error in flat file. How do you fix?
>>2) Load data to a temporary table and using PL/SQL parse the delimited fields and insert to table
No need. you face the same problem.
MK, see my solution. It is already there.
Acton
ASKER
i simply finished the task using continueif. but i do have another question related to this, that i posted as a new question.
now coming to points,
acton: i'll give him the most as he pointed out str function from where i ended catching up continueif
abd00n : though its an orthodox approach of solving this pbm, i would give him points for the time and effort
now coming to points,
acton: i'll give him the most as he pointed out str function from where i ended catching up continueif
abd00n : though its an orthodox approach of solving this pbm, i would give him points for the time and effort
You mean a CHR(10) appears inside varchar2 value?