Skip to Main Content
  • Questions

  • Sqoop ojdbc8.jar throws error ORA-06502:PL/SQL:: numeric or value error

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sridar.

Asked: February 10, 2020 - 1:34 pm UTC

Last updated: February 18, 2020 - 2:46 am UTC

Version: 12.2.2

Viewed 1000+ times

You Asked

Hi Tom,

We are trying to do a sqoop import to hive from Oracle and struck with a weird error below:

WARN[main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.io.IOException:
java.sql.SQLExcepion:
ORA-00606:error occurred at recursive SQL level 1

ORA-06502:PL/SQL: numeric or value error : character string buffer too small
ORA-06512:at line 25
at oracle.jdbc.driver.T4CCTTloer11.processError(T4CTTloer11.java:494

Issue seems to be coming from ojdbc8,jar Version below:

java -jar ./usr/lib/sqoop/lib/ojdbc8.jar
Oracle 12.2.0.1.0 JDBC 4.2 compiled with javac 1.8.0_91 on Tue_Dec_13_06:08:31_PST_2016
Default Connection Properties Resource
Mon Feb 10 16:50:46 AEDT 2020

Note that the table has no clob / blob columns and has only NUMBER with a max of 12 byte and VARCHAR with a max of 30 byte. The job fails with the same error even if the table is empty.

Sqoop command below:

sqoop import --connect 'jdbc:oracle:thin@(description=(address=(protocol=tcp)(host=xxx)(port=xxx)(connect_data=(SERVER=DEDICATED)(service_name=xxx)' --username xxx --password xxx --delete-target-dir --hive-import --hive-database default --hive-table TXN --hive-overwrite --table TXN --target-dir /tmp/TXN -m 1 --verbose

The issue seems to be coming from the host with service name (DF) but not with the same host on different service name(DW)

Could you please help in understanding the issue

and Connor said...

We should be able to detect the exact statement failing with a trace, but we need to auto enable tracing when you fire off this job, so we can do:

In your oraoop-site.xml file, uncomment the line:

alter session set events '10046 trace name context forever, level 8';

You user will need the "alter session" privilege.

Then give the job another run, and the trace file will contain all the statements including the one that failed.

If its not obvious, come back to us with a review and we'll take a look at the trace data.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Trace Debug help

Sridar Venkatesan, February 13, 2020 - 2:46 am UTC

Thanks a lot Connor,

I could see the below error in the trace,

PARSING IN CURSOR #140189741461552 len=38 dep=2 uid=531 oct=3 lid=531 tim=1581487365704207 hv=3472855628 ad='cabaf1a0' sqlid='d9ggvcr7gz4kc'
SELECT instance_number from v$instance
END OF STMT
PARSE #140189741461552:c=28,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=2529664852,tim=1581487365704207
EXEC #140189741461552:c=7,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,plh=2529664852,tim=1581487365704244
FETCH #140189741461552:c=27,e=27,p=0,cr=0,cu=0,mis=0,r=1,dep=2,og=1,plh=2529664852,tim=1581487365704287
STAT #140189741461552 id=1 cnt=1 pid=0 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=28 us cost=0 size=17 card=1)'
STAT #140189741461552 id=2 cnt=1 pid=1 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=19 us cost=0 size=6 card=1)'
STAT #140189741461552 id=3 cnt=1 pid=2 pos=1 obj=0 op='FIXED TABLE FULL X$KSUXSINST (cr=0 pr=0 pw=0 time=7 us cost=0 size=6 card=1)'
STAT #140189741461552 id=4 cnt=1 pid=2 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=11 us cost=0 size=0 card=1)'
STAT #140189741461552 id=5 cnt=1 pid=4 pos=1 obj=0 op='FIXED TABLE FULL X$QUIESCE (cr=0 pr=0 pw=0 time=1 us cost=0 size=0 card=1)'
STAT #140189741461552 id=6 cnt=1 pid=1 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=6 us cost=0 size=11 card=1)'
STAT #140189741461552 id=7 cnt=1 pid=6 pos=1 obj=0 op='FIXED TABLE FULL X$KVIT (cr=0 pr=0 pw=0 time=4 us cost=0 size=11 card=1)'
CLOSE #140189741461552:c=1,e=1,dep=2,type=3,tim=1581487365704360
EXEC #140190156924416:c=780,e=811,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=0,tim=1581487365704432
ERROR #140190156924416:err=6502 tim=1581487365704446

Could you please let me know if some trigger has to be disabled for this ?

Let me know if you need more info,
Connor McDonald
February 18, 2020 - 2:46 am UTC

so if you scroll back from that spot in the trace file to find the last occurence of the parse for cursor 140190156924416 - what is the code ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here