From Sybase to Oracle

[artik]

Is it possible to copy tables (#10 tables) from a sybase DB to an Oracle DB without going through text files ?

 

[HerbLR]

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) +&quot; &quot;+
convert(char(8), isnull(beg_dte,getdate()), 8)) as beg_dte, (convert(char(11),isnull(end_dte,getdate()),106) +&quot; &quot;+
convert(char(8), isnull(end_dte,getdate()), 8)) as end_dte,
loc_cde,
loc_desc,
dept_nbr,
(convert(char(11),getdate(),106) +&quot; &quot;+
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 &quot;DD MON YYYY HH24:MI:SS&quot;,
end_dte DATE &quot;DD MON YYYY HH24:MI:SS&quot;,
loc_cde,
loc_desc,
dept_nbr integer external,
TIMESTAMP DATE &quot;DD Mon YYYY HH24:MI:SS&quot;
)

Hope this prove successful for you.

Herb ...

 

[artik]

I am afraid it is a NT environment

BTW thanx a lot
it will help me

 

[rcurva]

Try do some research on Heterogenous Connections in Oracle. Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War