Home » SQL & PL/SQL » SQL & PL/SQL » SQL Loader ; SQL (11g)
SQL Loader ; SQL [message #632108] Sun, 25 January 2015 05:37 Go to next message
mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore
Junior Member
Hi , I want to write a sql query to check the data loaded into Oracle tables land in correct columns. We are loading huge data thrugh SQL Loader into oracle Table , all the data are getting loaded properly but after the load data are getting loaded in wrong columns e.g in the data file we have data data of country and .ctl file we have proper maping for country but after the load the country data is being loaded to county field in the database table. For this reason we want to Prepare sql queries to check the data loaded into the tables land in correct columns .Can anyone please help on this .

Thanks,
Mike

Report message to a moderator

Re: SQL Loader ; SQL [message #632109 is a reply to message #632108] Sun, 25 January 2015 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you check the control file is correct instead?

How can you know if a data is in the correct column?
Define the rule/process.
If you can't then no one can, including Oracle.

Empty the table and restart the load with a correct control and data files.

Report message to a moderator

Re: SQL Loader ; SQL [message #632110 is a reply to message #632109] Sun, 25 January 2015 06:19 Go to previous messageGo to next message
mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore
Junior Member
Data is in the correct place in as per the control file but after the load data is getting loaded in the wrong columns .
For this i want to write sql query to check the data loaded into the tables land in correct columns if not where is the mismatch .

Report message to a moderator

Re: SQL Loader ; SQL [message #632111 is a reply to message #632110] Sun, 25 January 2015 06:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
mapps0999@gmail.com wrote on Sun, 25 January 2015 04:19
Data is in the correct place in as per the control file but after the load data is getting loaded in the wrong columns .
For this i want to write sql query to check the data loaded into the tables land in correct columns if not where is the mismatch .

Nobody here is stopping you from you doing any thing.

We don't have your data, your tables, or your code so we have ABSOLUTELY NOTHING to work with.
All you have posted so far are empty words.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: SQL Loader ; SQL [message #632112 is a reply to message #632111] Sun, 25 January 2015 06:29 Go to previous messageGo to next message
mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore
Junior Member
below is my control file . And the table strutcher is as per this . Hope this would help .

The .ct file :-


OPTIONS (silent=(header,feedback,discards),SKIP = 1, ERRORS=50, rows=500000)


load data
INFILE *
BADFILE 'xxrx_match.bad'
DISCARDFILE 'xxrx_match.dsc'


into table XX_SEAR_DATA
TRUNCATE
fields terminated by ','
optionally enclosed by '"'
TRAILING NULLCOLS
(
SCENARIO "
LTRIM(:SCENARIO)",
PARTY_ORIG_SYSTEM "RTRIM(:PARTY_ORIG_SYSTEM)",


PARTY_ORIG_SYSTEM_REFERENCE "TRIM(:PARTY_ORIG_SYSTEM_REFERENCE)",


COMPANYNAME NULLIF FIRSTNAME=BLANKS,


FIRSTNAME NULLIF FIRSTNAME=BLANKS,


LASTNAME NULLIF LASTNAME=BLANKS,


JOB NULLIF JOB =BLANKS,
ADD1 NULLIF ADD1=BLANKS,
CITY NULLIF CITY=BLANKS,

COUNTRY NULLIF FIRSTNAME=BLANKS,

PHONE NULLIF PHONE=BLANKS,
EMAIL NULLIF EMAIL =BLANKS,
URL NULLIF URL=BLANKS
)

Report message to a moderator

Re: SQL Loader ; SQL [message #632113 is a reply to message #632112] Sun, 25 January 2015 06:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
All & every SQL*Loader invocation involves the table, the control file contents & and the data.
If/when any one of the three items listed above is not correct, then a problem can result.

For a fire, you require Heat, Oxygen, & Fuel.
Showing you have fuel & asking why you don't have a fire; totally disregards the other two vital components.

OK, I agree you have a problem, but I don't know enough based upon the lack of complete details to understand which component is not correct for you.

Report message to a moderator

Re: SQL Loader ; SQL [message #632114 is a reply to message #632113] Sun, 25 January 2015 07:35 Go to previous messageGo to next message
mapps0999@gmail.com
Messages: 19
Registered: October 2013
Location: Bangalore
Junior Member
Here my requirement is not fix the problem for the above mentioned scenario , I just want to write a simple sql query to find out
what are the columns that is cumming from the control file and the corresponding field of its matching data in the database .E.g
in control file we have data of country but the this is being loaded to county field in the data base . Then our query should given only this discrepancy column names .Here the discrepancy column is country .Hence the output should look like as below .

OUPT PUT :-

COUNTRY .

Report message to a moderator

Re: SQL Loader ; SQL [message #632115 is a reply to message #632114] Sun, 25 January 2015 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How Oracle would know that something that is in the county column should be in a country one?
Just fullfil a country table and a county one, then Oracle can do it.

Do the same for ALL wrong columns and data.

Report message to a moderator

Re: SQL Loader ; SQL [message #632117 is a reply to message #632114] Sun, 25 January 2015 07:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Why would you check COUNTY data when it does not exist in the control file?
Might this be a problem?

If data is required in a column, just add NOT NULL constraint.

Report message to a moderator

Re: SQL Loader ; SQL [message #632440 is a reply to message #632108] Fri, 30 January 2015 23:59 Go to previous message
jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum
Senior Member
one of the strange question i have ever seen in oracle. Sad

Report message to a moderator

Previous Topic: paramter values fetch from dual and apply to query
Next Topic: REGEXP_REPLACE to remove non-native english characters
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 11:27:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.