Re: DBA_TAB_PARTITIONS

Evening Elizabeth,

Toad etc don't need to convert as they are selecting the LONG data in it's entirety. Similar to how they can show a NUMBER without converting using TO_CHAR() - these utilities fetch the data into local variables in whatever language they are written in - C, Delphi etc - so an Oracle LOND (or VARCHAR2 etc) will be converted into a C char[] or similar 'on the fly'.

LONGs are a monumental PITB when you want to do anything with them.

This blog post might help show you how to convert to a CLOB, which you can then use DBMS_LOB.SUBSTR etc to slice up. https://ellebaek.wordpress.com/2010/12/06/converting-a-long-column-to-a-clob-on-the-fly/

There are some less frantic solutions in PL/SQL at https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/05/20/long-to-clob-conversion-constraint-conditions-and-database-d-i-y - near the end - it looks like PL/SQL does implicit conversions. Test and see how you get on.

Finally, I had a similar problem myself looking for broken check constraints which suffered from a similar LONG problem. As the amount of data was small, I created a temporary table as described at http://qdosmsq.dunbar-it.co.uk/blog/2016/08/dropping-temporary-tables-with-bonus-broken-check-constraints/

HTH Cheers,
Norm.

On 16 January 2018 19:46:07 GMT+00:00, "Reen, Elizabeth " <dmarc-noreply_at_freelists.org> wrote:
>We are trying to create a sql which will create a script of partitions
>to compress which are older than a certain date. That information is
>kept in High_value. When displayed high_value looks like this
>
>TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
>'NLS_CALENDAR=GREGORIAN')
>
>So the first thing we did was to query where high_value like
>'TO_DATE%'. We got an invalid character error. Long story short, we
>discovered that high_value's data type is long. There must be a way to
>translate this into text. SQLplus, Sqldeveloper, and TOAD all do that.
> The question is how do they do it? Does anyone know how it is done?
>
>Thanks,
>
>Liz

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
--
http://www.freelists.org/webpage/oracle-l