Oracle Database Discussions

Hi there!

I am working on an Oracle data extract project. My output file will send to a DB2 database and a mainframe application. In my file, some fields' type is COMP-3. I use Oracle build-in function convert () to convert to EBCDIC. This works fine in SQL*PLUS. But when I use it in my PL/SQL program, I got "ORA-06502: PL/SQL: numeric or value error: character to number conversion error". Here is my program.

FUNCTION get_vd_pro_norm_mth_amt(p_account_rec IN ACCOUNT_ROW) return varchar2
as

v_scaled_amount varchar2(15);
BEGIN
select convert(a.scaled_amount, 'WE8EBCDIC500','US7ASCII')
into v_scaled_amount
from pin61_02.rate_bal_impacts_t a, pin61_02.rate_plan_t b
where b.poid_id0 = a.obj_id0 and
b.account_obj_db = p_account_rec.poid_db and
b.account_obj_id0 = p_account_rec.poid_id0 and
b.account_obj_type = p_account_rec.poid_type and
b.account_obj_rev = p_account_rec.poid_type;
return v_scaled_amount;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL;

END get_vd_pro_norm_mth_amt;

I guess the wrong data type of my variable v_scaled_amount generated the problem. I do not know which data type should I use to store EBCDIC data.

Thanks a lot!

Max