Re: Coercion issue

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 <john.weatherman_at_replacements.com>@fatcity.com on 09/04/2002 12:43:30 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: Chaim.Katz_at_Completions.Bombardier.com

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: ListGuru_at_fatcity.com (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).