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 DropDownList
.
Excellent. This query saved a lot of efforts.
Brilliant. I had to replace REGEXP_SUBSTR() with TRIM() to use this against (*)_TAB_COLS.DATA_DEFAULT.
Hi, maybe a stupid question, but I only get NULL value from the 2050 partitions that are in my table?
‘||REGEXP_SUBSTR(EXTRACTVALUE(DBMS_XMLGEN.GETXMLTYPE(‘SELECTHIGH_VALUEFROMALL_T
——————————————————————————–
”||REGEXP_SUBSTR(EXTRACTVALUE(DBMS_XMLGEN.GETXMLTYPE(‘SELECTHIGH_VALUEFROMALL_T
——————————————————————————–
Hi Ulf,
Have you tried Glen’s suggestion (see comment) and replace REGEXP_SUBSTR() with TRIM()?
Thx, TRIM solved my problem thanks!
Regards
/Ulf
Impressive. But how do you use this with a simple SELECT statement used to create a view.
Hi Anthony,
I would try to use TO_LOB and then do a CAST.
For example: