(PL/SQL)Oracle stored procedure parameter size limit(VARCHAR2)
- by Jude Lee
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.
