I have an stored procedure PROC_AUTOPAY can execute successfully under sqlplus in Oracle 11.2. When the same stored procedure was called from PowerBuilder 12.5 application, it returns the below error.
ORA-20000: cdc_payroll_proc_autopay : 40 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "HRMDBA.PROC_AUTOPAY", line 314
ORA-06512: at line 1
The code from Line 294 to Line 316 is as follow:
294 exception
295 when no_fiscal_yr then
296 raise_application_error(-20000,
297 'cdc_payroll.proc_autopay : ' || block_id || ' fiscal year not found!' );
298 when no_pay_schedule then
299 raise_application_error(-20000,
300 'cdc_payroll.proc_autopay : ' || block_id || ' pay schedule not found!');
301 when no_pay_method then
302 raise_application_error(-20000,
303 'cdc_payroll.proc_autopay : ' || block_id || ' pay method not found!');
304 when no_br_bank_acct then
305 raise_application_error(-20000,
306 'cdc_payroll.proc_autopay : ' || block_id || ' branch bank account no. not found!');
307 when no_filename then
308 raise_application_error(-20000,
309 'cdc_payroll.proc_autopay : ' || block_id || ' filename not found!');
310 when db_error then
311 raise_application_error(-20000,
312 'cdc_payroll_proc_autopay : ' || block_id || ' ' || substr(sqlerrm, 1, 100));
313 when others then
314 raise_application_error(-20000,
315 'cdc_payroll_proc_autopay : ' || block_id || ' ' || substr(sqlerrm, 1, 100));
316 end;
From the logic above, the error line should be happened after the block_id = 40. Which is as follow:
block_id := 40;
lli_proc_status := -1;
ls_fiscal_yr := lin_fiscal_yr;
ls_pay_schedule := lin_pay_schedule;
ls_pay_method := lin_pay_method;
ls_br_bank_acct := lin_br_bank_acct;
ls_filename := lin_filename;
li_reccnt := 0;
block_id := 50;
The definition of those variables are as follow:
create or replace procedure proc_autopay (
...
lli_proc_status out integer
) is
...
ls_fiscal_yr payroll_autopay_details.fiscal_year_code%type;
ls_pay_schedule payroll_autopay_details.pay_schedule_code%type;
ls_pay_method payroll_autopay_details.pay_method_code%type;
ls_br_bank_acct company_table.bank_account_no%type;
ls_filename varchar2(12);
li_reccnt integer;
Please kindly help. Thanks in advance.