Database Reference
In-Depth Information
29 return varchar2
30 as
31 l_buffer varchar2(4000);
32 l_buffer_len number;
33 begin
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 4,000—just like the built-in function
SUBSTR
:
34 if ( nvl(p_from,0) <= 0 )
35 then
36 raise_application_error
37 (-20002, 'From must be >=1 (positive numbers)' );
38 end if;
39 if ( nvl(p_for,0) not between 1 and 4000 )
40 then
41 raise_application_error
42 (-20003, 'For must be between 1 and 4000' );
43 end if;
44
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:
45 if ( p_query <> g_query or g_query is NULL )
46 then
47 if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
48 then
49 raise_application_error
50 (-20001, 'This must be a select only' );
51 end if;
52 dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
53 g_query := p_query;
54 end if;
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:
55 bind_variable( p_name1, p_bind1 );
56 bind_variable( p_name2, p_bind2 );
57 bind_variable( p_name3, p_bind3 );
58 bind_variable( p_name4, p_bind4 );
59
And now we can execute the query and fetch the row. Using
DBMS_SQL.COLUMN_VALUE_LONG
, we extract the
necessary substring of the
LONG
and return it:
60 dbms_sql.define_column_long(g_cursor, 1);
61 if (dbms_sql.execute_and_fetch(g_cursor)>0)
62 then
