John, Maybe the buy_price_pkg.cnv_bpt_to_bp_id(5) function is returning a string? From what you provided, it looks like its returning null or maybe a blank character. Null shouldn't be a problem, but a blank string .... Judging by the position of the * in the error message(?) ,it seems that the problem is in converting the function result to a number.
Chaim John Weatherman <[EMAIL PROTECTED]>@fatcity.com on 09/04/2002 12:43:30 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Hi all, I'm writing a package to manipulate a partitioned table for the duhvelopers and have run into a weird query that I can't figure out.� I can convert a substring to a number in a select clause, but as soon as I try to use that same number in the where clause, the thing chokes.� Has anyone else seen anything like this? I'm on 9.0.1.3, Solaris 8.� The�buy_price_pkg.cnv_bpt_to_bp_id in the query is a custom function that returns a number corresponding with which partition is current, old, next, etc. SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) � 2� FROM�� all_tab_partitions � 3� WHERE� table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' � 4*������� and partition_name != 'TYPE01' SQL> / TO_NUMBER(SUBSTR(PARTITION_NAME,5,2)) BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5) ------------------------------------- --------------------------------- ����������������������������������� 9 ���������������������������������� 10 ���������������������������������� 11 ���������������������������������� 12 SQL> SELECT to_number(substr(partition_name,5,2)), buy_price_pkg.cnv_bpt_to_bp_id(5) � 2� FROM�� all_tab_partitions � 3� WHERE� table_name = 'BUY_PRICE_PIECE_TYPE_HISTORY' � 4�������� and partition_name != 'TYPE01' � 5*������ and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) SQL> / ����� and to_number(substr(partition_name,5,2)) = buy_price_pkg.cnv_bpt_to_bp_id(5) ERROR at line 5: ORA-01722: invalid number TIA, John P Weatherman Database Administrator Replacements Ltd. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
