Thanks! I will look into these.
Liz
Elizabeth Reen
CPB Database Group Manager
718.248.9930 (Office)
Service Now Group: CPB-ORACLE-DB-SUPPORT
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Norman Dunbar
Sent: Tuesday, January 16, 2018 3:32 PM
To: Reen, Elizabeth ; oracle-l@xxxxxxxxxxxxx
Subject: 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/<https://urldefense.proofpoint.com/v2/url?u=https-3A__ellebaek.wordpress.com_2010_12_06_converting-2Da-2Dlong-2Dcolumn-2Dto-2Da-2Dclob-2Don-2Dthe-2Dfly_&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=8V99--zv5ic024WB8vHBXoDgoQej3MoQ7OAnVjYN7lk&s=eIqJ4GbOk7Zlr_bEExyUnTRnmnRlGJQMUWLRVhnV7KI&e=>
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<https://urldefense.proofpoint.com/v2/url?u=https-3A__www.toadworld.com_platforms_oracle_b_weblog_archive_2014_05_20_long-2Dto-2Dclob-2Dconversion-2Dconstraint-2Dconditions-2Dand-2Ddatabase-2Dd-2Di-2Dy&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=8V99--zv5ic024WB8vHBXoDgoQej3MoQ7OAnVjYN7lk&s=4T1qU1zZ8DdnFjNeRSuEI0Rgp63GtadU3BntKiBAXrA&e=>
- 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/<https://urldefense.proofpoint.com/v2/url?u=http-3A__qdosmsq.dunbar-2Dit.co.uk_blog_2016_08_dropping-2Dtemporary-2Dtables-2Dwith-2Dbonus-2Dbroken-2Dcheck-2Dconstraints_&d=DwMFaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=8V99--zv5ic024WB8vHBXoDgoQej3MoQ7OAnVjYN7lk&s=jpKnrnXEPbe2c9pDNqlR8Wyaxfngh5N2-jN9jJl_N_w&e=>
HTH
Cheers,
Norm.
On 16 January 2018 19:46:07 GMT+00:00, "Reen, Elizabeth "
<dmarc-noreply@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx>> 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.
