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 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 #57244 is a reply to message #57243] Fri, 30 May 2003 12:30 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
WHAT is tr in the cat command, please can u tell me in more detailed way. please
thanks
Re: sql loader date is causing problem [message #57245 is a reply to message #57244] Fri, 30 May 2003 13:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
quoting the man pages for tr,

DESCRIPTION
     The tr utility copies the standard  input  to  the  standard
     output with substitution or deletion of selected characters.
     The options specified and the string1 and  string2  operands
     control translations that occur while copying characters and
     single-character collating elements.

OPTIONS
     The following options are supported:

     -c    Complement the set of characters specified by string1.

     -d    Delete all occurrences of input  characters  that  are
           specified by string1.

     -s    Replace instances of repeated characters with a single
           character.

----------------------------------------------------------------------

-- now replace all the ($ with -
-- you need to espace ( 
-- syntax is
-- cat < sourcefile > | tr   find_this_pattern  replace_with_this_pattern > newFile
-- here we need to find ($ and replace with - (aiphen)
-- ( open bracelet is a reserved char. so escape it by a forward slash

$cat a | tr  ($  - > a1

-- now remove all trailing )
-- again you need to escape )
-- same here . escape closed bracket ) with a forward slash 
-- tr -d deletes the occurance of )
$ cat a1 | tr -d  ) > a2

Re: sql loader date is causing problem [message #57246 is a reply to message #57245] Fri, 30 May 2003 14:28 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
what if i want to delete a column in a file that is delimited by ","
Re: sql loader date is causing problem [message #57248 is a reply to message #57246] Fri, 30 May 2003 14:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
i beleieve, the example i given first, is a ',' delimited.
Re: sql loader date is causing problem [message #57249 is a reply to message #57248] Fri, 30 May 2003 15:02 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
IT WORKED, NOW I NEED TO DELETE A COLUMN PLEASE HLEP ME,
Re: sql loader date is causing problem [message #57250 is a reply to message #57249] Fri, 30 May 2003 15:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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.
Re: sql loader date is causing problem ONE more doubt [message #57280 is a reply to message #57265] Mon, 02 June 2003 07:25 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
buddy, I am loading 20 million records from my loader scripts, can u please tell me what would be best way to do, its not allowing me to do direct load because i got date format in it. please help me now
Re: sql loader date is causing problem ONE more doubt [message #57283 is a reply to message #57280] Mon, 02 June 2003 08:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
oracle docs classifies the restrions on direct path loads as following
apart from that, i dont see a date format being an issue.
please Post the log of session.
I agree that, sql*loader is not very brillaint in many cases. but it is the easiest one.
alternatively you can use UTL_FILE to read the text file, and write into a table
Restrictions on Using Direct Path Loads
In addition to the general load conditions described in Conventional Path Load Versus Direct Path Load, the following conditions must be satisfied to use the direct path load method: 

Tables are not clustered. 

Tables to be loaded do not have any active transactions pending. 

To check for this condition, use the Enterprise Manager command MONITOR TABLE to find the object ID for the tables you want to load. Then use the command MONITOR LOCK to see if there are any locks on the tables. 

You cannot have SQL strings in the control file. 

The following features are not available with direct path load. 

Loading object columns 

Loading LOBs 

Loading VARRAYs 

Loading nested tables 

Specifying OIDs for object tables with system-generated OIDs 

Specifying SIDs 

Loading REF columns 

Loading BFILE columns 

Physical records (set by the command-line option READSIZE) larger than 64k 

Re: sql loader date is causing problem ONE more doubt [message #57284 is a reply to message #57283] Mon, 02 June 2003 09:39 Go to previous messageGo to next message
sidd
Messages: 130
Registered: May 2003
Senior Member
I am so sorry to bother u again and again, i wonder if u have an example of it, i mean using UTL_FILE reading and writing into a table. please
Thanks again.
Re: sql loader date is causing problem ONE more doubt [message #57288 is a reply to message #57284] Mon, 02 June 2003 16:59 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
i beleieve, it wouldnt be any helpful to ur case.
utl_FILE is more costly.
Why is that, your sql*loader failing?
please post a sample session log.
Previous Topic: High CPU load with 300~500 connections
Next Topic: data need to wrap in one line.
Goto Forum:
  


Current Time: Fri Sep 20 11:47:32 CDT 2024