Doubt - Standard size varchar2 in the parameter of a proc

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
tiagogen
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Tue, 14 Dec 2010 11:18 am
Location: SP

I have the following problem.

I have a proc that receives as parameter a varchar2 type.

Currently when complying for PROC, the standard size of this varchar2 is 200.
I need to pass a string with a size than 1000

If there is any variable of Oracle environment, or any suggestion of how to increase this standard size at the time of compilation?
Example:

Select all

create or replace 
PROCEDURE proc 
  ( 
    variavel VARCHAR2 --> não consigo passar tam > 200 
  ) 
IS  
vQtdeStop NUMBER; 
BEGIN 
vQtdeStop :=1; 
END proc;
Error in the execution:

Select all

ORA-06502: PL/SQL: erro: character string buffer too small numérico ou de valor 
ORA-06512: em line 8
rogenaro
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Fri, 30 Mar 2007 7:26 pm
Location: Londrina - PR
Rafael O. Genaro

What version of the bank?
at 10g at least I know the maximum size for a varchar2 last for a procedure is 32K. The problem would not be in another stretch of your logic, inside the procedure? In version 8 the limit is 4000.

What does this test return to you?

Select all

 
declare  
  i number; 
  procedure p1 ( v varchar2 ) 
  is 
  begin 
    dbms_output.put_line('OK: '||length(v)); 
  end p1; 
begin 
  i := 100; 
  p1( lpad('.', i  , '.') );  
  i := 1000; 
  p1( lpad('.', 1000 , '.') ); 
  i := 32000; 
  p1( lpad('.', 32000, '.') ); 
  i := 32767; 
  p1( lpad('.', 32767, '.') ); 
  i := 32768; 
  p1( lpad('.', 32768, '.') ); 
exception 
  when others then 
    dbms_output.put_line('Tamanho: '||i||' '||sqlerrm); 
end; 

Select all

 
OK: 100 
OK: 1000 
OK: 32000 
OK: 32767 
Tamanho: 32768 ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
tiagogen
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Tue, 14 Dec 2010 11:18 am
Location: SP

I discovered that the problem occurs when I pass the parameters by "running" from SQLDeveloper. When I called the proc inside a block, it worked! Thank you for the help!
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest