Under 8.1.5 partition_name is varchar2(30), as most of the names in data dictionary.
Igor Neyman, OCP DBA [EMAIL PROTECTED] ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
