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 on “HIGH_VALUE (and other LONG columns) revisited

  1. 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

  2. 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?
    😦

    • 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)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: