Home » SQL & PL/SQL » SQL & PL/SQL » How to find the right partition or its existence to a value
How to find the right partition or its existence to a value [message #287748] Thu, 13 December 2007 06:27 Go to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Hello!

I have a value (current_timestamp) and want to see if there is already the right partition for this value.
Partition definitions:
PARTITION "P200701"  VALUES LESS THAN (TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))


I've already wrote something like this:
SELECT HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME'
AND TABLE_OWNER = 'USERNAME'
AND current_timestamp < high_value;

I get the error message: 'illegal use of long datatype'.
What did I wrong?

BUT when I write this:
SELECT HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME'
AND TABLE_OWNER = 'USERNAME'
AND current_timestamp < TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');

it is working perfectly.
Is there any other way how to find the right partition for a value?

Thanks,
Alex
Re: How to find the right partition or its existence to a value [message #287770 is a reply to message #287748] Thu, 13 December 2007 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I have a value (current_timestamp) and want to see if there is already the right partition for this value.

It is otherwise there is a big bug in Oracle.

Regards
Michel

[Updated on: Thu, 13 December 2007 07:09]

Report message to a moderator

Re: How to find the right partition or its existence to a value [message #287771 is a reply to message #287748] Thu, 13 December 2007 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For you question: high_value is of type LONG and you can't compare LONG with anything. It is an implementation restriction.

Regards
Michel
Re: How to find the right partition or its existence to a value [message #287772 is a reply to message #287748] Thu, 13 December 2007 07:15 Go to previous messageGo to next message
novalex
Messages: 19
Registered: November 2007
Junior Member
Ok, so how can I convert LONGto CHAR? I want to use the entry of high_value in my comparison of dates.
Re: How to find the right partition or its existence to a value [message #287775 is a reply to message #287772] Thu, 13 December 2007 07:21 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to create a PL/SQL function like the one in http://www.orafaq.com/forum/m/276306/102589/?srch=to_varchar#msg_276306

Regards
Michel


Previous Topic: Count grouped by 6month period
Next Topic: Is DMBS_LOCK.REQUEST First-In First-Out?
Goto Forum:
  


Current Time: Fri Dec 09 15:31:31 CST 2016

Total time taken to generate the page: 0.09022 seconds