Oracle SQL Loader
Firstly you haven't specified a log file, which means Oracle SQL Loader probably in the same place as your ctl file but it could also be in the directory you called Oracle SQL Loader from or the directory the data is in - as an aside it's a good idea to call Oracle SQL Loader from the same place you store the ctl file to avoid confusion. Go looking for it and the associated bad file.
I would always explicitly state the location of the log and bad - and discard file if appropriate - either in the command line or in the ctl file. I prefer the command line so you can put them all in different folders so they don't overwrite each other. So you don't have to change the ctl file every time you load something you can also put the data file (and almost everything else) in the command line. Something like this:
call sqlldr scott/tiger@mydb my_ctl.ctl data=d:\20110201\my_file.csv log=d:\20110201\my_log.log bad=d:\20110201\my_bad.bad
There are two probable reasons for your problem.
As @JustinCave suggested you're simply selecting from the wrong table. We'll put this aside for now.
You've noted that you've reached a commit point so there should be data in the table. This is not at all the case. You have reached a commit point, but, according to your posted ctl file, you haven't specified the number of errors allowed. This means Oracle SQL Loader is using the default - 50. It's possible to reach a commit point, where everything loaded before it is errors; i.e. you're committing nothing.
Point 2 is the most likely cause of your problem. Look in the log file and it should tell you, in a not necessarily very helpful way, why you've got errors. The bad file contains all the data that hasn't been loaded, which you can check against the log.
There are quite a few reasons for the second one to occur so here's a list of things that can go wrong with Oracle SQL Loader:
Your ctl columns and table columns are not called by exactly the same names.
Your file doesn't exist.
Your table doesn't exist.
You have a delimiter at the end of your file, which means you need to add the option TRAILING NULLCOLS.
You have a new-line in the middle of a line - you're in big trouble. You'll need to ask another question with full ctl and table descriptions along with sample data.
One of the columns in your table is a date datatype. As every piece of data in a csv is by definition is a string Oracle SQL Loader can't turn this into a date.
I'm going to assume this is hiredate, which would become hiredate "to_date(:hiredate,'yyyy/mm/dd')" in the ctl file, where yyyy/mm/dd is changed to whatever date format you need. See here for a good list. Of course you could always change this column to a char and deal with the transformation later.
One of the columns in your table is a number datatype and you're trying to load a non-number into it. Sorry, in this case you need to change the datatype of your column to a char.
One of the columns in your table is a number and you're trying to insert formatted numbers into it. Remember that commas and decimal points are not a number, in which case you could use the to_number function: sal "to_number(:sal,'999.99')". As with dates you could always change this column to a char and deal with the transformation later.
You have a new line at the end of each line in your csv, which takes the length of the column over the maximum. Change deptno to deptno terminated by whitespace.
The fields in your table aren't big enough.
You're loading multi-byte data, for e.g. UTF-8 into a byte semantic table meaning that the number of characters are the same but the number of bytes is too few. Change this to char semantic.
A number has a space at the end, let's say this is sal as well you should change this to sal integer external, which explicitly tells Oracle SQL Loader it's a number.
Your file is called a csv but it's not actually. Somebody's renamed a pipe delimited text file as a csv ( this is just one example of quite literally hundreds of example I can give - .txt to .exe anyone? )
The simplest reason, that should probably be at the top, is that the data in your csv bears no relation to the specification of your table.
The characterset in your csv file is different to that of your database and Oracle is having problems translating it. Use the characterset option.
Off the top of my head that's most of what could go wrong with a load as simple as yours.
Now the advice. Specify. It's as simple as that. If you don't take advantage of the extremely powerful nature of Oracle SQL Loader and the myriad of options it provides you will come across problems like this. Not only that when a supplier changes something without telling you you're less likely to notice the change.
I would also highly recommend ALWAYS checking the log file after a load. Normally this is one of the only ways of checking that your load was successful. Oracle SQL Loader fails silently on almost every error bar ORA-01653 - not enough space and puts all information about these errors in the log file. You will not know about them unless you check.
A typical ctl file would normally look something like this:
OPTIONS ( skip=1, errors=10, rows=10000, direct=True)
LOAD DATA
INFILE 'd:\mydata.csv'
TRUNCATE
INTO TABLE emp1
FIELDS TERMINATED BY ","
OPTIONALLY ENLCOSED BY '"'
TRAILING NULLCOLS
( empno
, ename
, job
, mgr
, hiredate "to_date(:hiredate,'dd/mm/yy')"
, sal integer external
, comm
, deptno terminated by whitespace
)
All these things, bar the column names and the table name are optional.
The ones I've added are:
skip - Number of rows at the top to skip.
errors - The maximum number of errors before stopping.
rows - The number of rows to load before committing.
direct - Use direct path load.
TRUNCATE - Truncate the table prior to loading
TRAILING NULLCOLS - There are null columns at the end of your file.
"to_date(..." - Specify an Oracle function to call when loading this column
integer external - Force this column into a number datatype.
terminated by whitespace - Remove whitespace at the end of the line or column.
Sample 1 :
options (skip=1)
load data
infile ':p_filename'
append into table XXNIPS_DOMESTIC_ROQ_STG_ALL
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( PLANNING_ID ,
PLANNING_MONTH ,
INVENTORY_ITEM_ID ,
PART_NO ,
ROQ0,
ROQ1,
ROQ2,
ROQ3,
ROQ4,
ROQ5,
ROQ6,
ROQ7,
ROQ8,
ROQ9,
ROQ10,
ROQ11 terminated by whitespace
)
Move the control file & data file from local system to Server using FTP
Register the Control file in Front end
Step 1 : Go to the Application Developer Concurrent Executable
Step 2 : Go to the Application Developer Concurrent Program
Step 3 : Go to the System Administrator Security Responsibility Request
Step 4 : Go to the Responsibility View Request
Firstly you haven't specified a log file, which means Oracle SQL Loader probably in the same place as your ctl file but it could also be in the directory you called Oracle SQL Loader from or the directory the data is in - as an aside it's a good idea to call Oracle SQL Loader from the same place you store the ctl file to avoid confusion. Go looking for it and the associated bad file.
I would always explicitly state the location of the log and bad - and discard file if appropriate - either in the command line or in the ctl file. I prefer the command line so you can put them all in different folders so they don't overwrite each other. So you don't have to change the ctl file every time you load something you can also put the data file (and almost everything else) in the command line. Something like this:
call sqlldr scott/tiger@mydb my_ctl.ctl data=d:\20110201\my_file.csv log=d:\20110201\my_log.log bad=d:\20110201\my_bad.bad
There are two probable reasons for your problem.
As @JustinCave suggested you're simply selecting from the wrong table. We'll put this aside for now.
You've noted that you've reached a commit point so there should be data in the table. This is not at all the case. You have reached a commit point, but, according to your posted ctl file, you haven't specified the number of errors allowed. This means Oracle SQL Loader is using the default - 50. It's possible to reach a commit point, where everything loaded before it is errors; i.e. you're committing nothing.
Point 2 is the most likely cause of your problem. Look in the log file and it should tell you, in a not necessarily very helpful way, why you've got errors. The bad file contains all the data that hasn't been loaded, which you can check against the log.
There are quite a few reasons for the second one to occur so here's a list of things that can go wrong with Oracle SQL Loader:
Your ctl columns and table columns are not called by exactly the same names.
Your file doesn't exist.
Your table doesn't exist.
You have a delimiter at the end of your file, which means you need to add the option TRAILING NULLCOLS.
You have a new-line in the middle of a line - you're in big trouble. You'll need to ask another question with full ctl and table descriptions along with sample data.
One of the columns in your table is a date datatype. As every piece of data in a csv is by definition is a string Oracle SQL Loader can't turn this into a date.
I'm going to assume this is hiredate, which would become hiredate "to_date(:hiredate,'yyyy/mm/dd')" in the ctl file, where yyyy/mm/dd is changed to whatever date format you need. See here for a good list. Of course you could always change this column to a char and deal with the transformation later.
One of the columns in your table is a number datatype and you're trying to load a non-number into it. Sorry, in this case you need to change the datatype of your column to a char.
One of the columns in your table is a number and you're trying to insert formatted numbers into it. Remember that commas and decimal points are not a number, in which case you could use the to_number function: sal "to_number(:sal,'999.99')". As with dates you could always change this column to a char and deal with the transformation later.
You have a new line at the end of each line in your csv, which takes the length of the column over the maximum. Change deptno to deptno terminated by whitespace.
The fields in your table aren't big enough.
You're loading multi-byte data, for e.g. UTF-8 into a byte semantic table meaning that the number of characters are the same but the number of bytes is too few. Change this to char semantic.
A number has a space at the end, let's say this is sal as well you should change this to sal integer external, which explicitly tells Oracle SQL Loader it's a number.
Your file is called a csv but it's not actually. Somebody's renamed a pipe delimited text file as a csv ( this is just one example of quite literally hundreds of example I can give - .txt to .exe anyone? )
The simplest reason, that should probably be at the top, is that the data in your csv bears no relation to the specification of your table.
The characterset in your csv file is different to that of your database and Oracle is having problems translating it. Use the characterset option.
Off the top of my head that's most of what could go wrong with a load as simple as yours.
Now the advice. Specify. It's as simple as that. If you don't take advantage of the extremely powerful nature of Oracle SQL Loader and the myriad of options it provides you will come across problems like this. Not only that when a supplier changes something without telling you you're less likely to notice the change.
I would also highly recommend ALWAYS checking the log file after a load. Normally this is one of the only ways of checking that your load was successful. Oracle SQL Loader fails silently on almost every error bar ORA-01653 - not enough space and puts all information about these errors in the log file. You will not know about them unless you check.
A typical ctl file would normally look something like this:
OPTIONS ( skip=1, errors=10, rows=10000, direct=True)
LOAD DATA
INFILE 'd:\mydata.csv'
TRUNCATE
INTO TABLE emp1
FIELDS TERMINATED BY ","
OPTIONALLY ENLCOSED BY '"'
TRAILING NULLCOLS
( empno
, ename
, job
, mgr
, hiredate "to_date(:hiredate,'dd/mm/yy')"
, sal integer external
, comm
, deptno terminated by whitespace
)
All these things, bar the column names and the table name are optional.
The ones I've added are:
skip - Number of rows at the top to skip.
errors - The maximum number of errors before stopping.
rows - The number of rows to load before committing.
direct - Use direct path load.
TRUNCATE - Truncate the table prior to loading
TRAILING NULLCOLS - There are null columns at the end of your file.
"to_date(..." - Specify an Oracle function to call when loading this column
integer external - Force this column into a number datatype.
terminated by whitespace - Remove whitespace at the end of the line or column.
Sample 1 :
options (skip=1)
load data
infile ':p_filename'
append into table XXNIPS_DOMESTIC_ROQ_STG_ALL
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( PLANNING_ID ,
PLANNING_MONTH ,
INVENTORY_ITEM_ID ,
PART_NO ,
ROQ0,
ROQ1,
ROQ2,
ROQ3,
ROQ4,
ROQ5,
ROQ6,
ROQ7,
ROQ8,
ROQ9,
ROQ10,
ROQ11 terminated by whitespace
)
Move the control file & data file from local system to Server using FTP
Register the Control file in Front end
Step 1 : Go to the Application Developer Concurrent Executable
Step 2 : Go to the Application Developer Concurrent Program
Step 3 : Go to the System Administrator Security Responsibility Request
Step 4 : Go to the Responsibility View Request
No comments:
Post a Comment