ORA-06502: PL/SQL: numeric or value error ~ for all STRING values

Question

I encountered error

ORA-06502: PL/SQL: numeric or value error at FETCH INTO (last line at below FUNCTION ).

I understand that this error may happen due to type mismatch of data in the cursor and its row type.

But if you see the screenshot (definition of HQP_IF_DATA_CONV_EXTRACT and 1 record that is going to be inserted), everything looks ok.

All are STRING type data and there is no limit exceeded.

Is there any better idea, otherwise how to troubleshoot further?

 FUNCTION INSERT_INTO_BU_STAGING(p_sql IN VARCHAR2) RETURN VARCHAR2 IS TYPE refCursor IS REF CURSOR; -- define weak REF CURSOR type a_cursor refCursor; rec HPQ_IF_DATA_CONV_EXTRACT_BU%ROWTYPE; j NUMBER; BEGIN j := 0; OPEN a_cursor FOR p_sql; LOOP FETCH a_cursor INTO rec;

table and record to be inserted

hi Yahia, i executed the sql stored in p_sql and the return value is in screenshot (right side). thanks. — iwan
– iwan, Commented Nov 13, 2011 at 2:53
please post the sql, without it we can only guess the answer....My guess would be that you have a where clause predicate that is generating the error. — Kevin Burton
– Kevin Burton, Commented Nov 13, 2011 at 9:58
@iwan I understand that BUT we need to see the SQL stored in p_sql to be able to help! — Yahia
– Yahia, Commented Nov 13, 2011 at 10:25

steve · Accepted Answer · 2011-11-13 09:17:39Z

The problem is the order of the columns used for the select might be different than the one how the record is defined. Therefore you might try to load a column in a record field that is defined with a smaller size than the data returned from the select.

Seems like a likely explanation, since the order of the columns shown in the two screenshots above is not the same.

Collectives™ on Stack Overflow

ORA-06502: PL/SQL: numeric or value error ~ for all STRING values

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related