Skip to Main Content
  • Questions
  • How to fetch part of a string for LONG datatype

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jaswanth.

Asked: February 14, 2020 - 4:27 pm UTC

Last updated: March 07, 2023 - 1:41 am UTC

Version: 12c

Viewed 1000+ times

You Asked

HI,

I am writing a query to find missing table partitions for next year using all_tab_partitions table, I am able to fetch the records with the help of column partition positions, but I have to extract the last partition date (YYYY-MM-DD) from HIGH_VALUE which is a Long data type

Here is the value of HIGH_VALUE = TO_DATE(' 2023-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

I want the value as

HIGH_VALUE
````````````
2023-12-31

If I try to cast or use REGEXP its throwing error inconsistent datatypes: expected CHAR got long

and Connor said...

Here's a video walk through of one way to do it


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

bborba, February 22, 2023 - 5:36 pm UTC

Non sense, a video instead of 2, 3 lines of sql.
Connor McDonald
February 27, 2023 - 1:38 am UTC

Always a joy to work on AskTom :-)

Learning is the goal.

Daecam, March 03, 2023 - 3:48 pm UTC

Thank you Connor for the video. This instructional video solved my issue in under 10 minutes. I have added something new to my tool box. Unlike others, I want to learn from something from the Ask Tom team, without expectations that you would do my job for me. After 40 years in the industry, learning something new each day is still the goal. Many times, I would not have reached that goal, without Ask Tom. Keep up the good work.
Connor McDonald
March 07, 2023 - 1:41 am UTC

thanks for the kind feedback

A reader, March 17, 2023 - 9:22 pm UTC


Getting High Value for Partition

Raj Kusampudi, May 12, 2023 - 4:24 am UTC

Very useful Video, Learned different methods

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library