| 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
Tom,
I have to disagree, because, the following works:
SQLWKS> select table_name, partition_name from dba_tab_partitions
2> where table_name = 'IPN_CYCLE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ IPN_CYCLE P200203 IPN_CYCLE P200204 IPN_CYCLE P200205 IPN_CYCLE P200206 IPN_CYCLE P200207 IPN_CYCLE P200208 IPN_CYCLE P200209 IPN_CYCLE P200210
2> where table_name = 'IPN_CYCLE' and to_number(substr(partition_name, 6, 2)) = 9;
TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ IPN_CYCLE P200209
Now, on my other databse, where LogMiner istalled creating partitioned tables:
SQL af_dba_at_AF> select table_name, partition_name, table_owner from dba_tab_partitions;
TABLE_NAME PARTITION_NAME TABLE_OWNER ------------------------------ ------------------------------ --------------
LOGSTDBY$APPLY_PROGRESS P0 SYSTEM LOGMNR_DICTSTATE$ P_LESSTHAN100 SYSTEM LOGMNR_DICTIONARY$ P_LESSTHAN100 SYSTEM LOGMNR_OBJ$ P_LESSTHAN100 SYSTEM LOGMNR_USER$ P_LESSTHAN100 SYSTEM LOGMNRC_GTLO P_LESSTHAN100 SYSTEM LOGMNRC_GTCS P_LESSTHAN100 SYSTEM LOGMNRC_GSII P_LESSTHAN100 SYSTEM LOGMNR_TAB$ P_LESSTHAN100 SYSTEM LOGMNR_COL$ P_LESSTHAN100 SYSTEM LOGMNR_ATTRCOL$ P_LESSTHAN100 SYSTEM LOGMNR_TS$ P_LESSTHAN100 SYSTEM LOGMNR_IND$ P_LESSTHAN100 SYSTEM LOGMNR_TABPART$ P_LESSTHAN100 SYSTEM LOGMNR_TABSUBPART$ P_LESSTHAN100 SYSTEM LOGMNR_TABCOMPART$ P_LESSTHAN100 SYSTEM LOGMNR_TYPE$ P_LESSTHAN100 SYSTEM LOGMNR_COLTYPE$ P_LESSTHAN100 SYSTEM LOGMNR_ATTRIBUTE$ P_LESSTHAN100 SYSTEM LOGMNR_LOB$ P_LESSTHAN100 SYSTEM LOGMNR_CDEF$ P_LESSTHAN100 SYSTEM LOGMNR_CCOL$ P_LESSTHAN100 SYSTEM LOGMNR_ICOL$ P_LESSTHAN100 SYSTEM LOGMNR_LOBFRAG$ P_LESSTHAN100 SYSTEM LOGMNR_INDPART$ P_LESSTHAN100 SYSTEM LOGMNR_INDSUBPART$ P_LESSTHAN100 SYSTEM LOGMNR_INDCOMPART$ P_LESSTHAN100 SYSTEM GP_CYCLE P200208 BOB GP_CYCLE P200209 BOB GP_CYCLE P200210 BOB GP_CYCLE P200211 BOB GP_CYCLE_PART_ID P200208 BOB GP_CYCLE_PART_ID P200209 BOB GP_CYCLE_PART_ID P200210 BOB GP_CYCLE_PART_ID P200211 BOB
similar statement does not work:
SQL af_dba_at_AF> select table_name, partition_name from dba_tab_partitions
2 where table_name = 'GP_CYCLE' and to_number(substr(partition_name, 6,
2)) = 9;
where table_name = 'GP_CYCLE' and to_number(substr(partition_name, 6, 2)) =
9
*ERROR at line 2:
because there are rows with partition_name (i.e. 'P_LESSTHAN100') not
convertible, when using "to_number(substr(partition_name, 6, 2))".
Even when I select from user_tab_partitions, which does not show LogMiner
tables/partitions, I get the same error, because user_tab_partitions view
is
based on the table, which contains rows with non-convertible values in
partition_name column.
So, the conclusion is: the auther of original e-mail has non-convertible values in partition_name column, like 'TYPEA01', which will cause an error, when doing "to_number(substr(partition_name,5,2))".
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
>
> ----- Original Message -----
> From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> To: <ORACLE-L_at_fatcity.com>
> Cc: <ineyman_at_perceptron.com>
> Sent: Wednesday, September 04, 2002 1:14 PM
> Subject: RE: Coercion issue
>
>
> > Igor,
> >
> > 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-----
> > From: Igor Neyman [mailto:ineyman_at_perceptron.com]
> > Sent: Wednesday, September 04, 2002 1:43 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Coercion issue
> >
> >
> > 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
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Wednesday, September 04, 2002 1:03 PM
> >
> >
> > > 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: 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).Received on Wed Sep 04 2002 - 16:12:35 CDT
 |
 |