Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

KUP-04002 when first time access of Oracle Extenal table from Unix

Posted on 2009-04-14
6
Medium Priority
?
1,191 Views
Last Modified: 2013-12-26
Hi,

I am having a strange problem accessing a file in Oracle through an external table. It fails the first time I access it, but works for subsequent attempts. During first time read, the error is

ERROR at line 1:
ORA-20000: -29913: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04002: error getting file size for file

The file works fine as is, but I needed to do some modifications to the file before loading, and the problems are for the modified file. Modifications include a bit of Unix shell scripting, so I am not sure if this caused any file corruption. I did not see anything in plain sight though.

Are there any common reasons why an external table can throw this error just once and then start working?
I am not sure which piece of code would be relevant. Posting the offending Unix script piece for starters. Please let me know if you need anything else.
#New variables for massage
Year3=`date +%Y`
Year2=`expr $Year3 - 1`
Year1=`expr $Year2 - 1`
YearMinus1=`expr $Year1 - 1`
YearMinus2=`expr $YearMinus1 - 1`
Year1String=${Year1}000000000000000000000000000000
Year2String=${Year2}000000000000000000000000000000
Year3String=${Year3}000000000000000000000000000000
 
#Massage file. Divide the file into 5 tokens. File record adjustments are made based on the year field present on the second token.
 
sed -e "s|^\(.\{31,31\}\)\($YearMinus1.\{26,26\}\)\($Year1.\{26,26\}\)\($Year2.\{26,26\}\)\(.*$\)|\1\3\4$Year3String\5|g" 
-e "s|^\(.\{31,31\}\)\($YearMinus2.\{26,26\}\)\($YearMinus1.\{26,26\}\)\($Year1.\{26,26\}\)\(.*$\)|\1\4$Year2String$Year3String\5|g" < $file > ${file}_temp
 
#Move all massaged records back to original file. Note that records not massaged will be removed. 
grep "^.\{31,31\}$Year1" ${file}_temp> $file

Open in new window

0
Comment
Question by:jainulap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 24147387
I'm a little bit unclear as to what happens here, so let me attempt to figure out the process:
- you have a file, let's call it fileA
- you run the code above in order to create fileA_temp and then you dump the contents of fileA_temp back into fileA
- finally, you get an error when trying to access the external table linked to fileA

Is this correct?

A few questions:
1. Are you getting any other error following KUP-04002?
2. How are you reading from the external table?
3. Is fileA also from a Unix system or is it coming from a different OS?
0
 

Author Comment

by:jainulap
ID: 24178415
Resolved.

gatorvip - First of all, thanks for looking. To your questions, what I do is almost exactly as you mentioned. Only difference is that when I move from fileA_temp back to fileA, I drop some of the records in fileA_temp.

There is no other error following KUP-04002. The stack trace is exactly as I provided.
I am reading from the external table by calling a stored procedure using sqlplus from Unix. I also tried a direct select from sqlplus.
fileA is an ascii file coming from a Windows system.


How did it get resolved?
I honestly don't know. This was showing up consistently for one whole day, and resolved itself the next day. I did try doing the same thing in a couple of slightly different ways and finally came back to the original code. I could have accidentally corrected some mistake in the original script. I have not been able to reproduce the problem yet. I will post a solution when/if I find why this happened.
0
 
LVL 20

Expert Comment

by:gatorvip
ID: 24184287
>>Resolved
Good to know! Sometimes it's very nice when issues just seem to work themselves out!

>>fileA is an ascii file coming from a Windows system.

It is possible there could be an initial error due to newline characters
0
 

Author Comment

by:jainulap
ID: 24219432
Possible.
For a file that is coming from a windows system unaltered, the carriage return characters are just ignored by Oracle in Unix servers, because the external table definition does not include that last character.

My manipulation could have misplaced these ^M characters somewhere else, causing a problem. I did notice that the ^Ms disappeared once I tried the little manipulation using sed, so I guessed we were good to go.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 24457150
Question PAQ'd, 50 points refunded, and stored in the solution database.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question