Database Reference
In-Depth Information
If you have by now surmised that we could have selected
SYSDATE
from any table or view, you would
be correct! However, we would get a result for each and every row in the view or table we specified. Try
this query and you will see results like those shown:
SELECT USERNAME, SYSDATE FROM SYS.ALL_USERS ORDER BY USERNAME;
ANONYMOUS 09-JUN-11
APEX_030200 09-JUN-11
APEX_PUBLIC_USER 09-JUN-11
APPQOSSYS 09-JUN-11
APPSEC 09-JUN-11
APPUSR 09-JUN-11
APPVER 09-JUN-11
A row for each user is returned, but the only values we return are username and
SYSDATE
for each
row. This is a feature of the
SELECT
statement that we can use to our advantage. For example, if we select
all the managers from a list of personnel, we might insert the words “Pointy-Haired” between the first
and last names, like this:
SELECT FIRST_NAME || ' Pointy-Haired ' || LAST_NAME
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID IN (
SELECT DISTINCT MANAGER_ID FROM EMPLOYEES
);
The double pipes “||” is a way to
concatenate
strings in Oracle database. We have a sub-query in the
parentheses that selects all the
distinct
(unique)
EMPLOYEE_ID
s that are listed in the
MANAGER_ID
column
of
EMPLOYEES
. For all employees that have one of those
MANAGER_ID
s as their
EMPLOYEE_ID
, we concatenate
their
FIRST_NAME
, the words “Pointy-Haired” and their
LAST_NAME
, just for fun mind you.
We can select whatever we want for each row that is returned from a query. We can
SELECT
SYSDATE
for each row, or we can
SELECT
“First Pointy-Haired Boss” for each row.
Review The Roster of Participants
The following is a quick rundown of the participants in our test of the example code:
1.
Your client (Oracle command line) calling.
2.
The function on the Oracle database that encapsulates.
3.
The Java source named
MyApp4
(Listing 4-1) that queries.
4.
The Oracle database to select
SYSDATE.
When we created the
MyApp4
Java source, the Oracle database compiled the code and created the
package
pkg4
and the class
MyApp4
. Our Oracle function calls the static method
getOracleTime()
in the
MyApp4
class. In the
getOracleTime()
method, we open a connection to the Oracle database and request
the system time,
SYSDATE
which we then return as a
String
. Our function receives the time string and
returns it to our client as a
VARCHAR2
, and the client displays the date and time string.
You can see how these participants relate to one another in Figure 4-1.
