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