Database Reference
In-Depth Information
can be a complex array of objects or an object with many members, it is still a single return value—a
model that is analogous to Oracle functions. Oracle defines a stored procedure as code that performs a
process, and a stored function as code that returns a value. Of course, an Oracle procedure can return
multiple values through
OUT
parameters, and it would be handy if we could map that to a Java stored
procedure that returns multiple values, but we cannot easily do that. And of course, an Oracle function
can perform a process before returning a value. Oracle functions can also return multiple values through
OUT
parameters, but that is discouraged.
VARCHAR OR VARCHAR2
When creating database tables and writing PL/SQL, it is customary to define string columns and variables
as type VARCHAR2. For historical reasons, there is no VARCHAR1. Oracle database does currently
recognize VARCHAR as a synonym (a subtype in PL/SQL) for VARCHAR2, but Oracle warns against the use
of VARCHAR in their official documentation. You can read that warning at the following link:
f117
There is no reason to go against Oracle's advice, and I recommend VARCHAR2 when creating tables and
writing PL/SQL. There is an exception to using VARCHAR2 as the string type, and that exception occurs
when writing Java code to interface with the database. Later on when we define the parameters that we
intend to pass from Java to Oracle in an
Insert
or
Update
statement, we will call them
OracleTypes.VARCHAR
. There is no
OracleTypes.VARCHAR2
defined.
OracleTypes
is a class provided by
Oracle, so
VARCHAR2
as a type is conspicuous in its absence.
The important thing to remember here, is that one uses VARCHAR2 when creating a database column, or
when defining a PL/SQL variable, and one uses
OracleTypes.VARCHAR
when defining a Java variable or
parameter to be compatible with VARCHAR2 values from Oracle database.
Installing and Testing the Example Code
Run the code from Listing 4-1. You can run it using any of
SQL*Plus
,
SQL Developer
,
JDeveloper
,
TOAD
or
any other database code editor that enables execution of code. The first portion is one line to set your
role to
appsec_role
. The second portion of Listing 4-1 creates the Java Source named
MyApp4
. And the
third portion creates the
f_get_oracle_time
function.
After running the code, you can test the Java stored procedure by calling the Oracle function
f_get_oracle_time
. The function will return the current date and time from the Oracle database.
SELECT f_get_oracle_time FROM DUAL;
A note here about the
FROM DUAL
clause. In order to select data, the
SELECT
query must have a source.
The source will typically be an Oracle table or view. In this case, however, we are selecting the return
value from a function. By saying
FROM DUAL
we are fulfilling the requirement for a source in the
SELECT
query syntax, but essentially saying “from the database.”
DUAL
is actually a dummy table with one
column named
DUMMY
and one row with
DUMMY
= 'X'. You can see this from the following query:
SELECT * FROM DUAL;
