Using Oracle’s Built-In Functions 177
6
T
ABLE
6.9
Masks Used with the
ROUND
and
TRUNC
Functions
Mask Options Description
CC
,
SCC
Rounds or truncates to the century
YYYY
,
SYYYY
,
YEAR
,Truncates to the year or rounds up to the next year after July 1st
SYEAR
,
YYY
,
YY
,
Y
IYYY
,
IYY

Dy
,
D
Truncates or rounds to the first day of the week
HH24
,
HH12
,
HH
Truncates to the hour or rounds up to the next hour on or after 30 minutes
MI
Truncates to the minute or rounds up on or after 30 seconds
Now that you have seen all the possible masking options, try the
TRUNC
function by
testing it with different examples. You will first truncate the time from the system date.
Remember, you still see the time displayed, but if you use
TRUNC
on all dates, the time is
always 12:00 AM. instead of the time the date was assigned; therefore, all dates can be
calculated properly regardless of time. Go ahead and execute the SQL code in Listing 6.19.
L
ISTING
6.19
Removing the Time from
SYSDATE
1: SELECT TO_CHAR(TRUNC(SYSDATE),’MM/DD/YYYY HH:MM:SS AM’)
2: “Today’s Date and Time”
3: from DUAL;
Your output appears similar to

2: “Today’s Date and Time”
3: from DUAL
Assuming today’s date is 06/01/99, you get the following output:
Today’s Date and Time
-----------------------
04/01/1999 12:00:00 AM
This result makes sense because June is in the second quarter, and the quarter
ranges from 04/01/99 to 06/30/99. Truncating to the quarter gives the beginning
date for the applicable quarter. You’ll get the opportunity to test this function in the
exercises at the end of the lesson.
The
ADD_MONTHS
Function
The
ADD_MONTHS
function adds or subtracts months from a date. Because this function is
overloaded, which means that you can pass different data types to the same function or
change the order of the parameters, you can specify the parameters in any order.
ADD_MONTHS(date_passed, months_to_add)
If
months_to_add
is positive, it adds months into the future. If the
months_to_add
number is negative, it subtracts months from
date_passed
. You can specify
months_to_add
as a fraction, but Oracle completely ignores the fraction. You can indi-
cate the day level by using other Oracle functions. Another caution is that Oracle returns
the same day in the resulting calculation except when the last day in one month (for

31st, as shown in Listing 6.22.
L
ISTING
6.22
Adding One Month
SELECT ADD_MONTHS(TO_DATE(‘31-MAR-99’),1) from DUAL;
This example has the output
ADD_MONTH
---------
30-APR-99
Oracle could not output April 31st because no such date exists.
The
NEXT_DAY
Function
The
NEXT_DAY
function returns the next date in the week for the day of the week speci-
fied after the input date. The time returned is the time specified by the input date when
called.
NEXT_DAY(input_date_passed, day_name)
The
NEXT_DAY
function offers a lot of possibilities. You can calculate anything
from the first Monday of every month to each payday in a calendar year. You’ll
start by testing the
NEXT_DAY
function on the
SYSDATE
function to find the next Monday.
Assume the

The first Monday after the date is June 7, 1999. Because you are using the
SYSDATE
,the corresponding time value is returned when the function is called.
You can find the first Monday for August 1999 by executing the code in Listing 6.24.
L
ISTING
6.24
Finding the First Monday in the Month of August
1: SELECT TO_CHAR(NEXT_DAY(‘01-AUG-99’,’Monday’),’MM/DD/YYYY HH:MM:SS AM’)
2: “Next_Day”
3: from DUAL;
Your output is
Next_Day
-----------------------
08/02/1999 12:00:00 AM
Although the first Monday in August is 08/02/99, is there a logic problem here?
If you repeat the example but use a month in which Monday is the first day of
the month, what happens? Execute the code in Listing 6.25.
L
ISTING
6.25
Finding the First Monday in the Month of September
1: SELECT TO_CHAR(NEXT_DAY(‘01-MAY-00’,’Monday’),’MM/DD/YYYY HH:MM:SS AM’)
2: “Next_Day”
3: from DUAL;
Your output is
Next_Day
-----------------------
05/08/2000 12:00:00 AM
The result is definitely not what you had in mind! The

The Proper Method to Find the First Monday in a Given
Month
1: SELECT TO_CHAR(NEXT_DAY(‘30-APR-00’,’Monday’),’MM/DD/YYYY HH:MM:SS AM’)
2: “Next_Day”
3: from DUAL;
Your output is
Next_Day
-----------------------
05/01/2000 12:00:00 AM
You finally have the proper logic for what you intended to find.
The
LAST_DAY
Function
The
LAST_DAY
function provides the last day of the given month. A useful purpose is to
determine how many days are left in the given month.
LAST_DAY(input_date_passed)
You will compute the last days in the month when summer officially starts from 1999.
Execute the code in Listing 6.27.
L
ISTING
6.27
Finding the Last Day of the Month Starting Summer
1: SELECT TO_CHAR(LAST_DAY(‘30-JUN-99’),’MM/DD/YYYY HH:MM:SS AM’) “Last_Day”
2: from DUAL;
Your output is
Last_Day
-----------------------
06/30/1999 12:06:00 AM

2: LAST_DAY(‘20-JUN-99’) - TO_DATE(‘20-JUN-99’) “Days_Summer”
3: from DUAL;
Your output is
Last_Day Days_Summer
---------------------
30-JUN-99 10
The
MONTHS_BETWEEN
Function
The
MONTHS_BETWEEN
function returns the number of months between two given dates. If
the day is the same in both months, you get an integer value returned. If the day is dif-
ferent, you get a fractional result based upon a 31-day month. If the second date is prior
to the first date, the result is negative.
MONTHS_BETWEEN(input_date1,input_date2)
You can see all the possible returned values by executing the code in Listing 6.29.
L
ISTING
6.29
Experimenting with
MONTHS_BETWEEN
1: SELECT MONTHS_BETWEEN(‘25-DEC-99’,’02-JUN-99’) “Fractional”,
2: MONTHS_BETWEEN(‘02-FEB-99’,’02-JUN-99’) “Integer”
3: from DUAL;
Your output is
Fractional Integer
---------- ---------
6.7419355 -4
182 Day 6

first zone and specifying the second zone.
NEW_TIME(input_date_and_time, time_zone1, time_zone2)
See Table 6.10 for the valid time zones.
T
ABLE
6.10
Time Zones
Time Zone
Abbreviation Time Zone Description
AST
Atlantic Standard Time
ADT
Atlantic Daylight Savings Time
BST
Bering Standard Time
BDT
Bering Daylight Savings Time
CST
Central Standard Time
CDT
Central Daylight Savings Time
EST
Eastern Standard Time
EDT
Eastern Daylight Savings Time
GMT
Greenwich Mean Time (the date line!)
HST
Alaska-Hawaii Standard Time
HDT

184 Day 6
I
NPUT
Remember, minutes are expressed as
MI
, not
MM
. This is a common mistake!
Tip
Your output is
Central to Pacific
-----------------------
01-JUN-99 11:00:00 PM
Because there is a two-hour time difference, you not only see the revised time,
but also the revised date. I guess you truly can go back in time!
O
UTPUT
In a database that traverses time zones, you might want to store the time
and date for all entries in one standardized time zone, along with the time
zone abbreviation from the original time zone. This arrangement saves you
a lot of time and coding when designing the database.
Tip
A
NALYSIS
The
ROUND
Function
ROUND
is similar to the
TRUNC

---------------------------
02-JUN-99 01:01:00 AM 10
Because the seconds were 30 or greater, this example rounded to the next minute
at 1:01 from 1:00. Had the number of seconds been 22, the return value would be
1:00. You should test this code on your own.
The
TRIM
Function
The
TRIM
function truncates leading and trailing characters from a specified string. This
is equivalent to using the
LTRIM
and
RTRIM
functions simultaneously.
TRIM ([LEADING/TRAILING/BOTH], trim_character FROM trim source)
You can practice the
TRIM
function to remove leading and trailing zeroes from a specified
number by entering the code in Listing 6.32.
L
ISTING
6.32
TRIM
Leading and Trailing Zeroes
SELECT TRIM (0 FROM 067270676800) “TRIM Example”
FROM DUAL;
Your output is
TRIM Example

Q Must I use Oracle’s built-in functions?
A No. You can always create your own similar functions, but when speed is of the
essence, why reinvent the wheel? Use the built-in functions whenever possible.
Q What date does the Julian system start counting from?
A January 1, 4712 BC.
Q When using
TO_DATE
, is the format mask important?
A Not just a little bit important, but very important and required! Without the proper
format mask, you will most certainly get an Oracle error message.
QHow long should the number format mask be?
A At least equal to or greater than the length of the largest value.
Q What function allows you to perform mathematical computations on char-
acter strings?
A
TO_NUMBER
converts character strings to numbers so that you can perform any
mathematical calculations you want.
Q Where does the
SYSDATE
date and time originate?
A If you are using Personal Oracle, the system date and time come from the PC’s
internal clock. If you are in a client/server environment, the system date and time
are pulled from the server.
Workshop
Use the following workshop to test your ability to understand and use several of Oracle’s
built-in functions. The answers to the quiz and exercises appear in Appendix A,
“Answers.”
Quiz
1. True or False: All functions are accessible within PL/SQL.

to
remove any spaces padded to the left of the Roman numeral. If you are really
ambitious, on your own you can create the same RM-type function by using
IF...THEN...ELSE
statements for practice from Day 4. Remember, practice helps
to solidify your knowledge through repetition and understanding.
2. Use the
TRUNC
function on the
SYSDATE
to round to the nearest century.
3. Use
CONCAT
to link two strings together. Repeat the same line by using
||
instead
of
CONCAT
.
4. Calculate the number of days between 01/01/97 to 03/31/97. Remember to use the
TRUNC
function to eliminate the
TIME
dependency.
5. Convert the
CHARACTER
string
‘06/11/67’
to a date, and subtract from 06/11/97 to
see how old your author is (and holding).

•Creating packages
•Trapping errors and exceptions
09 7982 CH07 11.8.00 11:24 AM Page 189
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using Procedures
A procedure is a logically grouped set of SQL and PL/SQL statements that per-
form a specific task. It’s a miniature self-contained program. A stored procedure
is a procedure that has been compiled and stored inside the database. Once stored the
procedure is a schema object (that is, a specific database object).
Procedures have several parts. The declarative part contains declarations of types, cursors,
constants, variables, exceptions, and nested subprograms. Procedures can be declared in
PL/SQL blocks, packages, and other procedures. The executable part contains statements
that control execution and manipulate data. Occasionally, the procedure might contain an
exception-handling part to deal with exceptions raised during execution. Procedures can be
defined and executed by using any Oracle tool that supports PL/SQL, such as SQL*Plus.
Why Use Procedures?
Procedures are created to solve a specific problem or task. PL/SQL procedures offer the
following advantages:
•In PL/SQL, you can tailor a procedure to suit your specific requirements.
•Procedures are modular, which means they let you break a program down into
manageable, well-defined units.
• Because procedures are stored in a database, they are reusable. After a procedure
has been validated, it can be used over and over, without being recompiled or dis-
tributed over the network.
•Procedures improve database security. You can restrict database access by allowing
users to access data only through stored procedures.
•Procedures take advantage of shared memory resources.
Procedures Versus Functions
Procedures and functions are PL/SQL subprograms that are stored in the database. The
significant difference between the two is simply the types of output the two objects gen-

change an existing procedure without having to drop and re-create the procedure.
•
procedure_name
—The name you assign to the procedure being created.
•
arguments
—The arguments in the procedure, which can be the following:
•
in
—Specifies that you must pass a value to the subprogram being called.
The
in
parameter might not be assigned a value because it acts like a con-
stant. The actual value that corresponds to the parameter can be a constant, a
literal, an initialized variable, or an expression.
•
out
—Specifies that the procedure returns a value to the calling program.
This parameter acts like an uninitialized parameter; therefore, its value
cannot be assigned to another variable. The actual value that corresponds to
the parameter must be a variable. It cannot be a literal, a constant, or an
expression. Within your subprogram, the
out
parameter must be assigned a
value.
•
inout
—Specifies that you must pass a value to the procedure and that the
procedure returns a value to its calling environment after execution.
•

and can accept one parameter,
emp_id
.
When this procedure is stored in the database, you can invoke the program via any other
PL/SQL block. To see the effect of this procedure, first select the rows from the
employee table prior to running this procedure. Then run the procedure and re-select the
rows from the table. The records are now updated.
Normally, procedures are created as standalone schema objects. However, you can create
a procedure as part of a package; this topic is discussed later in this lesson, in the section
“Exploring Packages.”
The
RETURN
Statement
The
RETURN
statement causes a subprogram to immediately complete its execution and
return to the calling program. Execution in the calling program resumes with the state-
ment following the procedure call.
In procedures, the
RETURN
statement cannot contain an expression. Its sole purpose is to
return control to the calling program before the end of the procedure is reached.
Procedure Dependencies
One of the inherent features of Oracle is that it checks the database to make sure that the
operations of a procedure, function, or package are possible based on the objects the user
has access to. For example, if you have a procedure that requires access to several tables and
views, Oracle checks during compilation time to see if those tables and views are present
and available to the user. The procedure is said to be dependent on these tables and views.
192 Day 7
Oracle automatically recompiles all dependent objects when you explicitly

OR REPLACE
clause to do these things. If Oracle
successfully recompiles a procedure, then the procedure becomes a valid procedure that
can be executed without runtime compilation. If compilation fails, the procedure
becomes invalid and must be debugged.
You can use the
ALTER PROCEDURE
command to explicitly recompile a procedure that is
invalid. After a procedure is compiled, it does not need to be recompiled implicitly during
runtime processes. This leads to reduced overhead and elimination of runtime compilation
errors.
You can produce debugging information from within an application by issuing the
PUT
or
PUT_LINE
commands. These commands place the debugging information into a buffer
that was created by the
DBMS_OUTPUT
package. To display the contents of the buffer, type
the
SET SERVEROUTPUT ON
command at the SQL*Plus prompt.
The code in Listing 7.2 illustrates the
PUT_LINE
command line that you can include
inside a procedure.
L
ISTING
7.2
The

During its invocation, one parameter was passed. During its execution, this pro-
cedure simply created the new
Pay Type
value, and displayed it.
Re-creating and Modifying Procedures
A valid standalone procedure cannot be altered; it must be either replaced with a new
definition or dropped and re-created. For example, you cannot just slightly alter one of
the PL/SQL statements in the procedure. Instead, you must re-create the procedure with
the modification.
When replacing a procedure, you must include the
OR REPLACE
clause in the
CREATE
PROCEDURE
statement. The
OR REPLACE
clause is used to replace an older version of a
procedure with a newer version of the procedure. This replacement keeps all grants in
place; therefore, you do not have to re-create the grants. Grants are statements which
when executed allow certain privileges to be given to the object of the grant. However, if
you drop the procedure and re-create it, the grants are dropped and consequently have to
be rebuilt. If you attempt a
CREATE PROCEDURE
command for a procedure that already
exists, Oracle generates an error message.
Listing 7.3 re-creates the procedure named
emp_change_s
.
L
ISTING

emp_change_s(2); --calls the emp_change_s procedure
... -- remainder of PL/SQL block
This example is an illustration of how you can call a stored procedure and pass
parameters. In this case, the stored procedure
emp_change_s
is called with para-
meter
i_emp_id
. You can see this in the line of code immediately after the
BEGIN
state-
ment. When the stored procedure is invoked and successfully runs, control of the program
is returned to the next line of code immediately following the procedure invocation line.
Another example of the same procedure being executed from within SQL*Plus is the
following:
SQL> execute emp_change_s (2);
The following example shows a procedure being called from within a precompiler program:
exec sql execute
BEGIN
emp_change_s (2)
END
END-exec
This is a fairly common and simple method for executing a stored procedure. You
will probably use this approach frequently during your developments efforts.
Using Parameters
Procedures use parameters (that is, variables or expressions) to pass information. When
a parameter is being passed to a procedure, it is known as an actual parameter.
Parameters declared internal to a procedure are known as internal, or formal, parameters.
The actual parameter and its corresponding formal parameter must belong to compatible
datatypes. For example, PL/SQL cannot convert an actual parameter with the datatype

—A list of current errors on all objects accessible to the user
•
all_source
—The text source of all stored objects accessible to the user
•
user_objects
—A list of all the objects the current user has access to
•
dba_errors
—Current errors on all stored objects in the database
•
dba_object_size
—All PL/SQL objects in the database
•
dba_source
—The text source of all stored objects in the database
•
user_errors
—The current errors on all a user’s stored objects
•
user_source
—The text source of all stored objects belonging to the user
•
user_object_size
—The user’s PL/SQL objects
The code in Listing 7.4 queries the
user_errors
view to obtain information about the
current errors on a procedure owned by
user_01

Emp_change_h Procedure
In this example, the procedure named
emp_change_h
is listed in the
user_objects
table as invalid.
Emp_change_h
is a procedure that I have made up to illustrate this
table. It will not show up in your exercise. Now the developer knows which object is
invalid and can correct it. For other databases, you may see other objects listed as well.
196 Day 7
I
NPUT
/
O
UTPUT
A
NALYSIS
I
NPUT
O
UTPUT
A
NALYSIS
09 7982 CH07 11.8.00 11:24 AM Page 196
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.