On Tue, 2008-03-18 at 14:06 -0700, Pompiliu wrote:
...
> $dbh->func(1000000000, 'dbms_output_enable');
> right after connect.
> The problem is that I am getting only part of the
> result and the error I am getting is ORA 06502.
ORA-6502 is often the result of size mismatch between variables and
columns in tables. At the end of this message is a demonstration script
of this kind of error.
In your case you exceeding the limits of the dbms_output package.
(Including the error text of the ORA-6502 would have been most helpful
here)
The maximum string lenth is 32767, easily demonstrated with put_line
Wrote file afiedt.buf
1 begin
2 dbms_output.put_line(rpad('X',32767,'X'));
3* end;
SQL> /
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
...
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
PL/SQL procedure successfully completed.
13:20:05 SQL>2
2* dbms_output.put_line(rpad('X',32767,'X'));
13:20:09 SQL>c/67/68
2* dbms_output.put_line(rpad('X',32768,'X'));
13:20:16 SQL>/
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too
small
ORA-06512: at line 2
When all else fails, read the documentation.
Jared
-- 6502.sql
-- demonstrate common causes of ORA-6502
-- Jared Still
-- See note # 20063.1 on MetaLink
drop table t6502;
create table t6502 ( char_data varchar2(20), number_data number(6) );
insert into t6502 values('100', 10000);
set serveroutput on size 1000000
declare
small_char varchar2(2);
char_ok t6502.char_data%type;
small_int number(2);
number_ok t6502.number_data%type;
begin
-- varchar2 data
begin
-- this one succeeds
select char_data into char_ok
from t6502;
-- this one fails
select char_data into small_char
from t6502;
exception
when value_error then
dbms_output.put_line('The value ' || char_ok || ' is too large
for the character variable small_char');
end;
-- numeric data
begin
-- this one succeeds
select char_data into number_ok
from t6502;
-- this one fails
select char_data into small_int
from t6502;
exception
when value_error then
dbms_output.put_line('The value ' || number_ok || ' is too
large for the numeric variable small_int');
end;
end;
/
drop table t6502;
