Q Must I use Oracle's built-in functions?

A No. You could 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, very important and required! Without the proper format mask, you will most certainly get an Oracle error message.

Q How 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 character strings?

A TO_NUMBER converts the character strings to numbers so that you can perform any mathematical calculations you want.

Q From what machine 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 can be found in Appendix A, "Answers."

Quiz

1. True or False: All functions are accessible from within PL/SQL.
2. What function would I use to combine two strings together?
3. What function would convert '11/28/97' to an Oracle DATE?
4. In a VARCHAR2 string, each string can be a variable length. What function would you use to determine the length so that you can search through the entire string?
5. How do you get rid of padded spaces to the right of a string in Oracle?
6. To determine the remainder, you would use the _____________ function.
7. To determine how many months a customer is delinquent, you can use the _________ function.
8. The TRUNC and ROUND functions can be used with what datatypes?

Exercises

1. Create a PL/SQL block that reads in the month of a date and displays the month in a Roman numeral format. Use a date of 06/11/67. This will allow you to practice the TO_CHAR function. When printing the Roman numeral equivalent, use LTRIM to remove spaces padded to the left of the Roman numeral. If you are really ambitious, on your own you could create the same RM type function by using IF...THEN...ELSE statements for practice from Day 5. 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).
6. Calculate how many months are between 05/15/97 to 08/22/97.
7. Round the SYSDATE to the nearest century.
8. Calculate the time in Newfoundland from Central Standard Time from 02/22/97, 05:00 a.m.
9. From Listing 7.22, subtract one month and explain the answer.
10. Calculate the number of days until Christmas from the last day of the month of today's date! (We don't get paid until the end of the month!)