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

Answered by: Connor McDonald - Last updated: February 18, 2020 - 2:46 am UTC

Category: Database Administration - 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 we 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.

and you rated our response

  (1 rating)

Reviews

Trace Debug help

February 13, 2020 - 2:46 am UTC

Reviewer: Sridar Venkatesan from Australia

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

Followup  

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 ?