Database Reference
In-Depth Information
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.
The package body, the implementation, begins with two global variables. The
G_CURSOR
variable holds a
persistent cursor open for the duration of our session. This is to avoid having to repeatedly open and close the cursor
and to avoid parsing SQL more than we need to. The second global variable,
G_QUERY
, is used to remember the text
of the last SQL query we've parsed in this package. As long as the query remains constant, we'll just parse it once. So,
even if we query 5,000 rows in a query, as long as the SQL query we pass to this function doesn't change, we'll have
only one parse call:
EODA@ORA12CR1> create or replace package body long_help
2 as
3
4 g_cursor number := dbms_sql.open_cursor;
5 g_query varchar2(32765);
6
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:
7 procedure bind_variable( p_name in varchar2, p_value in varchar2 )
8 is
9 begin
10 if ( p_name is not null )
11 then
12 dbms_sql.bind_variable( g_cursor, p_name, p_value );
13 end if;
14 end;
15
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:
16
17 function substr_of
18 ( p_query in varchar2,
19 p_from in number,
20 p_for in number,
21 p_name1 in varchar2 default NULL,
22 p_bind1 in varchar2 default NULL,
23 p_name2 in varchar2 default NULL,
24 p_bind2 in varchar2 default NULL,
25 p_name3 in varchar2 default NULL,
26 p_bind3 in varchar2 default NULL,
27 p_name4 in varchar2 default NULL,
28 p_bind4 in varchar2 default NULL )
