| question on sqlload [SOLVED] [message #149882] |
Mon, 05 December 2005 09:23  |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
I'm working on a validation package and I need to load some data from excel into an oracle table.
The table as 3 fields. The first one is employee numbers obtained from HR. The second and third columns contain the employee number and email attached to it from notes.
My issue is that column A does not always have the same number of records as column B and C (B being a key I use for referencing C)
My question is this. Will it cause problems for SQLLOAD if my columns do not have the same number of records?
[Updated on: Tue, 06 December 2005 08:20] Report message to a moderator |
|
|
|
|
|
| Re: question on sqlload [message #149887 is a reply to message #149886] |
Mon, 05 December 2005 09:42  |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
That wasn't exactly it, but it does answer my question.
Basically, either column A is a set of data and column B and C is another. It is possible that A have more rows then B/C or vice versa. Thank you very much.
|
|
|
|
|
|
| Re: question on sqlload [message #149892 is a reply to message #149891] |
Mon, 05 December 2005 10:04 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
I still haven't tested it yet, but I think it will work.
For the sake of usefulness of the topic, here is an exemple.
Column A is the list of employee numbers found in a report provided by HR.
Column B is the list of employee numbers that correspond with the e-mail found in column C
This is how the csv file will look.
00001;01000;us.na@compagny.com
00002;02000;email.add@compagny.com
00003;;
00004;;
I need the data put in the database, but I don't really care if it puts NULL in the fields when I do not have data (3rd and 4th line).
I need to do treatment on the first column to find out if employees not in it were end-dated. The second and third column will be used to ensure that e-mails are up to date in the database.
|
|
|
|
| Re: question on sqlload [message #149893 is a reply to message #149882] |
Mon, 05 December 2005 10:06 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
I guess I should post my definition file.
========================================
load data
infile validations.csv
replace
into table User_Validations
fields terminated by ';'
(emp_number_hr,emp_number_notes,email)
========================================
|
|
|
|
| Re: question on sqlload [message #149894 is a reply to message #149892] |
Mon, 05 December 2005 10:10 |
fjfish
Messages: 16
Registered: December 2005
Location: UK
|
Junior Member |
|
|
The nulls will just go in, assuming the table allows nulls of course.
Have you looked at external tables? You may be able to put this file into a directory and treat it just like a table. You can't index them and it loads it every time you query them though, so there may be some performance hit.
Hope this helps.
|
|
|
|
| Re: question on sqlload [message #149895 is a reply to message #149894] |
Mon, 05 December 2005 10:16 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
| fjfish wrote on Mon, 05 December 2005 10:10 |
The nulls will just go in, assuming the table allows nulls of course.
Have you looked at external tables? You may be able to put this file into a directory and treat it just like a table. You can't index them and it loads it every time you query them though, so there may be some performance hit.
Hope this helps.
|
Actually, no, I hadn't looked. I will be loading this table twice when I run my validation procedures. This only happens once a month and during maintenance at that.
As such, performance isn't really an issue. I don't plan on using indexes, as I have to do a full scan anyway.
Would be more of a performance hit to make the index then I get by not using any.
Got any pointers for external tables?
|
|
|
|
| Re: question on sqlload [message #149896 is a reply to message #149882] |
Mon, 05 December 2005 10:24 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
Well, I just tried loading the data and it didn't work quite as expected.
SQL*Loader: Release 8.0.6.3.0 - Production on Mon Dec 5 11:21:45 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 17
cacpx1orudbd01:/home/applsnbx/validations
That part worked. However, the last records with data only in column A did not get recorded. Looking at the why.
edit: It looks like it doesn't like the rows with only column A.
SQL*Loader: Release 8.0.6.3.0 - Production on Mon Dec 5 11:21:45 2005
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: definition.ini
Data File: data.csv
Bad File: load.bad
Discard File: load.disc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table USER_VALIDATIONS, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEE_NUMBER_HR FIRST * ; CHARACTER
EMPLOYEE_NUMBER_NOTES NEXT * ; CHARACTER
EMAIL NEXT * ; CHARACTER
Record 15: Rejected - Error on table USER_VALIDATIONS, column EMAIL.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 16: Rejected - Error on table USER_VALIDATIONS, column EMAIL.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 17: Rejected - Error on table USER_VALIDATIONS, column EMPLOYEE_NUMBER_HR.
Column not found before end of logical record (use TRAILING NULLCOLS)
Table USER_VALIDATIONS:
14 Rows successfully loaded.
3 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 65016 bytes(84 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 0
Total logical records read: 17
Total logical records rejected: 3
Total logical records discarded: 0
Run began on Mon Dec 05 11:21:45 2005
Run ended on Mon Dec 05 11:21:48 2005
Elapsed time was: 00:00:03.16
CPU time was: 00:00:00.03 [Updated on: Mon, 05 December 2005 10:28] Report message to a moderator |
|
|
|
|
|
| Re: question on sqlload [message #149909 is a reply to message #149900] |
Mon, 05 December 2005 12:17 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
Thank you. That worked. Here is what I put as my definition file.
load data
infile validations.csv
replace
into table User_Validations
TRAILING NULLCOLS
(
EMPLOYEE_NUMBER_HR TERMINATED BY ';',
EMPLOYEE_NUMBER_NOTES TERMINATED BY ';',
email TERMINATED BY WHITESPACE
)
This fixed the issue and allowed the lines with columns without data to get loaded. Thanks
|
|
|
|
| Re: question on sqlload [message #150089 is a reply to message #149895] |
Tue, 06 December 2005 04:24 |
fjfish
Messages: 16
Registered: December 2005
Location: UK
|
Junior Member |
|
|
Sorry I haven't got back to you - mad busy.
Have a look on Oracle Base http://www.oracle-base.com/Index.php and search for external table in the search on the bottom left.
It will eventually take you to the documentation. You'll need a technet registration.
There's also quite a bit on the Ask Tom website (another excellent resource)
Got to run - hope this is of use.
|
|
|
|
| Re: question on sqlload [message #150130 is a reply to message #150089] |
Tue, 06 December 2005 08:17 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
No problem about the delay. Absolutely normal 
I'll see if I can have a look at that article, but for now, sqlload does the trick for me.
Thank you very much
Mad Chaz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
