Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> ORA-06502: PL/SQL: numeric or value error? Not to sure whats wrong.
Any one have any idea whats wrong with this code? Other than the fact
its a bit messy...
IF in_location_array.count > 1 THEN
sql_contains_str := 'contains('||in_location_array(1)||',
''%'||in_word_to_search||'%'', 1) > 0';
FOR i in 2..in_location_array.count LOOP
sql_contains_str := sql_contains_str || ' OR contains('
||in_location_array(i)|| ', ''%'||in_word_to_search||'%'', '|| i ||') >
0';
END LOOP;
FOR j in 2..in_location_array.count LOOP
sql_score_str := sql_score_str || ' + score('||j||')';
END LOOP;
sql_orderby_str := 'ORDER BY score(1) ';
sql_final := sql_score_str ||' "total_score" FROM project WHERE ' || sql_contains_str; sql_final := 'SELECT DISTINCT projectid, jobordernumber, score(1)'||sql_final;
ELSE
sql_final := 'SELECT DISTINCT projectid, jobordernumber, title,
score(1) ' ||
'FROM project ' ||
'WHERE contains('||in_location_array(1)||',
''%'||in_word_to_search||'%'', 1) > 0 ';
END IF;
testscript
declare
errcode number(10);
errmsg varchar2(255);
cv SUBMITQUERY_API.curtype;
in_location_array SUBMITQUERY_API.charArray;
begin
in_location_array(1) := 'title'; in_location_array(2) := 'scope'; in_location_array(3) := 'objectives'; in_location_array(4) := 'approach';
The thing is when i take this statement
sql_final := 'SELECT DISTINCT projectid, jobordernumber, score(1)'
and remove jobordernumber it works fine but when i add any more
columns to the select clause i get this error..
ORA-06502: PL/SQL: numeric or value error
Thank you,
Jimi
Received on Mon Aug 08 2005 - 15:13:19 CDT