I Faced this error in a client while this procedure was executted:
create or replace procedure remove_indice (p_tabela in varchar2, p_indice in varchar2) is
v_qtde integer;
begin
select count(*) into v_qtde from user_indexes where index_name = upper(p_indice);
if v_qtde = 0 then
begin
dbms_output.put_line('indice ' || p_indice || ' nao removido (nao existe).');
end;
else
begin
execute immediate('drop index ' || p_indice);
dbms_output.put_line('indice ' || p_indice || ' removido com sucesso.');
end;
end if;
exception
when others then
dbms_output.put_line('nao foi possivel remover o indice ' || p_indice);
dbms_output.put_line('erro: ' || sqlerrm);
raise;
end;
S1000 6502 {[Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SITEF.REMOVE_INDICE", line 21 ORA-06512: at line 1
{call remove_indice ('conc_ticket_ll', 'conc_ticket_ll_indx1')}
Then I changed the procedure to this, because the error appears to happen in "raise":
create or replace procedure remove_indice (p_tabela in varchar2, p_indice in varchar2) is
v_qtde integer;
begin
select count(*) into v_qtde from user_indexes where index_name = upper(p_indice);
if v_qtde = 0 then
begin
dbms_output.put_line('indice ' || p_indice || ' nao removido (nao existe).');
end;
else
begin
execute immediate('drop index ' || p_indice);
dbms_output.put_line('indice ' || p_indice || ' removido com sucesso.');
end;
end if;
end;
Then I got this:
S1000 6502 {[Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 ORA-06512: at "SITEF.REMOVE_INDICE", line 13 ORA-06512: at line 1
In my environment the error doesn't occurs. The procedure simple checks if an index exists, and remove it if exists. If not exists, don't stop the application with error (it's an installer).
Some notes: 1- The connection is made through ODBC; 2- In the client environment, the error doesn't occurs through SqlPlus:
set serveroutput on;
begin
remove_indice ('conc_ticket_ll', 'conc_ticket_ll_indx1');
end;
indice conc_ticket_ll_indx1 removido com sucesso.
PL/SQL procedure successfully completed.
3- Oracle ODBC driver in the same version of mine, same OS.
