| 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
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
John,
Oracle will apply both "where" clauses: table_name =3D =
'BUY_PRICE_PIECE_TYPE_HISTORY' and to_number(substr(partition_name,5,2)) =
=3D buy_price_pkg.cnv_bpt_to_bp_id(5)
to each row at the same time.
If you want to force oracle to do it sequencially (first - table_name, = then - the other one), use in-line view:
SELECT to_number(substr(partition_name,5,2)), =
buy_price_pkg.cnv_bpt_to_bp_id(5)
FROM (select partition_name from
all_tab_partitions
WHERE table_name =3D 'BUY_PRICE_PIECE_TYPE_HISTORY'
and partition_name !=3D 'TYPE01')
WHERE to_number(substr(partition_name,5,2)) =3D =
buy_price_pkg.cnv_bpt_to_bp_id(5)
This should do it for you.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
=20
There are partitions with names that don't have the 5th and 6th =
characters equal to
numbers, but not for this table:
SQL> select partition_name from all_tab_partitions=20
2 where table_name =3D 'BUY_PRICE_PIECE_TYPE_HISTORY';
PARTITION_NAME
-----Original Message-----
From: Igor Neyman [mailto:ineyman_at_perceptron.com]
Sent: Wednesday, September 04, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Coercion issue
John,
Do you have other partitions with such names, that 5th and 6th = characters are not convertible into numbers?
Like: 'TYPEA1'?
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
=20
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=20
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 =3D 'BUY_PRICE_PIECE_TYPE_HISTORY'
4* and partition_name !=3D '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 =3D 'BUY_PRICE_PIECE_TYPE_HISTORY'
4 and partition_name !=3D 'TYPE01'
5* and to_number(substr(partition_name,5,2)) =3D =
buy_price_pkg.cnv_bpt_to_bp_id(5)
SQL> /
and to_number(substr(partition_name,5,2)) =3D =
buy_price_pkg.cnv_bpt_to_bp_id(5)
*
ERROR at line 5:
ORA-01722: invalid number
TIA,
John P Weatherman=20
Database Administrator=20
Replacements Ltd.=20
------=_NextPart_000_0302_01C25435.D9D68630
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; =charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4731.2200" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2>John,</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>Oracle will apply both "where" clauses: <FONT=20color=3D#0000ff>table_name =3D 'BUY_PRICE_PIECE_TYPE_HISTORY' and=20 to_number(substr(partition_name,5,2)) =3D=20 buy_price_pkg.cnv_bpt_to_bp_id(5)</FONT></FONT></DIV>
<DIV><FONT size=3D2> to each row at the same time.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>If you want to force oracle to do it sequencially =(first -=20
<DIV><FONT size=3D2>This should do it for you.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV>Igor Neyman, OCP DBA<BR><A=20
class=3D711230717-04092002>PARTITION_NAME<BR>----------------------------=--<BR>TYPE01<BR>TYPE09<BR>TYPE10<BR>TYPE11<BR>TYPE12<BR></SPAN></FONT></D= IV>
size=3D2>-----Original Message-----<BR><B>From:</B> Igor Neyman=20 [mailto:ineyman_at_perceptron.com]<BR><B>Sent:</B> Wednesday, September = 04,=20
2002 1:14 PM<BR><B>To:</B> Multiple recipients of list=20 ORACLE-L<BR><B>Subject:</B> Re: Coercion issue<BR><BR></DIV></FONT>
<DIV><FONT size=3D2>John,</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>Do you have other partitions with such names, =
that 5th and=20
6th characters are not convertible into numbers?</FONT></DIV>
<DIV><FONT size=3D2>Like: 'TYPEA1'?</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV>Igor Neyman, OCP DBA<BR><A=20
<BR><BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- = </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A title=3Djohn.weatherman_at_replacements.com=20
href=3D"mailto:john.weatherman_at_replacements.com">John =
Weatherman</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3DORACLE-L_at_fatcity.com=20
href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list=20
ORACLE-L</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, September =
04, 2002=20
12:43 PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Coercion =
issue</DIV>
<DIV><BR></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>Hi all,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>I'm writing a package to manipulate a =
partitioned=20
table for the duhvelop</SPAN></FONT><FONT face=3DArial =
color=3D#0000ff=20
size=3D2><SPAN class=3D024323315-04092002>ers and have run=20
into</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>a weird query that I can't figure =
out. I=20
can convert a substring to a number in a select=20
clause,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>but as soon as I try to use that same =
number in=20
the where clause, the thing chokes. Has </SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>anyone else seen anything like=20
this?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>I'm on 9.0.1.3, Solaris 8. =20
The buy_price_pkg.cnv_bpt_to_bp_id in the query is a=20
custom</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>function that returns a number =
corresponding with=20
which partition is current, old, next,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>etc.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>SQL> SELECT=20
to_number(substr(partition_name,5,2)),=20
buy_price_pkg.cnv_bpt_to_bp_id(5)<BR> 2 =
FROM =20
all_tab_partitions<BR> 3 WHERE table_name =3D=20
'BUY_PRICE_PIECE_TYPE_HISTORY'<BR> =20
4* and partition_name =
!=3D=20
'TYPE01'<BR>SQL> /</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff=20
size=3D2>TO_NUMBER(SUBSTR(PARTITION_NAME,5,2))=20
=
BUY_PRICE_PKG.CNV_BPT_TO_BP_ID(5)<BR>------------------------------------=
-=20
=
---------------------------------<BR> =
&=
nbsp; &n=
bsp; =20
=
9 =
&=
nbsp; =20
=
9<BR> &n=
bsp; &nb=
sp; =20
=
10  =
; =
=20
=
9<BR> &n=
bsp; &nb=
sp; =20
=
11  =
; =
=20
=
9<BR> &n=
bsp; &nb=
sp; =20
=
12  =
; =
=20
9</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2>SQL> SELECT=20
to_number(substr(partition_name,5,2)),=20
buy_price_pkg.cnv_bpt_to_bp_id(5)<BR> 2 =
FROM =20
all_tab_partitions<BR> 3 WHERE table_name =3D=20
'BUY_PRICE_PIECE_TYPE_HISTORY'<BR> =20
4 and =
partition_name !=3D=20
'TYPE01'<BR> 5* and=20
to_number(substr(partition_name,5,2)) =3D=20
buy_price_pkg.cnv_bpt_to_bp_id(5)<BR>SQL>=20
/<BR> and=20
to_number(substr(partition_name,5,2)) =3D=20
=
*<BR>ERROR at line 5:<BR>ORA-01722: invalid = number<BR></FONT></DIV>
<DIV><FONT face=3DArial color=3D#0000ff size=3D2><SPAN=20
class=3D024323315-04092002>TIA,</SPAN></FONT></DIV>
<P><FONT face=3D"Courier New" size=3D2>John P Weatherman</FONT> =
<BR><FONT=20
face=3D"Courier New" size=3D2>Database Administrator</FONT> =
<BR><FONT=20
face=3D"Courier New" size=3D2>Replacements Ltd.</FONT>=20
</P></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0302_01C25435.D9D68630--
-- 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 - 17:03:23 CDT
 |
 |