Home » RDBMS Server » Server Administration » sql loader date is causing problem
sql loader date is causing problem [message #57242] |
Fri, 30 May 2003 05:23 |
sidd
Messages: 130 Registered: May 2003
|
Senior Member |
|
|
Hi guys here is sample data file i need to write a ctl script for it. what i want is where ever there is an amt in () i need to put "-" b4 the amt in the table,
ID HDATE AMT STA
4, 4/5/01 0:00:00 , ($88.53), E
8, 7/8/02 0:00:00 , ($975.52), X
ALL THESE ARE , DELIMITED.. PLEASE HELP ME SOON
|
|
|
Re: sql loader date is causing problem [message #57243 is a reply to message #57242] |
Fri, 30 May 2003 08:03 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
there are many methods to do it.
the simplest in your case is to use simple os utilities
like this.
If this doesnt fit you, we can look into other options
[b]-- let 'a' be the source file[/b]
$ cat a
4, 4/5/01 0:00:00 , ($88.53), E
8, 7/8/02 0:00:00 , ($975.52), X
[b]-- now replace all the ($ with -
-- you need to espace ( [/b]
$ cat a | tr ($ - > a1
$ cat a1
4, 4/5/01 0:00:00 , -$88.53), E
8, 7/8/02 0:00:00 , -$975.52), X
[b]-- now remove all trailing )
-- again you need to escape )[/b]
$ cat a1 | tr -d ) > a2
$ cat a2
4,4/5/010:00:00,-$88.53,E
8,7/8/020:00:00,-$975.52,X
|
|
|
|
|
|
|
|
Re: sql loader date is causing problem [message #57250 is a reply to message #57249] |
Fri, 30 May 2003 15:27 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
-- let t be the source file
$ cat t
4, 4/5/01 0:00:00 , ($88.53), E
8, 7/8/02 0:00:00 , ($975.52), X
-- strip first and third field and create a new file
-- syntax is
-- cut -f field1,fieldn -d, sourcefile > new file
-- here -d, states that, a ',' coma is the delimiter
$ cut -f 1,3 -d, t > t1
$ cat t1
4, ($88.53)
8, ($975.52)
-- to keep the first three columns and remove the forth
$ cut -f-3 -d, t > t2
$ cat t2
4, 4/5/01 0:00:00 , ($88.53)
8, 7/8/02 0:00:00 , ($975.52)
|
|
|
Re: sql loader date is causing problem [message #57260 is a reply to message #57250] |
Sat, 31 May 2003 05:50 |
sidd
Messages: 130 Registered: May 2003
|
Senior Member |
|
|
Appreciated buddy, Thanks for your great help.. i have am new to this field, i have one more question, i have 160,0000 rows to load in a table using sql loader, is it best to use direct path or conventional path. and when am doing conventional path, it's displaying commit point reach and logical read count on my screen, for every 10 rows or so. so my questions is, is it normal to see that kind of message on screen? is going to display even if do throguh conventional path?. please if you can tell me something about it would really appreciate ur help.
Thanks again.
|
|
|
Re: sql loader date is causing problem [message #57265 is a reply to message #57260] |
Sat, 31 May 2003 14:37 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
it is normal
quoting the docs
ROWS (rows per commit)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.
Conventional path loads only: ROWS specifies the number of rows in the bind array. See Bind Arrays and Conventional Path Loads.
Direct path loads only: ROWS identifies the number of rows you want to read from the datafile before a data save. The default is to read all rows and save data once at the end of the load. See Using Data Saves to Protect Against Data Loss.
Because the direct load is optimized for performance, it uses buffers that are the same size and format as the system's I/O blocks. Only full buffers are written to the database, so the value of ROWS is approximate.
SILENT (feedback mode)
When SQL*Loader begins, a header message similar to the following appears on the screen and is placed in the log file:
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Feb 27 14:33:54 2002
(c) Copyright 2002 Oracle Corporation. All rights reserved.
As SQL*Loader executes, you also see feedback messages on the screen, for example:
Commit point reached - logical record count 20
SQL*Loader may also display data error messages like the following:
Record 4: Rejected - Error on table EMP
ORA-00001: unique constraint <name> violated
You can suppress these messages by specifying SILENT with one or more values.
For example, you can suppress the header and feedback messages that normally appear on the screen with the following command-line argument:
SILENT=(HEADER, FEEDBACK)
|
|
|
Re: sql loader date is causing problem [message #57266 is a reply to message #57265] |
Sat, 31 May 2003 22:08 |
sidd
Messages: 130 Registered: May 2003
|
Senior Member |
|
|
terrific, Thanks a lot for information, i don't know who you are, but helping me out a lot.. Thanks a lot. may i know how many yeays of exp u have in this. are in USA or in INDIA? if u don't mind to answer.
Thanks again.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 30 17:14:38 CST 2024
|