Oracle Enterprise stage, UPSERT works, TRUNCATE aborts!
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Oracle Enterprise stage, UPSERT works, TRUNCATE aborts!
Hi,
I have a file which I load into a table. If I use UPSERT option in the Oracle enterprise stage, the job finishes fine, but If I use
WRITE METHOD=LOAD & WRITE MODE= TRUNCATE, the job aborts with the following error!
The system(sqlldr bdr@cooradevl31 CONTROL=ora.3331.899749.0.ctl LOG=ora.3331.899749.0.log BAD=ora.3331.899749.0.log.bad SILENT=header PARFILE=ora.3331.899749.0.par) failed; see the log file for the Oracle specific message.
Any help is greatly appreciated
Thanks,
I have a file which I load into a table. If I use UPSERT option in the Oracle enterprise stage, the job finishes fine, but If I use
WRITE METHOD=LOAD & WRITE MODE= TRUNCATE, the job aborts with the following error!
The system(sqlldr bdr@cooradevl31 CONTROL=ora.3331.899749.0.ctl LOG=ora.3331.899749.0.log BAD=ora.3331.899749.0.log.bad SILENT=header PARFILE=ora.3331.899749.0.par) failed; see the log file for the Oracle specific message.
Any help is greatly appreciated
Thanks,
Vinay,
have you done what the error message has told you to do?
Go to your DataStage temp directory, start with node1 and look into the directory /default/Scratch and then view the file ora.3331.899749.0.log
sometimes the first /tmp node doesn't have the error, in which case I would look at the log file in more detail or just use the "find" unix command to locate the log file.
have you done what the error message has told you to do?
Go to your DataStage temp directory, start with node1 and look into the directory /default/Scratch and then view the file ora.3331.899749.0.log
sometimes the first /tmp node doesn't have the error, in which case I would look at the log file in more detail or just use the "find" unix command to locate the log file.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Vinay,
the column is probably an Oracle VarChar2() datatype ... and by definition the loader trims these and makes it a null field.
the column is probably an Oracle VarChar2() datatype ... and by definition the loader trims these and makes it a null field.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
There is a sql loader option to PRESERVE BLANKS and I would think there would be a way to enable this for a load.ArndW wrote:the column is probably an Oracle VarChar2() datatype ... and by definition the loader trims these and makes it a null field.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
The APT_ORACLE_PRESERVE_BLANKS will work as long as the column isn't VarChar2; I assumed that it was set so was looking for the more obscure error.
Good job - glad to see it is working.
Good job - glad to see it is working.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
There isn't a workaround. If you have a NOT NULLable VarChar2 column and the data only has spaces in it then the bulk load method won't work.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am