HIGH_VALUE (and other LONG columns) revisited

Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE


SQL> create table t1 ( x timestamp )
  2  partition by range ( x )
  3  ( partition p1 values less than ( timestamp '2019-01-01 00:00:00' ),
  4    partition p2 values less than ( timestamp '2019-02-01 00:00:00' )
  5  );

Table created.

SQL>
SQL> set serveroutput on
SQL> declare
  2    v long;
  3    d date;
  4  begin
  5    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  6    loop
  7      execute immediate 'select cast(:hv as date) from dual' into d using  i.high_value;
  8      dbms_output.put_line(d);
  9    end loop;
 10  end;
 11  /
01-JAN-19
01-FEB-19

PL/SQL procedure successfully completed.

You can see that we are casting the HIGH_VALUE as a date, and voila, out pops the values in a nice DATE datatype. That all seems well and good, but it only works for TIMESTAMP partitions and not the general case. Let’s replace our table with a DATE based partitioning scheme


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 ( x date )
  2  partition by range ( x )
  3  ( partition p1 values less than ( date '2019-01-01' ),
  4    partition p2 values less than ( date '2019-02-01' )
  5  );

Table created.

SQL>
SQL> set serveroutput on
SQL> declare
  2    d date;
  3  begin
  4    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  5    loop
  6      execute immediate 'select cast(:hv as date) from dual' into d using i.high_value;
  7      dbms_output.put_line(d);
  8    end loop;
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 6
ORA-06512: at line 6

There are plenty of methods to overcome the limitations of LONG. Here’s the one I typically use – just being a small variant on the code above


SQL> set serveroutput on
SQL> declare
  2    d date;
  3  begin
  4    for i in ( select high_value from user_tab_partitions where table_name = 'T1' )
  5    loop
  6      execute immediate 'select '||i.high_value||' from dual' into d;
  7      dbms_output.put_line(d);
  8    end loop;
  9  end;
 10  /
01-JAN-19
01-FEB-19

PL/SQL procedure successfully completed.

which I’ve also covered in video form here.

But just be careful using the CAST trick. It might cause you grief depending on the partitioning key you are using.

And if you’re wondering why the image associated with this post is that of a horse. It’s the old joke: “A horse walks into a bar, and the barman says: Why the LONG face?” 🙂

4 comments

Quanwen Zhao says:

July 9, 2019 at 9:23 am

Hello Connor :-),

In “cast(:hv as date)” this bind variable “:hv” I didn’t found it on your declaring statement, I think that whether it is “v long”? In general it doesn’t call a bind variable with never being declared.

Best Regards
Quanwen Zhao

1. Connor McDonald says:
  
  July 9, 2019 at 9:33 am
  
  :hv is a placeholder for whatever we bind *into* it, in this case, i.high_value
  
Noons says:

July 9, 2019 at 10:57 am

Heaven forbid Oracle would do the right thing and get rid of LONG from its dictionary…
After all, they have “declared” it unsupported when? 10 years ago?
😦

1. Connor McDonald says:
  
  July 9, 2019 at 12:37 pm
  
  True, but we’re getting there slowly…. For example, views and constraints
  
  SQL> desc user_constraints
  Name Null? Type
  —————————– ——– ——————–
  OWNER VARCHAR2(128)
  CONSTRAINT_NAME VARCHAR2(128)
  CONSTRAINT_TYPE VARCHAR2(1)
  TABLE_NAME VARCHAR2(128)
  SEARCH_CONDITION LONG
  SEARCH_CONDITION_VC VARCHAR2(4000) desc user_views
  Name Null? Type
  —————————– ——– ——————–
  VIEW_NAME NOT NULL VARCHAR2(128)
  TEXT_LENGTH NUMBER
  TEXT LONG
  TEXT_VC VARCHAR2(4000) <================
  TYPE_TEXT_LENGTH NUMBER
  TYPE_TEXT VARCHAR2(4000)
  OID_TEXT_LENGTH NUMBER
  OID_TEXT VARCHAR2(4000)
  VIEW_TYPE_OWNER VARCHAR2(128)
  VIEW_TYPE VARCHAR2(128)
  SUPERVIEW_NAME VARCHAR2(128)
  EDITIONING_VIEW VARCHAR2(1)
  READ_ONLY VARCHAR2(1)
  CONTAINER_DATA VARCHAR2(1)
  BEQUEATH VARCHAR2(12)
  ORIGIN_CON_ID NUMBER
  DEFAULT_COLLATION VARCHAR2(100)
  CONTAINERS_DEFAULT VARCHAR2(3)
  CONTAINER_MAP VARCHAR2(3)
  EXTENDED_DATA_LINK VARCHAR2(3)
  EXTENDED_DATA_LINK_MAP VARCHAR2(3)
  HAS_SENSITIVE_COLUMN VARCHAR2(3)