Coping with Legacy LONG Types-Datatypes
A question that arises frequently is, “What about the data dictionary in Oracle?” It is littered with LONG columns, and this makes using the dictionary columns problematic. For example, it is not possible using SQL to search the ALL_VIEWS dictionary view to find all views that contain the text HELLO:
$ sqlplus eoda/foo@PDB1
SQL> select * from all_views where text like ‘%HELLO%’; where text like ‘%HELLO%’
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
This issue is not limited to the ALL_VIEWS view; many views are affected:
SQL> select table_name, column_namefrom dba_tab_columnswhere data_type in ( ‘LONG’, ‘LONG RAW’ )
So, what is the solution? If you want to make use of these columns in SQL, then you’ll need to convert them to a SQL-friendly type. You can use a user-defined function for doing so. The following example demonstrates how to accomplish this using a LONG SUBSTR function that will allow you to effectively convert any 4000 bytes of a LONG type into a VARCHAR2 for use with SQL. When you are done, you’ll be able to query:
SQL> select * from select owner, view_name,long_help.substr_of’select textfrom dba_views
You’ve converted the first 4000 bytes of the TEXT column from LONG to VARCHAR2 and can now use a predicate on it. Using the same technique, you could implement your own INSTR, LIKE, and so forth for LONG types as well. In this book, I’ll only demonstrate how to get the substring of a LONG type.
The package we will implement has the following specification:
SQL> create or replace package long_helpauthid current_user
Package created.
Note that on line 2, we specify AUTHID CURRENT_USER. This makes the package run as the invoker, with all roles and grants in place. This is important for two reasons. First, we’d like the database security to not be subverted—this package will only return substrings of columns we (the invoker) are allowed to see. Specifically, that means this package is not vulnerable to SQL injection attacks—it is not running as the owner of the package but as the invoker. Second, we’d like to install this package once in the database and have its functionality available for all to use; using invoker rights allows us to do that. If we used the default security model of PL/SQL—definer rights—the package would run with the privileges of the owner of the package, meaning it would only be able to see data the owner of the package could see, which may not include the set of data the invoker is allowed to see.
The concept behind the function SUBSTR_OF is to take a query that selects at most one row and one column: the LONG value we are interested in. SUBSTR_OF will parse that query if needed, bind any inputs to it, and fetch the results programmatically, returning the necessary piece of the LONG value.
Next in this package is a private procedure, BIND_VARIABLE, which we’ll use to bind inputs passed to us by the caller. We implemented this as a separate private procedure only to make life easier; we want to bind only when the input name is NOT NULL. Rather than perform that check four times in the code for each input parameter, we do it once in this procedure:
procedure bind_variable( p_name in varchar2, p_value in varchar2 )isbeginif ( p_name is not null )
Next is the actual implementation of SUBSTR_OF in the package body. This routine begins with a function declaration from the package specification and the declaration for some local variables. L_BUFFER will be used to return the value, and L_BUFFER_LEN will be used to hold the length returned by an Oracle-supplied function:
function substr_of( p_query in varchar2,p_from in number,
p_for in number,
p_name1 in varchar2 default NULL,
p_bind1 in varchar2 default NULL,
p_name2 in varchar2 default NULL,
p_bind2 in varchar2 default NULL,
p_name3 in varchar2 default NULL,
p_bind3 in varchar2 default NULL,
p_name4 in varchar2 default NULL,
p_bind4 in varchar2 default NULL )
Now, the first thing our code does is a sanity check on the P_FROM and P_FOR inputs. P_FROM must be a number greater than or equal to 1, and P_FOR must be between 1 and 4000—just like the built-in function SUBSTR:
if ( nvl(p_from,0) <= 0 )thenraise_application_error(-20002, ‘From must be >=1 (positive numbers)’ ); end if;if ( nvl(p_for,0) not between 1 and 4000 )thenraise_application_error(-20003, ‘For must be between 1 and 4000’ ); end if;
Next, we’ll check to see if we are getting a new query that needs to be parsed. If the last query we parsed is the same as the current query, we can skip this step. It is very important to note that on line 47 we are verifying that the P_QUERY passed to us is just a SELECT—we will use this package only to execute SQL SELECT statements. This check validates that for us:
if ( p_query <> g_query or g_query is NULL )
if ( upper(trim(nvl(p_query,’x’))) not like ‘SELECT%’)
We are ready to bind the inputs to this query. Any non-NULL names that were passed to us will be bound to the query, so when we execute it, it finds the right row:
bind_variable( p_name1, p_bind1 );
bind_variable( p_name2, p_bind2 );
bind_variable( p_name3, p_bind3 );
bind_variable( p_name4, p_bind4 );
That’s it—you should be able to use that package against any legacy LONG column in your database, allowing you to perform many WHERE clause operations that were not possible before.
For example, you can now find all partitions in your schema such that the HIGH_VALUE has the year 2014 in it (please remember that if you do not have any tables with 2014 in the partition high value, you would not expect to see anything returned):
SQL> select * from select table_owner, table_name, partition_name, long_help.substr_of’select high_valuefrom all_tab_partitionswhere table_owner = 😮 and table_name = :n and partition_name
Using this same technique—that of processing the result of a query that returns a single row with a single LONG column in a function—you can implement your own INSTR, LIKE, and so on as needed.
This implementation works well on the LONG type, but will not work on LONG RAW types. LONG RAWs are not piecewise accessible (there is no COLUMN_VALUE_LONG_RAW function in DBMS_SQL). Fortunately, this is not too serious of a restriction since LONG RAWs are not used in the dictionary, and the need to “substring” so you can search on it is rare. If you do have a need to do so, however, you will not use PL/SQL unless the LONG RAW is 32KB or less, as there is simply no method for dealing with LONG RAWs over 32KB in PL/ SQL itself. Java, C, C++, Visual Basic, or some other language would have to be used.
Another approach is to temporarily convert the LONG or LONG RAW into a CLOB or BLOB using the TO_LOB built-in function and a global temporary table. Your PL/SQL procedure could be as follows:
Insert into global_temp_table ( blob_column ) select to_lob(long_raw_column) from t where…
This would work well in an application that occasionally needs to work with a single LONG RAW value. You would not want to continuously do that, however, due to the amount of work involved. If you find yourself needing to resort to this technique frequently, you should definitely convert the LONG RAW to a BLOB once and be done with it.