BCP usage practices in Sybase and sqlldr usage practices in Oracle

There is a lot of information about the usage of BCP on the Internet, but it can only be used after it has been confirmed by its own practice. Some of the online stuff is transferred in and out, and there will be some mistakes, therefore, the relevant information on the Internet should be widely used for reference. Do not be superstitious about a specific family, because IT-related stuff is stored on different platforms, different versions, different habits, and different la S, so there will be any possible and impossible things. My practical platform is under Windows 2003 Server, Sybase ASE 12.5, Oracle9i
For BCP export and import, note that if you export data from one Sybase Database to another, parameter selection is important:
-C parameter.-T is followed by a separator, which can be separated by "," or "|, we recommend that you use "|" to avoid interference with some fields;
Sybase BCP export Syntax:
BCP datasource. DBO. pub_goods out data/pub_goods.txt-uuser-ppassword-sService-T,-n
Note: datasource is a data source, and it is a level with the master. If your table is created under the master, datasource is the master; DBO owner Needless to say (some articles have been saved, such as datasource .. pub_goods: If I try it, it will waste a lot of time.) pub_goods indicates the table name, out indicates the export, and in indicates the import;

Data/pub_goods.txt stores the exported files in the datadirectory under the current directory, and the name is pub_goods.txt;-S is the database service name,
Import Sybase BCP:
BCP datasource. DBO. pub_goods in data/pub_goods.txt-uuser-ppassword-sService-T,-n

Assume that the database service name in my Sybase Database is Sherp, and a shpos data source is created. There is a table pub_goods under shpos.
The Data Source Name of oracle9i is ora_sherp. In ora_sherp, create a pub_goods table that is the same as that of shpos in Sybase,
Note that the Field Types of Oracle and Sybase are different. In particular, for example, the range of smallmoney values in Sybase is-214 748.3648 to 214 748.3647,
In Oracle, the data type should be set to number (). If the data type is not clear, it should be replaced by char in Oracle,
The password must be converted as needed. The user name SA in the Sybase library is empty:

Export all the data in the pub_goods table from the Sybase Database. Of course, we need to export the required data according to our own requirements;
First, we need to convert the date format in Sybase. Otherwise, it is not recognized in Oracle. You can consider creating a view.
Convert the date format to: for example:
Creat view v_pub_goods as select pub_goods, pub_goodsname, pub_class, pub_price,
Convert (char (10), createdate, 111) + ''+ (convert (char (8), createdate, 108) as createdate from pub_goods
Then execute the export job:
BCP shpos. DBO. pub_goods out data/pub_goods.txt-USA-p-ssherp-T "|"-C

-- Of course, you can also use the iSQL tool in Sybase to save the intermediate process of creating a view or temporary database. iSQL usage:
D: \> iSQL-USA-p-w1000-s "|"-I SQL/pub_goods. SQL-o Data/pub_goods.txt
First, adjust the width of the CMD buffer-cmd properties-> Layout-> the screen buffer size and window size to 10000,
The specific size can be calculated based on the maximum number of characters in the table row;-w1000 buffer width;-s "|" similar to-T "|" in BCP ";
-I SQL/pub_goods. SQL: Create a pub_goods. SQL document under the SQL directory of the current directory, that is, the SELECT statement. The content is as follows:
Select pub_goods, pub_goodsname, pub_class, pub_price,
Convert (char (10), createdate, 111) + ''+ (convert (char (8), createdate, 108) as createdate from pub_goods
Note: because the data file exported by iSQL is large and it is troublesome to write the CTL control file when I use sqlldr to import it to Oracle below,
Because all columns are empty "|" delimiters, add a row before the first column of the CTL control file: NULL filler,
It is easy to handle null values. Therefore, we recommend that you use BCP. Sybase on the sco unix platform also supports the select condition export, and Microsoft sqlserver
The BCP tool supports more format patterns. I do not know why the BCP in Sybase on Windows cannot be exported using the select condition !~! @ # $ % &!

Next, start the derivative to the pub_goods table of the Oracle database. preparations:
Prepare two text files pub_goods.par and pub_goods.ctl in the current directory.

The pub_goods.par file is as follows:
--------------------------------------
Userid = user @ ora_sherp // you can also use user/password @ ora_sherp to omit the password. You will be prompted to enter the password during running.
Control = pub_goods.ctl // is the control file mentioned above
Bad = log/pub_goods.bad // the path for storing bad records, so there is no imported record;
Log = log/pub_goods.log // complete record. You can find related logs, especially detailed descriptions of failure records;
Errors = 500 // The number of allowed error records. If the number of error records reaches this value, stop sqlldr.
Skip = 0 // ignore 0th rows. Generally, we can see the data file format. If there is a line of field headers, write skip = 1.
Rows = 50 // bind an array: 50 rows, up to 256000 bytes. Each row is committed.
// Direct = true // this parameter is directly loaded in the path and Will unmount some index constraints of the table, and will soon,
// Confirm or re-create the index constraint after installation. The default value is false,
--------------------------------------

The content of the pub_goods.ctl file is as follows:
---------------------------------------------------
Load data
Infile 'data/pub_goods.txt '// data file path
Truncate into Table pub_goods
Fields terminated by '|' -- // delimiter in the data file
Trailing nullcols -- // ignore null values

(-- // If you add null filler here, it indicates canceling the effect that the first empty column is "|, otherwise, the system regards the first "|" column as the first column.
-- // This is because you are using the iSQL tool derivative. You don't have to worry about this problem when using BCP.
Pub_goods,
Pub_goodsname,
Pub_class,
Pub_price,
Createdate timestamp "yyyy/mm/DD hh24: MI: SS" -- // load according to the time format of the pub_goods.txt data file,
-- // Note that nullif createdate = "00:00:00" can be added after this"
-- // If the field is null, a value of "00:00:00" will be returned. Otherwise, the non-Char NULL data import will fail,
-- // Of course, this can also be used as a work und: for example, pub_price filler char manually defines the pub_price column to be imported as a char type
-- // If it is: pub_price filler (Note: This column is ignored when the filler is added here, and this column is not loaded)
)
----------------------------------------------------------------
Above -- // and the following content are all comments, which can be removed in actual operations;

If all are ready! Open CMD and enter sqlldr parfile = pub_goods.par.
You can see the running result, which may fail and fail n times,

If an import failure record exists, Check log = log/pub_goods.log and bad = log/pub_goods.bad,
Is there a problem with the data file? Is column sex too long or too small? Check the data file and Table Structure

If you have any questions or have a better solution, please letter to: ximenqiang013@hotmail.com, need to discuss, please tell msn

The following is a batch process imported using sqlldr: Make sure that the current directory contains the log and Data Directories, and make sure that the current directory contains "table name. CTL" and "table name. Par"
"Example Table Name .txt" is stored in the dataproject -- the data file exported with BCP; it is strongly feasible to manually export it again. The relevant file name rules are named by the table name.

Rem *************************************** *****************************
Rem *************************************** *****************************
Rem *** import the data exported with iSQL to the corresponding table in Oracle with sqlldr *****
Rem *************************************** *****************************
Rem *************************************** *****************************

@ Echo off
Color 0a
CLS
Echo. The Execution Code of this program is Yolo by default.
Set/P val =. Enter the Execution Code:
If not "% Val %" = "yolo" Goto end
Echo. The Execution Code check is complete !!!!!
Set/P clstns1 = enter the name of the database to be imported:
Set/P Table1 = enter the % clstns1 % table name you want to import:
Set/P user1 = enter % clstns1 % database username:
CLS
Rem ##########< <for the sake of confidentiality, hide output display in assembly mode >>> ################
Echo /////////////////////////////////////// /////////////
Echo/password are hidden for processing, and the operator will not be able to see the screen display /////////
Echo/press Enter when the input is complete! Follow the on-screen prompts. Please wait ......////////
Echo /////////////////////////////////////// /////////////
Rem ### The following figure shows the screen latency ###
Pinging 127.1> NUL
Rem ###### Ping 127.1> NUL
Rem ####################################### #############################

CLS
@ Echo off
Chcp 437> NUL & graftabl 936> NUL
Echo hp1x500p [pzbbbfh # B # fxf-V @ '$ fpf] F3/F1/5 ++ u5x> in.com
Set/P password1 = enter the password (screen input is hidden ): For/F "tokens = *" % I in ('in. com') do set password1 = % I
Del in.com
CLS
Rem ####################################### ##########################

CLS
Echo: you are about to import the % clstns1 % database, % Table1 % table under % user1 % USER. Please verify ......
ECHO is preparing to execute sqlldr to import data to the Oracle database. Press any key to export data ......
Pause
Echo reminds you again: You export the % Table1 % table under % clstns1 % database % user1 % user,
Echo because this operation writes data directly to the database, misoperation may cause irreparable consequences ,,,,,
Echo producers are not responsible for any consequences!
Pause
Pause
Rem #########################< <Execution Code >>>> # ######################

@ Echo off

Sqlldr % user1 %/% password1 % @ % clstns1 % control = % Table1 %. CTL log = log \ sqlldr % Table1 %. log bad = log \ sqlldr % Table1 %. bad Data = data \ %

Table1).txt

Rem ############# view the result status to the user ##############
Pinging 127.1> NUL

REM can fix the syntax sentence "sqlldr" and implement regular import of scheduled tasks through batch processing,
Rem bad = log \ sqlldr % table %. bat changed
Rem bad = log \ sqlldr % table % Date :~ 0, 10% BAT to name by date or time,
The specific date and time formats of REM are self-tested. The premise is:
The data files imported by rem bcp must also be periodically exported to the date directory as scheduled.