| 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
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)
*
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). Received on Wed Sep 04 2002 - 12:03:27 CDT
 |
 |