| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Coercion issue
Sory Igor - I misread who sent the original email.
John,
If the buy_price_pkg.cnv_bpt_to_bp_id function returns a number, this makes perfect sense. Oracle is attempting to convert the partition_name column to a number before your instr function gets to do its magic.
Try doing the following:
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 substr(partition_name,5,2) =to_char(buy_price_pkg.cnv_bpt_to_bp_id(5)) /
I bet you a dollar it works.
Remember, when it comes to comparisons, Oracle will convert the database column to match the literal data type. In your case, the character values in the 'partition_name' column will not convert to a number.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, September 04, 2002 1:43 PM
To: Multiple recipients of list ORACLE-L
Under 8.1.5 partition_name is varchar2(30), as most of the names in data dictionary.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
> Unless my memory is failing (more than likely) I thought you couldn't use
> the partition_name like that in the where clause of patitioned tables as
it
> is a LONG ??
>
> Mine was 8.0.6 but I'm sure this is still the case for at the very least
8i.
> I ran into the same problem a while ago.
>
> HTH
>
> -----Original Message-----
> Sent: 04 September 2002 17:44
> To: Multiple recipients of list ORACLE-L
>
>
> 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 9
> 10 9
> 11 9
> 12 9
>
> 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.
>
>
>
> *********************************************************************
>
> The information contained in this communication is
> confidential, is intended only for the use of the recipient
> named above, and may be legally privileged.
> If the reader of this message is not the intended
> recipient, you are hereby notified that any dissemination,
> distribution, or copying of this communication is strictly
> prohibited.
> If you have received this communication in error,
> please re-send this communication to the sender and
> delete the original message or any copy of it from your
> computer system. Thank You.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Robertson Lee - lerobe
> INET: lerobe_at_acxiom.co.uk
>
> 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: ineyman_at_perceptron.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us 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).Received on Wed Sep 04 2002 - 15:59:21 CDT
 |
 |