Database Reference
In-Depth Information
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 4,000 bytes of a LONG
type into a VARCHAR2 for use with SQL. When you are done, you'll be able to query:
EODA@ORA12CR1> select *
2 from (
3 select owner, view_name,
4 long_help.substr_of( 'select text
5 from dba_views
6 where owner = :owner
7 and view_name = :view_name',
8 1, 4000,
9 'owner', owner,
10 'view_name', view_name ) substr_of_view_text
11 from dba_views
12 where owner = user
13 )
14 where upper(substr_of_view_text) like '%INNER%'
15 /
You've converted the first 4,000 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:
EODA@ORA12CR1> create or replace package long_help
2 authid current_user
3 as
4 function substr_of
5 ( p_query in varchar2,
6 p_from in number,
7 p_for in number,
8 p_name1 in varchar2 default NULL,
9 p_bind1 in varchar2 default NULL,
10 p_name2 in varchar2 default NULL,
11 p_bind2 in varchar2 default NULL,
12 p_name3 in varchar2 default NULL,
13 p_bind3 in varchar2 default NULL,
14 p_name4 in varchar2 default NULL,
15 p_bind4 in varchar2 default NULL )
16 return varchar2;
17 end;
18 /
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
 
Search WWH ::




Custom Search