Using long datatype is a problem we seldom face with new applications. But some old may still have it if they have not managed to convert to somethings easier to work with. But where I most often encounter it is in Oracles tables. The place that by far most often pops up its ugly head is high_value in *_tab_partitions. Just the other day a colleague was stuck on how to grab a portion of its content in SQL.

“You cannot” was not really the expected answer. My clarification “more work than you will want to do” wasn’t appreciated much more. We agreed that as he was going to use it in PL/SQL anyway, it was enough that it works in PL/SQL.

The issue is this:

select to_char(high_value) from all_tab_partitions;
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

select to_char(high_value) from all_tab_partitions;
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

So a long cannot be converted implicitly or explicitly to varchars. How do you deal with it then? The standard handling of it is to do SQL in PL/SQL and operate on the long there.

begin
  for r in (select high_value from all_tab_partitions)
  loop
    dbms_output.put_line(substr(r.high_value,1));
  end loop;
end;
/

That does work and you can of course then use it for any kind of PL/SQL magic you want. Like pipelined functions.

If you’re like me, you will think “aha, then I can fix this with a refactoring function”.

with
  function with_function(p_hv in long) return number is
  begin
    return substr(p_hv,1);
  end;
select with_function(high_value) from all_tab_partitions
/
ERROR at line 6:
ORA-00997: illegal use of LONG datatype

OK then, how about a function that just returns the varchar?

create function l2vc(lng in long) return varchar2 is
  begin
    return substr(lng, 1, 10);
  end;
/

select l2vc(high_value) from all_tab_partitions;
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

So SQL does not play nicely with LONG. Can you add more complexity and get it to be somewhat possible. Sure, but for most that is overkill. Just do what you need in PL/SQL.

Since it is possible, why hasn’t Oracle fixed it a long time ago? Oracle’s own tables are probably not fixed due to legacy restrictions on stuff that expects these columns to be long. As for providing means in SQL to support it, it would not help. You are not advised to ever use LONG, making it easier to do so is not helping the situation so do not expect a supported way for SQL to do this. And do not put LONG columns into your application.