Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-06502: PL/SQL: numeric or value error? Not to sure whats wrong.

ORA-06502: PL/SQL: numeric or value error? Not to sure whats wrong.

From: <jimi_xyz_at_hotmail.com>
Date: 8 Aug 2005 13:13:19 -0700
Message-ID: <1123530977.610018.240660@g47g2000cwa.googlegroups.com>


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';

 submitquery_api.query('daml', in_location_array, errcode, errmsg, :cv);
 if errcode <> 0 then
   dbms_output.put_line(errmsg);
 end if;
 end;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US