Database Reference
In-Depth Information
63 dbms_sql.column_value_long
64 (g_cursor, 1, p_for, p_from-1,
65 l_buffer, l_buffer_len );
66 end if;
67 return l_buffer;
68 end substr_of;
69
70 end;
71 /
Package body created.
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):
EODA@ORA12CR1> select *
2 from (
3 select table_owner, table_name, partition_name,
4 long_help.substr_of
5 ( 'select high_value
6 from all_tab_partitions
7 where table_owner = :o
8 and table_name = :n
9 and partition_name = :p',
10 1, 4000,
11 'o', table_owner,
12 'n', table_name,
13 'p', partition_name ) high_value
14 from all_tab_partitions
15 where table_owner = user
16 )
17 where high_value like '%2014%'
18 /
TABLE_OWNER TABLE_NAME PARTITION_NAME HIGH_VALUE
----------- ----------- -------------- --------------------
EODA F_CONFIGS CONFIG_P_7 20140101
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 RAW
s 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 RAW
s 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 RAW
s over 32KB in PL/SQL itself. Java, C, C++, Visual Basic, or some other language
would have to be used.
