If you're using a UNIX environment for a transfer of SyBase to Oracle, the following has been tested and proven successful in this endeavor.
===> Your UNIX script :::
#
# Pulling of Sybase data
#
isql -U<userid> -P<password> -S<sybase system> < some_code_view.sql
bcp dbo.some_code_ora out <directory>/some_code.dat -U<userid> -P<password> -S<sybase system> -c -t '|' -e some_code.err -b 15000
#
# Loading of Pulled Sybase data to Oracle
#
sqlldr userid=<userid>/<password> control=some_code.ctl log=some_code.log
===> some_code_view.sql :::
IF OBJECT_ID('dbo.some_code_ora') IS NOT NULL
BEGIN
DROP VIEW dbo.some_code_ora
IF OBJECT_ID('dbo.some_code_ora') IS NOT NULL
PRINT '<<< FAILED DROPPING VIEW dbo.some_code_ora >>>'
ELSE
PRINT '<<< DROPPED VIEW dbo.some_code_ora >>>'
END
go
SETUSER 'dbo'
go
create view some_code_ora as
select
(convert(char(11),isnull(beg_dte,getdate()),106) +" "+
convert(char(8), isnull(beg_dte,getdate()), 8)) as beg_dte, (convert(char(11),isnull(end_dte,getdate()),106) +" "+
convert(char(8), isnull(end_dte,getdate()), 8)) as end_dte,
loc_cde,
loc_desc,
dept_nbr,
(convert(char(11),getdate(),106) +" "+
convert(char(8),getdate(),8)) as timestamp
from some_code_db.dbo.some_code
go
SETUSER
go
IF OBJECT_ID('dbo.some_code_ora') IS NOT NULL
PRINT '<<< CREATED VIEW dbo.some_code_ora >>>'
ELSE
PRINT '<<< FAILED CREATING VIEW dbo.some_code_ora >>>'
go
===> some_code.ctl :::
LOAD DATA
INFILE '<directory>/some_code.dat'
BADFILE '<directory>/some_code.bad'
TRUNCATE INTO TABLE SOME_CODE FIELDS TERMINATED BY '|'
(
beg_dte DATE "DD MON YYYY HH24:MI:SS",
end_dte DATE "DD MON YYYY HH24:MI:SS",
loc_cde,
loc_desc,
dept_nbr integer external,
TIMESTAMP DATE "DD Mon YYYY HH24:MI:SS"
)
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.