OraFAQ Forum: Data Integration

Home » Developer & Programmer » Data Integration » Missing Records (oracle 10.1.2)

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

Missing Records [message #386303]

Fri, 13 February 2009 09:06

aarti81
Messages: 235
Registered: December 2007
Location: USA

Senior Member

Hi All
I'm encountering a problem with the csv file, it has 116 records and when i execute the mapping the table thats created in the stage has only 8 records, and i'm getting the following errors in the OWB:

ecord 3: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 8: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 9: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 10: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "VERIFIED".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 12: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 13: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 14: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 15: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 17: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 18: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "TIME_RECEIVED".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 19: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "ROAD_OPEN".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 20: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 21: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 22: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 23: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 24: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 25: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 26: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 27: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 28: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 29: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 32: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 33: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "CREATED_BY".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 34: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "VERIFIED".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 35: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 36: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 37: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 38: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 39: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "ROAD_OPEN".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 41: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 42: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 43: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 44: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "CREATED_BY".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 45: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 46: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "NOTES".
second enclosure string not present
Record 47: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "RTDIRNTYMEAS".
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 48: Rejected - Error on table "DWSTAGE"."STG_GEM_EVENT_ITS", column "BLOCKING".
Column not found before end of logical record (use TRAILING NULLCOLS)

And i dont see any NULL records in the source file, and thank you very much for your time and patience.

Report message to a moderator

Re: Missing Records [message #386326 is a reply to message #386303]

Fri, 13 February 2009 13:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How good are you at writing SQL*Loader control files? Did you consider including what's been suggested?

Oracle

use TRAILING NULLCOLS

Report message to a moderator

Re: Missing Records [message #386329 is a reply to message #386326]

Fri, 13 February 2009 14:52

aarti81
Messages: 235
Registered: December 2007
Location: USA

Senior Member

Unfortunately we are not having SQL Loader, i have to do it some where in the OWB(Oracle ware house builder).

Thanks

Littlefoot wrote on Fri, 13 February 2009 13:05

How good are you at writing SQL*Loader control files? Did you consider including what's been suggested?

Oracle

use TRAILING NULLCOLS

Report message to a moderator

Re: Missing Records [message #386330 is a reply to message #386303]

Fri, 13 February 2009 15:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use EXTERNAL TABLE

Report message to a moderator

Re: Missing Records [message #386367 is a reply to message #386329]

Sat, 14 February 2009 00:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Oh, there's bunch of unfortunate men around. I, for one, don't know anything about the Warehouse Builder.

But: error messages you got very much look like SQL*Loader error messages. In my opinion (which doesn't have to be true), Oracle uses SQL*Loader while loading data from a CSV file, just as external tables feature (mentioned by Black Swan) are based on SQL*Loader (note this part of the CREATE external table statement):

...
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ...

Now, could you - somehow - check how this loading process is done in Warehouse Builder? I guess that you must specify what goes where. This "mapping" process probably results in a control file I was talking about. Is there, perhaps, some "View control file" (or similar) button in the Warehouse builder? Or, is there a "use trailing nullcols" checkbox? Anything like that?

Because, if you don't use it, you'll have to give up and switch to something else (pure SQL*Loader or external tables). Although, I believe that Oracle (as a corporation) isn't that stupid so that it wouldn't include TRAILING NULLCOLS into the loading process of the Warehouse Builder.

Report message to a moderator

Re: Missing Records [message #386665 is a reply to message #386367]

Mon, 16 February 2009 08:50

aarti81
Messages: 235
Registered: December 2007
Location: USA

Senior Member

Thank You little foot.
Finally i found out the solution. In the OWB i have to right click on the mapping and configure and in the configure tab i ahd to set the "Tariling null cols" option to true............. but now i'm getting a new error :

SQL*Loader-466: Column "ID" does not exist in table "DWSTAGE"."STG_GEM_EVENT_ITS".

Thanks

Littlefoot wrote on Sat, 14 February 2009 00:52

...
ORGANIZATION EXTERNAL
( TYPE oracle_loader
  DEFAULT DIRECTORY ...

Report message to a moderator

Re: Missing Records [message #386693 is a reply to message #386665]

Mon, 16 February 2009 12:34

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

SQL*Loader-466: Column "ID" does not exist in table "DWSTAGE"."STG_GEM_EVENT_ITS".

That's an easy one, as - it is self-explanatory. You are loading data into a table, told Oracle to load data into the "stg_gem_event_its" column which is supposed to exist in the "dwstage" table, but - it doesn't.

Remedy: specify column that exists in a target table or - alternatively - specify a target table which contains a missing column.

Report message to a moderator

Previous Topic:	Table or View doesn't exist
Next Topic:	OUTER JOIN does not work with SUBSTR

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 10 02:15:40 CST 2024