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.