CODEWITHSUNDEEP
sqloracle-databasesql-loader
chikun
chikun

Reputation: 209

Sybase bcp data type date and text to oracle database using sqlldr

I am trying to migrate a table from sybase database to Oracle database. Following is the structure of my sybase database.

enter image description here

I have used bcp to extract all records. My extracted records as below -

1|0.1|1/7/2010 12:00:00 AM|<exm><id>1</id></exm> 
2|0.2|1/8/2010 12:00:00 AM|<exm><id>1</id></exm>

I am using following sqlldr command to insert records into oracle database.

sqlldr \'username/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.com)(PORT=1111)))(CONNECT_DATA=(SID=MYSIDE)(SERVER=DEDICATED)))\' control=loader.ctl log=mylog.log

This is my loader file(loader.ctl)

LOAD DATA 
INLINE '/tmp/bcp_out.txt'
INTO TABLE MY_TABLE
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(ID,
VERSION,
DATE,
STATEMENT CHAR(10000))

Table created in Oracle - enter image description here

So from the above table, we want the date coming from sybase to be converted to oracle timestamp and statement to be converted as Oracle CLOB.

Could you please suggest what changes I have to make in loader file to achieve my goal?

Upvotes: 0

Views: 589

Answers (2)

Littlefoot
Littlefoot

Reputation: 143103

Target table (in Oracle; note that date is an invalid column name (unless enclosed into double quotes, but I wouldn't recommend that)):

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 VERSION                                            VARCHAR2(5)
 DATUM                                              TIMESTAMP(6)
 STATEMENT                                          CLOB

SQL>

Control file (sample data included; you'll probably have it in a separate file; also, I don't know what 1/7/2010 really represents - is it 1st of July or 7th of January - date format might need to be changed):

load data 
infile *
replace
into table test
fields terminated by '|'
trailing nullcols
( 
id,
version,
datum "to_date(:datum, 'dd/mm/yyyy hh:mi:ss am')",
statement
)

begindata
1|0.1|1/7/2010 12:00:00 AM|<exm><id>1</id></exm> 
2|0.2|1/8/2010 12:00:00 AM|<exm><id>1</id></exm>

Loading session and the result:

SQL> $sqlldr scott/tiger control=test22.ctl log=test22.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Lis 13 18:16:41 2020

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
    
SQL> -- you don't have to do that; I'm just showing *what is what* in the timestamp
SQL> alter session set nls_timestamp_format = 'dd.mm.yyyy hh:mi:ss.ff6';

Session altered.

SQL> select * from test;

        ID VERSION DATUM                          STATEMENT
---------- ------- ------------------------------ ------------------------------
         1 0.1     01.07.2010 12:00:00.000000 AM  <exm><id>1</id></exm>
         2 0.2     01.08.2010 12:00:00.000000 AM  <exm><id>1</id></exm>


SQL>

As of another date format:

SQL> select to_timestamp('1 Mon 2020 12:00:00:000 am', 'dd Mon yyyy hh:mi:ss:ff am') from dual;

TO_TIMESTAMP('1SIJ202012:00:00:000AM','DDMONYYYYHH:MI:SS:FFAM')
---------------------------------------------------------------------------
01.01.20 00:00:00,000000000

SQL>

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

Do you really need the oracle data type to be TIMESTAMP? From what I see DATE would be sufficient. In oracle, unlike some other rdbms, a datatype of DATE includes time, down to the second. In oracle TIMESTAMP includes date and time down to the nano-second and can also have timezone variants.

The documentation gives a very good example of dealing with dates in your input csv file. Adapting that to your situation:

LOAD DATA 
INLINE '/tmp/bcp_out.txt'
INTO TABLE MY_TABLE
FIELDS TERMINATED BY '|'
DATE FORMAT "mm/dd/yyyy hh12:mi:ss AM"
TRAILING NULLCOLS
(ID,
VERSION,
DATE,
STATEMENT CHAR(10000))

Further explanations of the date format model here.

As for the clob, I can't help there. Never done it myself, but I'd start by looking at the docs for sqlldr.

Upvotes: 1

Related Questions