OraFAQ Forum: SQL & PL/SQL » question on sqlload [SOLVED]

Home » SQL & PL/SQL » SQL & PL/SQL » question on sqlload [SOLVED]

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #149886 is a reply to message #149882]

Mon, 05 December 2005 09:37

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

In other words, are you asking whether column A can have a null value?
If so, please look here
http://www.orafaq.com/forum/t/54852/0/
Else
Please rephrase the question.

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.

Report message to a moderator

Re: question on sqlload [message #149891 is a reply to message #149882]

Mon, 05 December 2005 09:57

fjfish
Messages: 16
Registered: December 2005
Location: UK

Junior Member

If this is still an issue can you give us an example of the different scenarios, making the details anonymous of course.

Report message to a moderator

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.

Report message to a moderator

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)
========================================

Report message to a moderator

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.

Report message to a moderator

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

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?

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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 #149900 is a reply to message #149896]

Mon, 05 December 2005 11:05

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1005731

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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.

Report message to a moderator

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 Smile

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

Report message to a moderator

Previous Topic:	exiting PL/SQL block with error code
Next Topic:	Not geting rows with empty email.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 10:52:44 CDT 2025