select parition_name,to_number(substr(partition_name,6)) NUM from user_tab_partitions;
-----------------------
PARTITION_NAMENUM
PART_20120320 20120320
PART_20120321 20120321
-----------------------------
SELECT parition_name,to_number(substr(partition_name,6)) NUM from user_tab_partitions WHERE to_number(substr(partition_name,6)) = 20120320;
出现ORA-01722: invalid number
user_tab_partitions是个视图,你的WHERE条件被推到底层表去了,碰到了其他格式的分区名。
加个ROWNUM看看行不行。
SELECT parition_name,to_number(substr(partition_name,6)) NUM
from (SELECT t.*,ROWNUM FROM user_tab_partitions t)
WHERE to_number(substr(partition_name,6)) = 20120320;
user_tab_partitions是个视图,你的WHERE条件被推到底层表去了,碰到了其他格式的分区名。
加个ROWNUM看看行不行。
SELECT parition_name,to_number(substr(partition_name,6)) NUM
from (SELECT t.*,ROWNUM FROM user_tab_partitions t)
WHERE to_number(substr(partition_name,6)) = 20120320;