Home » SQL & PL/SQL » SQL & PL/SQL » ORA 6502 Pl/SQL : numeric or value error
| ORA 6502 Pl/SQL : numeric or value error [message #36811] |
Sun, 23 December 2001 04:17  |
Harish Advani
Messages: 1
Registered: December 2001
|
Junior Member |
|
|
I'm getting the below mentioned error when we call stored procedure named as Fetch_Analysis_Results from OCI program. Below attached is the error message, OCI program,stored procedure.
Error Message
=============
executing fetch() for 1981 time
dbFetchOneRow(): Executing stored procedure
dbFetchOneRow(): Could not execute statment
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
B: Text of OCI program
======================
int dbFetchOneRow(AlarmInfo_t *alarmInfo)
{
text *plSqlProcedure = (text *)"BEGIN
FETCH_ANALYSIS_RESULT(:nodeType, :nodeName,:paramName, :severity, :thresholdValue, :functionType, :timeperiod, :varianceThreshold, :thresholdOperation, :thresholdFrequency, :profileType, :profileValue, :thresholdRate, :startTime, :endTime, :value);
END;";
C: Text of Stored procedure
===========================
CREATE or REPLACE PROCEDURE FETCH_ANALYSIS_RESULT(nodeType OUT varchar2, nodeName OUT varchar2, paramName OUT varchar2, severity OUT number,
thresholdValue OUT float, functiontype OUT number, timeperiod OUT number, varianceThreshold OUT float, thresholdOperation OUT varchar2, thr
esholdFrequency OUT number, profileType OUT number, profileValue OUT float, thresholdRate OUT float, startTime OUT number, endTime OUT numbe
r, val OUT float) AS
/* Create the cursor to fetch the record of Analysis_result table */
cursor result_cursor IS
select threshold_id,period_start_time,
period_end_time,value from analysis_result;
/* PLSQL block start */
thresholdid number(8);
nodeid number(4);
counter number(1);
BEGIN
counter:=0;
/* Open the cursor and fetch the record */
open result_cursor;
LOOP
fetch result_cursor into thresholdid,startTime,endTime,val;
EXIT WHEN result_cursor%NOTFOUND;
if counter = 0 then
counter:=counter + 1;
select node_id,title,severity,threshold_value,function_type,
period,threshold_variance,threshold_op,threshold_frequency,
profile_type,profile_value,threshold_rate into nodeid,paramName,severity,
thresholdValue,functiontype,timeperiod,varianceThreshold,thresholdOperation,
thresholdFrequency,profileType,profileValue,thresholdRate from
threshold_conf where threshold_id=thresholdid;
select node_type,node_name into nodeType,nodeName from node_info
where node_id=nodeid;
delete from analysis_result where threshold_id=thresholdid
and Period_start_time=startTime and Period_end_time=endTime
and value=val;
commit;
end if;
EXIT WHEN counter = 1;
END LOOP;
close result_cursor;
END;
----------------------------------------------------------------------
|
|
|
|
| Re: ORA 6502 Pl/SQL : numeric or value error [message #36817 is a reply to message #36811] |
Sun, 23 December 2001 07:21  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
check local variable declaration. normally you will get this error when you try to fetch or assign value into variable of less length than fetch column length.
example:
ley us say, you declare variable a of length 2.
if you fetch value into that variable of lengh more than that results error.
declare
a varchar2(2);
begin
select b into a from table;
-- if b is of length 3, it will result in error.
end;
----------------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 23:16:26 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
