(PL/SQL)Oracle stored procedure parameter size limit(VARCHAR2)
Posted
by Jude Lee
on Stack Overflow
See other posts from Stack Overflow
or by Jude Lee
Published on 2010-04-08T10:29:10Z
Indexed on
2010/04/08
10:33 UTC
Read the original article
Hit count: 445
Hello, there. I have an problem with my oracle stored procedure as following.
codes :
CREATE OR REPLACE PROCEDURE APP.pr_ap_gateway (
aiov_req IN OUT VARCHAR2,
aov_rep01 OUT VARCHAR2,
aov_rep02 OUT VARCHAR2,
aov_rep03 OUT VARCHAR2,
aov_rep04 OUT VARCHAR2,
aov_rep05 OUT VARCHAR2
)
IS
v_header VARCHAR (100);
v_case VARCHAR (4);
v_err_no_case VARCHAR (5) := '00004';
BEGIN
DBMS_OUTPUT.ENABLE (1000000);
aov_rep01 := lpad(' ', 190, ' ');
dbms_output.put_line('>> ['||length(aov_rep01)||']');
aov_rep01 := lpad(' ', 199, ' ');
dbms_output.put_line('>> ['||length(aov_rep01)||']');
aov_rep01 := lpad(' ', 200, ' ');
dbms_output.put_line('>> ['||length(aov_rep01)||']');
aov_rep01 := lpad(' ', 201, ' ');
dbms_output.put_line('>> ['||length(aov_rep01)||']');
END pr_ap_gateway;
/
results :
>> [190]
>> [199]
>> [200]
and then error 'buffer overflow' ORA-06502: PL/SQL: ?? ?? ? ??: ??? ??? ?? ????
I know that VARCHAR2 type can contain 32KB in PL/SQL. But, in my test, VARCHAR2 parameter contains only 200 Bytes. What's wrong with this situation?
This procedure will called by java daemon program. So, There's no declaration of parameters size before calling procedure.
Thanks in advance for your reply.
© Stack Overflow or respective owner