FW: Sybase tables

I use sql*loader and works fine. Just be careful with the data type conversion. Our analyst write a perl script to do the data extraction.

Here is the data type conversion that I use:

Sybase           Oracle
========         =======
numeric          number
tinyint          number(3,0)
smalldatetime    date
int              number
real             number    (If you want to have two decimal, you need to
specify the number
                            of decimal for example:  number(7,2)  However,
when this
                            floating number gets displayed, it will display
the whole number
                            if it is a whole number and drop the .00)
example: If the data
                            is 250.00 it display as 250.  It it is 250.50 it
displays as
                            250.5.  However, it does round up if the data
entered is 249.493
                            it will display as 250)

Here is a sample of the control file. For the example here, the date in our data file shows up like this: Jun 19 2000 23:00

LOAD DATA
INFILE items_status.dat
INTO TABLE items_status
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(id,
 order_id,
 tracking_num,
 sku,
 ship_method,
 status,
 carrier,
 ship_date date(20) "MON DD YYYY HH24:MI",  description)

Hope this help.

Lucia
-----Original Message-----
From: Gregory_LoughMiller_at_bscc.bls.com
[mailto:Gregory_LoughMiller_at_bscc.bls.com] Sent: Wednesday, June 21, 2000 5:49 AM
To: ORACLE-L_at_fatcity.com
Subject: RE: Sybase tables

There was some development done here to move data between sybase to Oracle via C programs using both the Sybase "libraries" and Oracle PRO*C/OCI. Another option-but the dumping to a delimited flat file and using sql*loader
seams more appealing..

greg

-----Original Message-----
Sent: Wednesday, June 21, 2000 8:08 AM
To: Multiple recipients of list ORACLE-L

Hi,

Yes you can use a Sybase utility to generate flat files, with <character> separate fields. The name of the utility is (I think) bcpout.

Also you can use some of the gateways products between sysbase and oracle databases.
This option is more *transparent* but prepare your self to empty your pockets.

HTH Joselu
www.OraEs.com
Madrid (SPAIN)

>> -----Mensaje original-----
>> De: Glen Braden [SMTP:g.a.braden_at_larc.nasa.gov]
>> Enviado el: martes 20 de junio de 2000 23:20
>> Para: Multiple recipients of list ORACLE-L
>> Asunto: Sybase tables
>>
>> Does anyone have a technique to load sybase tables (data) into oracle
>> tables?
>>
>> I am thinking that sybase must create a flatfile, and the flatfile is
>used
>>
>> by sqlloader.
>>
>> Is this correct?
>>
>> Glen A Braden g.a.braden_at_larc.nasa.gov
>> DBA gbraden_at_visi.net
>> ICQ#33298579
>> (757) 865-8440 x130 http://sun1misb.larc.nasa.gov/bamis/
>> (757) 865-6426 Fax ADABAS 6.2.1 NATURAL 2.2.8++ TRIM 6.1.0
>> NCI Information Systems SUPERNATURAL 3.1.5 MVS/OS 390 CICS 4.3
>> ORACLE 6 (don't Ask), ORACLE 7,
>> ORACLE 8
>>
>> --
>> Author: Glen Braden
>> INET: g.a.braden_at_larc.nasa.gov
>>
>> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>> San Diego, California -- Public Internet access / Mailing Lists
>> --------------------------------------------------------------------
>> To REMOVE yourself from this mailing list, send an E-Mail message
>> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>> the message BODY, include a line containing: UNSUB ORACLE-L
>> (or the name of mailing list you want to be removed from). You may
>> also send the HELP command for other information (like subscribing).

--
Author: Marina Leonardo, Jose Luis
  INET: JLMarina_at_Uni2.es

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Author: LoughMiller, Gregory
  INET: Gregory_LoughMiller_at_bscc.bls.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


--- Internet Message Header Follows ---
Received: from newsfeed.cts.com (209.68.192.199)
	by prophet.com (FirstClass Mail Server v5.11)
	transient id 58; 5:28:30 AM -0800
Received: from fatcity.UUCP (uucp_at_localhost)
	by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id FAA42830;
	Wed, 21 Jun 2000 05:19:37 -0700 (PDT)
Received: by fatcity.com (04-May-2000/v1.0f-b69/bab) via UUCP id 001813ED;
Wed, 21 Jun 2000 04:48:51 -0800
Message-ID: <F001.001813ED.20000621044851_at_fatcity.com>
Date: Wed, 21 Jun 2000 04:48:51 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "LoughMiller, Gregory" <Gregory_LoughMiller_at_bscc.bls.com>
Sender: root_at_fatcity.com
Reply-To: ORACLE-L_at_fatcity.com
Errors-To: ML-ERRORS_at_fatcity.com
From: "LoughMiller, Gregory" <Gregory_LoughMiller_at_bscc.bls.com>
Subject: RE: Sybase tables
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce A. Bergman