(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: 416

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

Related posts about plsql

Related posts about Oracle