[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1389
  • Last Modified:

CLOB to VARCHAR2 conversion

I have a table in MySQL database. One of the fields of the table is of "TEXT" data type (in MySQL).

I need to copy the table to an Oracle 11gR2 database. I have a cron process to dump the above table content to a tab separated text file. Then I run sqlldr to populate the table in Oracle. THis worked fine so far with other tables where there was no "TEXT" data type in the source MySQL database table.

If I create the Oracle target table field VARCHAR2(4000), half the record does not get inserted -- error shows that input value is too big for this  VARCHAR2(4000) Oracle field. If I change the target table (Oracle) field to CLOB (jnstead of VARCHAR2(4000), still I get the same error.

I looked at the content of the failed rows -- none has more than few hundred characters in it.

If I use Oracle Developer IDE (that has the Source MySQL to target Oracle data conversion wizard) -- the wizard creates the target table field in Oracle as CLOB and all source rows get imported!! But I cannot use this IDE as I need to schedule a process to keep the target Oracle table in sync.

How can I make it work...  
0
toooki
Asked:
toooki
  • 14
  • 11
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Please post your table definition, sqlldr control file and sample data.
0
 
slightwv (䄆 Netminder) Commented:
Going from memory and until I see your control file I think you can set the column in the controlfile to something like: char(100000) for a clob column.
0
 
toookiAuthor Commented:
Thank you.

I have attached the control file.

Also attached the source Table definition:

F1       INT
F2      TIMESTAMP
F3      TEXT
F4      INT
F5      VARCHAR


 
OPTIONS (SKIP = 1, ERRORS = 1000, ROWS = 66796)
load data
 infile '/mydir/.../F1.txt'
 TRUNCATE
 INTO TABLE MyTab
 fields terminated by X'09'
 TRAILING NULLCOLS
(
F1,
F2 DATE 'yyyy-mm-dd hh24:mi:ss',
F3,
F4,
F5
)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
Int and text aren't Oracle types.

Please provide the Oracle definition and still need some sample data that shows the error.
0
 
mrjoltcolaCommented:
INT is an alias for NUMBER(38) in Oracle.
0
 
toookiAuthor Commented:
Thanks. Those are the Source MySQL server table fields' data types.

Here are the corresponding Oracle target table's datatype:
F1 Number(10)
F2 DATE
F3 CLOB
F4 Number(10)
F5 VARCHAR2(40 CHAR)

I attached two sample lines of the input record from the dump file. TESTdata.txt
0
 
slightwv (䄆 Netminder) Commented:
Works for me.  I used inline data from your sample (the tabs were removed but I added them back in).


Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
F1                                  FIRST     *  WHT      CHARACTER            
F2                                   NEXT     *  WHT      DATE yyyy-mm-dd hh24:mi:ss
F3                                   NEXT *****  WHT      CHARACTER            
    Maximum field length is 10000000
F4                                   NEXT     *  WHT      CHARACTER            
F5                                   NEXT     *  WHT      CHARACTER            

value used for ROWS parameter changed from 66796 to 1

Table MYTAB:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:               10001036 bytes(1 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue May 10 21:10:58 2011
Run ended on Tue May 10 21:10:58 2011

Open in new window

drop table mytab purge;
create table mytab(
F1 Number(10),
F2 DATE,
F3 CLOB,
F4 Number(10),
F5 VARCHAR2(40 CHAR)
);

Open in new window

OPTIONS (SKIP = 1, ERRORS = 1000, ROWS = 66796)
load data
 infile *
 TRUNCATE
 INTO TABLE MyTab
 fields terminated by X'09'
 TRAILING NULLCOLS
(
F1,
F2 DATE 'yyyy-mm-dd hh24:mi:ss',
F3 char(10000000),
F4,
F5
)
begindata
some header row
1000080	2009-09-15 19:38:24	My Set 2: Abandoned  According to ME(Myname), me/frameworks/base/test is obsolete in TEST3.0, and the valid place should be test/apps. So I abandon this another one to new test/RdcService	34	AAAABn///4o=
1000282	2009-09-15 13:41:06	My Set 3: Abandoned  2: Fails; Do not submit   refers to test 1 (/tests/40/40/1). test 1 has been integrated in platform baseline through test while this test set was not approved in test 2 cannot be integrated as it was not mentioned in the test   Please test3	40	AAAABn///6I=

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>INT is an alias for NUMBER(38) in Oracle.

Thanks.  Good to know.
0
 
slightwv (䄆 Netminder) Commented:
My guess is it is the CHAR for the column I mentioned in http:#a35734385
0
 
toookiAuthor Commented:
Thank you....
With that change:

F3 char(10000000),

I see most my records get imported. Some failed but only few compared to before.

But I did not notice before that the sqldr stops after importing about 20K lines. But the text file has about 920K records. Is there something I need to change in the control file for it.?

I see failure if I have a sample input line:
NULL    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=

It says:
Record 18: Rejected - Error on table MyTab, column F1.
ORA-01722: invalid number

Is there any easy way to fix...? (Shall I ask a new question..?) Many thanks as the original issue mostly got resolved.
0
 
slightwv (䄆 Netminder) Commented:
Still the same question for now. You told sqlldr to stop after so many rows or errors in the OPTIONS line.

Check the failed row in the original file.  My guess is it contains an embedded tab character.

You might need to change your extract piece.

0
 
slightwv (䄆 Netminder) Commented:
There is a 'optionally enclosed by' parameter in the controlfile that will allow you to preserve embedded delimiters like tabs but you will need to add the optional character to the extract file.
0
 
toookiAuthor Commented:
Thank you.
Right it seems it stops because the number of error exceeds 1000 (as set in control file). OK

Most failed records are because of those lines starting will "NULL"  string (where data type number is expected)...
0
 
toookiAuthor Commented:
No there is no \t or \n characters for the troubled field F3 in the source text file.
I used the sed command to change "/t" and "/n" values to space before I run the sqlldr.
0
 
slightwv (䄆 Netminder) Commented:
>>Most failed records are because of those lines starting will "NULL"  string (where data type number is expected)...

blank lines or null fields?  Do you have not null constraints in the table?

Post a failing record and I'll try to reproduce the error.
0
 
toookiAuthor Commented:
Actually if the source (MySQL) has the null value (no value) for the first field (F1), the dump file puts a string value of "NULL" in the text file (dump file).It should have been no values isnted of this "NULL" string.

One such same input record in the text file is:
NULL    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=
(bad one)

123    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=
(good one)

Maybe I will try in the sql query I use on on MySQL that creates the dump file -- so that the output puts nothing for the field when empty.
0
 
toookiAuthor Commented:
NULL    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=

Yes this is one failing record.

No there is no constraint. But it as expected complains if I try to insert "NULL" or any other string in a NUMBER field....
0
 
slightwv (䄆 Netminder) Commented:
Change the controlfile for F1 to:

...
F1 "decode(:F1,'NULL',null)",
...
0
 
toookiAuthor Commented:
Thanks.

But if I do
F1 "decode(:F1,'NULL',null)",

All values at Oracle table for F1 field is having null values (no values) after sqlldr loads
0
 
slightwv (䄆 Netminder) Commented:
What do you want to have happen?
0
 
slightwv (䄆 Netminder) Commented:
If you want to 'skip' them and not log as an error check out the WHEN clause in the docs.

Away from keyboard and can't test now but try:

WHEN f1 != 'NULL'
0
 
toookiAuthor Commented:
If the input has a value for F1 filed (most cases it will have number a value):
For EX:
123    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=

Then the sqlldr needs to put the value of 123 in the Oracle table F1 field.

If the Input record has the value
NULL    2010-05-13 05:00:02     Your change could not be merged due to a path conflict.      6096    AAAAJn//94E=

Then the sqlldr needs to put null value (no value) in the Oracle table F1 field.

Now it is putting null value (no value) for F1 field values in the Oracle table...
(if I put F1 "decode(:F1,'NULL',null)", in the control file)

0
 
slightwv (䄆 Netminder) Commented:
Ok, then it is doing what you want?
0
 
slightwv (䄆 Netminder) Commented:
Sorry...  I get what you are saying.

Try:

 F1 "decode(:F1,'NULL',null,:F1)",
0
 
toookiAuthor Commented:
Thank slightwv, I am going to try that next.
0
 
toookiAuthor Commented:
F1 "decode(:F1,'NULL',null,:F1)",
The above worked perfectly.
Many thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now