Coding Still

Convert Long raw fields to varchar2 in Oracle with plain SQL

By 7 Comments

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.

Reader Interactions

Comments

  2. Brilliant. I had to replace REGEXP_SUBSTR() with TRIM() to use this against (*)_TAB_COLS.DATA_DEFAULT.

    Reply

  3. 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
    ——————————————————————————–

    Reply

  4. Impressive. But how do you use this with a simple SELECT statement used to create a view.

    CREATE OR REPLACE myempview 
AS
SELECT first_name,
             last_name, -- long column needing to convert to varchar2
             address
FROM myemp;
    Reply

    • Hi Anthony,

      I would try to use TO_LOB and then do a CAST.

      For example:

      SELECT
    first_name,
    CAST(TO_LOB(last_name) AS VARCHAR2)
FROM myemp
      Reply

Leave a Reply to _tasos Cancel reply

Your email address will not be published. Required fields are marked *

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