RE: DBA_TAB_PARTITIONS

               Many thanks!

Liz

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dominic Brooks Sent: Tuesday, January 16, 2018 4:34 PM To: oracle_at_dunbar-it.co.uk
Cc: Reen, Elizabeth; oracle-l_at_freelists.org Subject: Re: DBA_TAB_PARTITIONS

https://orastory.wordpress.com/2013/12/12/sql-utils-using-xml/<https://urldefense.proofpoint.com/v2/url?u=https-3A__orastory.wordpress.com_2013_12_12_sql-2Dutils-2Dusing-2Dxml_&d=DwMGaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=byTZxjseN8L32uAONu1y7ZESSvdko9GJAba_r3CW2PY&s=mtGqsr7ngFz63A0Z8927GMahjx_jMCAD0d64tQ-T5vo&e=> Sent from my iPhone

On 16 Jan 2018, at 21:28, Norman Dunbar <oracle_at_dunbar-it.co.uk<mailto:oracle_at_dunbar-it.co.uk>> wrote: 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__eur01.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Fellebaek.wordpress.com-252F2010-252F12-252F06-252Fconverting-2Da-2Dlong-2Dcolumn-2Dto-2Da-2Dclob-2Don-2Dthe-2Dfly-252F-26data-3D02-257C01-257C-257Cda36871121ab4070ae6908d55d282581-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636517349368903673-26sdata-3D2bfvOhnY-252BqDOkxycA-252Fo8E6iJKd34KR0XIcwAfjkG6CA-253D-26reserved-3D0&d=DwMGaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=byTZxjseN8L32uAONu1y7ZESSvdko9GJAba_r3CW2PY&s=1MUT4COp5x9kULx4nCfPyWG0ey7nE3rqtm1MwElXFOY&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__eur01.safelinks.protection.outlook.com_-3Furl-3Dhttps-253A-252F-252Fwww.toadworld.com-252Fplatforms-252Foracle-252Fb-252Fweblog-252Farchive-252F2014-252F05-252F20-252Flong-2Dto-2Dclob-2Dconversion-2Dconstraint-2Dconditions-2Dand-2Ddatabase-2Dd-2Di-2Dy-26data-3D02-257C01-257C-257Cda36871121ab4070ae6908d55d282581-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636517349368903673-26sdata-3DzGMvWBk24AO6l3sgCKu35niRnk4RbSjutiWTwhyKvpg-253D-26reserved-3D0&d=DwMGaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=byTZxjseN8L32uAONu1y7ZESSvdko9GJAba_r3CW2PY&s=bdi77oPVacVIGg-h5hAR9ck6uiRAZ-lZJ6BnP2FglmQ&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=https-3A__eur01.safelinks.protection.outlook.com_-3Furl-3Dhttp-253A-252F-252Fqdosmsq.dunbar-2Dit.co.uk-252Fblog-252F2016-252F08-252Fdropping-2Dtemporary-2Dtables-2Dwith-2Dbonus-2Dbroken-2Dcheck-2Dconstraints-252F-26data-3D02-257C01-257C-257Cda36871121ab4070ae6908d55d282581-257C84df9e7fe9f640afb435aaaaaaaaaaaa-257C1-257C0-257C636517349368903673-26sdata-3DMtbRVPfXI-252FPSN5Z-252FrHGhYIGqOvUK5Q9-252BZIeQ6k-252BdK3Q-253D-26reserved-3D0&d=DwMGaQ&c=j-EkbjBYwkAB4f8ZbVn1Fw&r=yWMFosURAngbt8VLeJtKLVJGefQxustAZ9UxecV7xpc&m=byTZxjseN8L32uAONu1y7ZESSvdko9GJAba_r3CW2PY&s=h3LhISYQ4aaKj4oUBTcn1zLHBClnkPSNjrvgy8kmBYo&e=>

HTH Cheers,
Norm.
On 16 January 2018 19:46:07 GMT+00:00, "Reen, Elizabeth " <dmarc-noreply_at_freelists.org<mailto: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 Received on Tue Jan 16 2018 - 22:50:51 CET