Home » Developer & Programmer » Data Integration » Missing Records (oracle 10.1.2)
Missing Records [message #386303] Fri, 13 February 2009 09:06 Go to next message
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.
Re: Missing Records [message #386326 is a reply to message #386303] Fri, 13 February 2009 13:05 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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
Re: Missing Records [message #386329 is a reply to message #386326] Fri, 13 February 2009 14:52 Go to previous messageGo to next message
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


Re: Missing Records [message #386330 is a reply to message #386303] Fri, 13 February 2009 15:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
use EXTERNAL TABLE
Re: Missing Records [message #386367 is a reply to message #386329] Sat, 14 February 2009 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: Missing Records [message #386665 is a reply to message #386367] Mon, 16 February 2009 08:50 Go to previous messageGo to next message
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
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.

Re: Missing Records [message #386693 is a reply to message #386665] Mon, 16 February 2009 12:34 Go to previous message
Littlefoot
Messages: 21806
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.
Previous Topic: Table or View doesn't exist
Next Topic: OUTER JOIN does not work with SUBSTR
Goto Forum:
  


Current Time: Thu Mar 28 16:34:41 CDT 2024