Oracle has a LONG RAW datatype that is used primarily in its system tables. There are cases where these tables need to be read from an application. There is a catch though, the common methods used to cast a datatype to another cannot be used in a plain SQL statement, but the developer must use PL/SQL in order to make this casting.
There is an alternative where with a plain SQL query we can retrieve the text representation of a LONG RAW datatype without using PL/SQL.
SELECT '' || regexp_substr( extractvalue( dbms_xmlgen.getxmltype('SELECT high_value FROM all_tab_partitions WHERE table_name=''' || TABLE_NAME || ''' AND table_owner = ''' || table_owner || ''' AND partition_name = ''' || partition_name || '''' ), '//text()' ), '''.*?''') FROM all_tab_partitions WHERE TABLE_NAME = 'MY_TABLE_WITH_PARTITIONS'
Our test case was that in a database we are working on a project, there was a table and one of its fields has some partitions defined, thus only allowing for this field to accept specific values. We needed a query to get those values and present them to the user with a
DropDownList. As you can see, the query has in its
WHERE clause a filter with the table name.
With the above query, we where able to retrieve these values, we only needed to apply a DISTINCT filter for our