the ORA-06502 error is often raised during
our stored procedures and PL/SQL blocks running.
How can it be prevented?
ora-06502
-
Guest
Ora-06502
The ORA-06502 error is raised ONLY 
after the Oracle server
patch 9.2.0.6 installation.
Repeated call of any functions containing at least one
VARCHAR2 type parameter (if the parameter value is greater then one in
the first call) raises this error.
Example:
in Oracle (SCOTT/TIGER schema):
sql:
create or replace function get_deptno(in_dname varchar2, in_loc varchar2) return number as
v_return number;
begin
select
sd.deptno into v_return
from dept sd
where sd.dname = in_dname
and sd.loc = in_loc;
return(v_return);
end;
in Delphi:
dfm:
object OraStoredProc1: TOraStoredProc
StoredProcName = 'SCOTT.GET_DEPTNO'
Session = OraSession1
SQL.Strings = (
'begin'
' :RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);'
'end;')
Debug = True
Left = 132
Top = 8
ParamData =
pas:
function TForm1.GetDeptNo(AName, ALoc: string): Integer;
begin
with OraStoredProc1 do
begin
ParamByName('IN_DNAME').AsString := AName;
ParamByName('IN_LOC').AsString := ALoc;
ExecProc;
Result := ParamByName('RESULT').AsInteger;
end;
end;
The first call completes error-free:
debug:
begin
:RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);
end;
:RESULT(FLOAT,OUT)=
:IN_DNAME(VARCHAR[5],IN)='SALES'
:IN_LOC(VARCHAR[7],IN)='CHICAGO'
The second call raises the error (LENGTH(IN_DNAME = 'RESEARCH') > LENGTH(IN_DNAME = 'SALES')):
debug:
begin
:RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);
end;
:RESULT(FLOAT,OUT)=30
:IN_DNAME(VARCHAR[8],IN)='RESEARCH'
:IN_LOC(VARCHAR[6],IN)='DALLAS'
after the Oracle serverpatch 9.2.0.6 installation.
Repeated call of any
functions containing at least oneVARCHAR2 type parameter (if the parameter value is greater then one in
the first call
) raises this error.Example:
in Oracle (SCOTT/TIGER schema):
sql:
create or replace function get_deptno(in_dname varchar2, in_loc varchar2) return number as
v_return number;
begin
select
sd.deptno into v_return
from dept sd
where sd.dname = in_dname
and sd.loc = in_loc;
return(v_return);
end;
in Delphi:
dfm:
object OraStoredProc1: TOraStoredProc
StoredProcName = 'SCOTT.GET_DEPTNO'
Session = OraSession1
SQL.Strings = (
'begin'
' :RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);'
'end;')
Debug = True
Left = 132
Top = 8
ParamData =
pas:
function TForm1.GetDeptNo(AName, ALoc: string): Integer;
begin
with OraStoredProc1 do
begin
ParamByName('IN_DNAME').AsString := AName;
ParamByName('IN_LOC').AsString := ALoc;
ExecProc;
Result := ParamByName('RESULT').AsInteger;
end;
end;
The first call completes error-free:
debug:
begin
:RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);
end;
:RESULT(FLOAT,OUT)=
:IN_DNAME(VARCHAR[5],IN)='SALES'
:IN_LOC(VARCHAR[7],IN)='CHICAGO'
The second call raises the error (LENGTH(IN_DNAME = 'RESEARCH') > LENGTH(IN_DNAME = 'SALES')):
debug:
begin
:RESULT := SCOTT.GET_DEPTNO(:IN_DNAME, :IN_LOC);
end;
:RESULT(FLOAT,OUT)=30
:IN_DNAME(VARCHAR[8],IN)='RESEARCH'
:IN_LOC(VARCHAR[6],IN)='DALLAS'
-
cis-wurzen
- Posts: 75
- Joined: Tue 04 Jan 2005 10:26