Page 511
Page 512
This appendix provides the answers to the quiz and exercise sections at the end of each chapter.
A Typing the slash character (/) on a line by itself immediately following the end of the PL/SQL block.
A The PL/SQL block.
A There are at least two advantages:
A There are several that execute PL/SQL:
And several that recognize it:
A SET SERVEROUTPUT ON
A The three options described in the chapter are as follows:
Page 513
A I'll leave the task of generating errors up to you.
A You should be able to type any PL/SQL block into SQL*Plus, execute it, and then use the EDIT command to bring up Notepad so that you can change it.
As for executing a PL/SQL block from a file, one possible solution is to create a file with these lines and call it test.sql:
SET SERVEROUTPUT ON BEGIN dbms_output.put_line(`Hello there!'); END; /
You can then execute the file from SQL*Plus using the command
@test
A SQL*Plus, Developer/2000 Forms, Developer/2000 Reports, Developer/2000 Graphs, and Oracle Call Interfaces.
A A bind variable is a variable that is created outside PL/SQL but referenced within a PL/SQL block.
A A substitution variable is a user-defined variable. This variable is preceded by an ampersand (&) when it is defined.
Write a simple piece of code outline that uses substitution variables and bind variables.
A The following is a simple illustration (code outline) of using bind variables and substitution variables in the same PL/SQL block:
DECLARE v_Emp_name varchar2(35); v_emp_no number(3);
Page 514
... BEGIN ... v_emp_name := &emp_name; -- substitution variable SELECT emp_no into v_emp_no from emp where emp_name = :v_emp_name; -- bind variable ... end;
A Procedures and functions hide complexity, promote modularity, and allow for reuse of code.
A A declaration of NUMBER(6,2) allows you to store values such as 1234.56, _2333.99, and so on. The maximum value you can store is 9999.99.
A A declaration of NUMBER(2,2) allows you to store values such as 0.01, 0.02, up through 0.99. All values will be rounded to the nearest hundredth.
A The maximum length of a VARCHAR2 in PL/SQL is 32767 bytes. In an Oracle database, the maximum length is 2000 bytes.
A The TRUNC() function can be used to truncate a date so that it no longer contains a time element.
A The RTRIM() function can be used to eliminate trailing spaces from both CHAR and VARCHAR2 strings.
Page 515
A Here is one solution:
--Remember to execute:SET SERVEROUTPUT ON DECLARE state_name VARCHAR2(30); BEGIN state_name := `Michigan'; --Now code a nested block which declares and prints --a variable of the same name. DECLARE state_name VARCHAR2(30); begin state_name := `Tennessee'; DBMS_OUTPUT.PUT_LINE(state_name); END; --Now print the state_name variable's value in the outer block. DBMS_OUTPUT.PUT_LINE (state_name); END; / Tennessee Michigan PL/SQL procedure successfully completed.The inner block will print the value assigned to state_name within that block. However, the value of state_name in the outer block is undisturbed because the scope of the inner block's state_name declaration is limited to that block.
A Here is a PL/SQL block containing a function named age_as_of, which is one possible solution to the exercise of coding an age calculation function:
--Remember to execute: SET SERVEROUTPUT ON
DECLARE
age BINARY_INTEGER;
birth_date DATE := TO_DATE('11-15-1961','mm-dd-yyyy');
current_date DATE;
FUNCTION age_as_of (birth_date IN DATE, as_of_date IN DATE)
RETURN POSITIVE IS
as_of_year NATURAL; --a year of 00 is valid.
as_of_month POSITIVE;
as_of_day POSITIVE;
birth_year NATURAL; --a year of 00 is valid.
birth_month POSITIVE;
birth_day POSITIVE;
age POSITIVE;
Page 516
BEGIN
--Get the various parts of the dates needed to determine age.
as_of_year := TO_NUMBER(TO_CHAR(as_of_date,'yyyy'));
as_of_month := TO_NUMBER(TO_CHAR(as_of_date,'mm'));
as_of_day := TO_NUMBER(TO_CHAR(as_of_date,'dd'));
birth_year := TO_NUMBER(TO_CHAR(birth_date,'yyyy'));
birth_month := TO_NUMBER(TO_CHAR(birth_date,'mm'));
birth_day := TO_NUMBER(TO_CHAR(birth_date,'dd'));
--Now make the actual computation.
IF as_of_month > birth_month THEN
age := as_of_year - birth_year;
ELSIF (as_of_month = birth_month) and (as_of_day >= birth_day) THEN
age := as_of_year - birth_year;
ELSE
age := as_of_year - birth_year - 1;
END IF;
RETURN age;
END;
BEGIN
--Let's test each of the cases that the age
--function needs to consider.
DBMS_OUTPUT.PUT_LINE(`Age as of 11-13-1997');
current_date := TO_DATE('11-13-1997','mm-dd-yyyy');
age := age_as_of (birth_date, current_date);
DBMS_OUTPUT.PUT_LINE(age);
DBMS_OUTPUT.PUT_LINE(`Age as of 11-15-1997');
current_date := to_date('11-15-1997','mm-dd-yyyy');
age := age_as_of (birth_date, current_date);
DBMS_OUTPUT.PUT_LINE(age);
DBMS_OUTPUT.PUT_LINE(`Age as of 12-13-1997');
current_date := to_date('12-13-1997','mm-dd-yyyy');
age := age_as_of (birth_date, current_date);
DBMS_OUTPUT.PUT_LINE(age);
DBMS_OUTPUT.PUT_LINE(`Age as of 5-13-1997');
current_date := TO_DATE(`5-13-1997','mm-dd-yyyy');
age := age_as_of (birth_date, current_date);
DBMS_OUTPUT.PUT_LINE(age);
END;
/
Age as of 11-13-1997
35
Age as of 11-15-1997
36
Age as of 12-13-1997
36
Age as of 5-13-1997
35
PL/SQL procedure successfully completed.
Page 517
A A unary operator works on only one value. An example is the negation operator, which is often used to write negative numbers. Binary operators work on two values. Examples are the addition and multiplication operators.
a. (5-4)-(3-1)
b. 4*2**3-2
c. 4*2**(3-2)
d. 4=4 AND 5=6 OR 3=3
A The expressions evaluate as follows:
a. (5-4)-(3-1) evaluates to -1.
b. 4*2**3-2 evaluates to 30.
c. 4*2**(3-2) evaluates to 8.
d. 4=4 AND 5=6 OR 3=3 evaluates to true.
a. A <> B
b. A < B
c. (A <= B) AND (B <= C)
A a. NOT (A = B)
b. NOT (A >= B)
c. NOT ((B < A) OR (B > C))
`123-45-6789''___-__-____'
`Boom''John%'
`Johnson''_oo_'
`517-555-1212'
`Broom'
`Jonson'
`Johnston'
A `John%' matches `Johnson' and `Johnston'. `_oo_' matches `Boom'. `___-__-____' matches `123-45-6789'. The remaining strings and patterns don't match at all.
Page 518
A Any time a VARCHAR2 string is involved.
Write a function to compute wages based on an hourly rate and the number of hours worked. Have it use a minimum wage of $5 per hour if the rate is unknown. Have it also use the minimum wage if the rate is too low. A Here is one solution:
--Remember to execute: SET SERVEROUTPUT ON
DECLARE
FUNCTION wage_calculate (
hours_worked IN NUMBER,
hourly_rate IN NUMBER) RETURN NUMBER IS
hourly_rate_to_use NUMBER;
minimum_wage NUMBER := 5;
BEGIN
IF (hourly_rate IS NULL) OR (hourly_rate < minimum_wage) THEN
hourly_rate_to_use := minimum_wage;
ELSE
hourly_rate_to_use := hourly_rate;
END IF;
RETURN hours_worked * hourly_rate_to_use;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(wage_calculate(40,10));
DBMS_OUTPUT.PUT_LINE(wage_calculate(40,2));
DBMS_OUTPUT.PUT_LINE(wage_calculate(40,NULL));
END;
/
400
200
200
PL/SQL procedure successfully completed.
A The required parts of the function are the function keyword and name, the RETURN statement and type, and a BEGIN and an END statement with the function name occurring at the end of the END statement.
Page 519
A It is not always necessary to pass values, even if the function allows for this. To compensate, make sure that you have a DEFAULT for the parameter if nothing is passed.
A The function will error out with no value returned.
A You can return more than one parameter by using the optional MODE of OUT or IN OUT. However, this is not a recommended programming practice.
A After the ELSE statement, code a NULL;.
A Some common errors are forgetting to put a space between END and IF; forgetting the semicolon after the END IF; missing an END IF statement, especially if nested IFs are used; and finally, misspelling ELSIF as ELSEIF.
A From your command prompt, type the words SHOW ERRORS and then troubleshoot. Remember, a program can compile and still be incorrect due to logic errors.
A Even though you are adding the REVERSE keyword, the starting and ending values must still be coded from lowest value to highest value.
A Here's one solution:
CREATE OR REPLACE FUNCTION mygrade(p_score NUMBER)
RETURN CHAR IS
BEGIN
IF p_Score >= 90 THEN
RETURN `A';
ELSIF p_Score >= 80 THEN
RETURN `B';
ELSIF p_Score >= 70 THEN
RETURN `C';
ELSIF p_Score >= 60 THEN
RETURN `D';
ELSE
RETURN `E';
Page 520
END IF; END;
A Here's one solution:
DECLARE
v_Score Number := 85; --Percentage
v_LetterGrade Char(1);
BEGIN
IF v_Score between 90 and 100 THEN
v_LetterGrade := `A';
ELSIF v_Score between 80 and 89 THEN
v_LetterGrade := `B';
ELSIF v_Score between 70 and 79 THEN
v_LetterGrade := `C';
ELSIF v_Score between 60 and 69 THEN
v_LetterGrade := `D';
ELSE
v_LetterGrade := `E';
END IF;
DBMS_OUTPUT.PUT_LINE(`Your Letter Grade is: ` || v_LetterGrade);
END;
/
A Here's one solution:
BEGIN
FOR v_loopcounter IN 1..9 LOOP
IF MOD(v_loopcounter,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(`The counter * pi is ` ||
v_loopcounter * mypi );
END IF; -- End execution of statements for even counter
END LOOP;
END;
Allow the high boundary to be a variable that can change. Use an initial value of
3 for testing.
A Here's one solution:
DECLARE
v_factorial NUMBER := 1;
BEGIN
FOR v_loopcounter IN REVERSE 1..4 LOOP
v_factorial := v_factorial * v_loopcounter;
DBMS_OUTPUT.PUT_LINE(`Your factorial value is now ` ||
v_factorial);
END LOOP;
END;
Page 521
A True. This is one of many reasons why you probably will never need to use the GOTO statement.
A The GOTO statement is typically used in emergency situations that require you
to respond immediately. It could be something as simple as a server going down, or
as complex as an incoming nuclear warhead. Other than that, it is not good
coding practice to use the GOTO statement.
A Yes. As long as the condition never evaluates to true, the same code will repeat over and over again.
A The EXIT and EXIT WHEN statements allow you to break out of the execution of a loop.
A Label names for loops
A This is not required as part of the syntax. However, you should make it mandatory as part of good programming practice instead of having infinite loops!
A No. You can simulate this by using a simple LOOP with the EXIT or EXIT WHEN statement.
A These statements should appear at the beginning or at the end of the LOOP body to avoid potential logic errors.
Page 522
A Here is one solution:
DECLARE
v_GOTOVARIABLE NUMBER := 0;
BEGIN
v_GOTOVARIABLE := 10;
IF v_GOTOVARIABLE = 10 THEN
GOTO nullstatement;
ELSE
NULL;
END IF;
<<nullstatement>>
NULL;
END;
/
A Here is one solution:
DECLARE
v_factorial NUMBER := 1;
v_factorial_counter NUMBER := 4;
BEGIN
WHILE v_factorial_counter != 1 LOOP
v_factorial := v_factorial * v_factorial_counter;
DBMS_OUTPUT.PUT_LINE(`Your factorial value is now `
|| v_factorial);
v_factorial_counter := v_factorial_counter - 1;
END LOOP;
END;
/
A Here is one solution:
DECLARE
v_factorial NUMBER := 1;
v_factorial_counter NUMBER := 4;
BEGIN
LOOP
v_factorial := v_factorial * v_factorial_counter;
DBMS_OUTPUT.PUT_LINE(`Your factorial value is now `
|| v_factorial);
v_factorial_counter := v_factorial_counter - 1;
EXIT WHEN v_factorial_counter = 1;
END LOOP;
END;
/
Page 523
A False. There are many SQL-only commands, which perform calculations on rows, such as AVG, MIN, MAX, and so on.
A You would use the CONCAT function; however, you can still fall back on || to concatenate strings.
A The TO_DATE function gives you this flexibility.
A By using the LENGTH function, you can determine the actual length of VARCHAR2. If the value is NULL, NULL is returned. If you are using type CHAR, it will include the padded spaces in the count.
A By using RTRIM and specifying the space as a character, you can trim any padded spaces in a string.
A MOD
A MONTHS_BETWEEN
A Both NUMBER and DATE include the ROUND and TRUNC functions.
Page 524
A Here is one solution:
DECLARE
v_Hold_Month Number;
BEGIN
v_Hold_Month := TO_NUMBER(TO_CHAR(TO_DATE('11-JUN-67'),'MM'));
DBMS_OUTPUT.PUT_LINE(v_Hold_Month);
DBMS_OUTPUT.PUT_LINE(`Converted to Roman Numeral ` ||
LTRIM(TO_CHAR(v_Hold_Month,'RM'),' `));
END;
/
6 Converted to Roman Numeral VI
A The answer is
SELECT TO_CHAR(TRUNC(SYSDATE,'CC'),'MM/DD/YYYY HH:MI:SS AM')
"Today's Date and Time"
from DUAL
Today's Date and Time ------------------------- 01/01/1900 12:00:00 AM
A Here is one solution:
DECLARE
v_String1 VARCHAR2(60) := CONCAT(`Connect String1 to',
` String2');
v_String2 VARCHAR2(60) := `Connect String1 to' || ` String2';
BEGIN
DBMS_OUTPUT.PUT_LINE(v_String1);
DBMS_OUTPUT.PUT_LINE(v_String2);
END;
/
Connect String1 to String2 Connect String1 to String2
A The answer is
SELECT TRUNC(TO_DATE('03/31/97','MM/DD/YY')) -
TRUNC(TO_DATE('01/01/97','MM/DD/YY')) "Days_Subtracted"
from DUAL;
Page 525
Days_Subtracted
---------------
89
A The answer is
SELECT (TO_DATE('06/11/97','MM/DD/YY') -
TO_DATE('06/11/67','MM/DD/YY'))/365 "Years Old"
from DUAL;
Years Old --------- 30.021918
A The answer is
SELECT MONTHS_BETWEEN('22-AUG-97','15-MAY-97') "Fractional"
from DUAL;
Fractional ---------- 3.2258065
A The answer is
SELECT TO_CHAR(ROUND(SYSDATE,'CC'),'MM/DD/YYYY HH:MI:SS AM')
"Today's Date and Time"
from DUAL;
Today's Date and Time ------------------------- 01/01/2000 12:00:00 AM
A Here is one solution:
SELECT TO_CHAR(NEW_TIME(TO_DATE('02-22-97 05:00:00 AM',
`MM-DD-YY HH:MI:SS AM'),
`CST','NST'), `DD-MON-YY HH:MI:SS AM')
"Central to Newfoundland"
from DUAL;
Page 526
Central to Newfoundland ------------------------ 22-FEB-97 07:30:00 AM
A Several possible answers are
SELECT ADD_MONTHS(TO_DATE('31-MAR-97'),-1) from DUAL;
SELECT ADD_MONTHS(TO_DATE('31-MAR-97'),-1.5) from DUAL;
SELECT ADD_MONTHS(-1,TO_DATE('31-MAR-97')) from DUAL;
The output, of course, is the end of February because February has fewer than
30 days:
ADD_MONTH --------- 28-FEB-97
A Here is one solution:
SELECT LAST_DAY(SYSDATE) "Last_Day",
TO_DATE('25-DEC-97') - LAST_DAY(SYSDATE) "Shopping Days"
from DUAL;
Last_Day Shopping Days --------- ------------- 30-JUN-97 177.67266
A The CREATE OR REPLACE PROCEDURE command is used to recompile a procedure.
A There are many Oracle predefined exceptions including: no_data_found, too_many_rows, invalid_cursor, value_error, invalid_number, zero_divide, cursor_already_open, login_denied, and others.
Page 527
A To call a specific procedure within a package, you use dot notation as shown in the following example:
package_name.procedure_name
A Package specifications contain public declarations of the name of the package and its functions and procedures. The following is an example and might differ slightly from your answer:
CREATE PACKAGE day_8_package_spec as -- package name declaration FUNCTION inv_count (qty number, part_nbr varchar2(25)) -- function declaration return number; PROCEDURE pay_salary (emp_id number); -- procedure declaration PROCEDURE hire_employee (emp_name, pay_date number, pay_type char)); --procedure declaration END day_8_package_spec;
A One possible way to write this exception handler is
exception
WHEN too_many_rows THEN
... -- code to be executed when a SELECT returns
-- too many rows
END;
WHEN others THEN
... -- code to be executed when an exception is
-- encountered which is not the too_many_rows
END;
Page 528
A The following built-ins can be used with the PL/SQL table: DELETE, first, last, next, prior, and count.
A The four DML statements that are supported within a PL/SQL block are INSERT, DELETE, UPDATE, and SELECT.
Evaluate each of the following three declarations and determine which ones are legal and which ones are not legal. Explain your answer for those that are not legal.
DECLARE emp_rec emp_rec_type;
A This is not a legal declaration because emp_rec_type must be declared prior to this declaration. A proper declaration would be
DECLARE
TYPE emp_rec_type IS record
(id INTEGER,
name VARCHAR2(35));
emp_rec emp_rec_type;
DECLARE emp_last_name %type;
A This is not a legal declaration. The proper declaration would have to include a table and column reference such as
emp_last_name emp.l_name%type;
DECLARE TYPE emp_table_type is table of VARCHAR2(55); emp_dept_table emp_table_type;
A This declaration is not legal because the INDEX BY clause is missing. This declaration should look like
DECLARE TYPE emp_table_type is table of VARCHAR2(55) INDEX BY BINARY_INTEGER; emp_dept_table emp_table_type;
Page 529
A The implicit and explicit cursors each have four attributes, which provide useful information about the cursor. The attributes are %isopen, %found, %notfound, and %rowcount.
A There is no predefined limit to the number of cursors a session can have. The only constraint that can limit the number of cursors is the availability of memory to manage the cursors. Also, there is a systemwide limit of cursors, which is defined by the OPEN_CURSOR parameter.
A When the cursor is opened, the cursor pointer is pointing to immediately prior to the first row.
Create a PL/SQL block that determines the top five highest-paid employees from your employee table. Be sure to incorporate the usage of the appropriate cursor attributes. Print these five employees to the screen.
A This exercise can be solved in several different ways. Your solution can include exception handling as well as other methods of processing the data. I have chosen the following method as my solution:
DECLARE c_emp_name VARCHAR2(32); c_sal NUMBER(9,2); CURSOR emp_cursor is -- cursor declaration SELECT emp_name, pay_type from employee ORDER BY pay_rate desc; -- key to getting top 5 highest-paid employees BEGIN OPEN emp_cursor; FETCH emp_cursor INTO c_emp_name, c_sal; --fetch into variables for later use WHILE emp_cursor%rowcount<=5 and -- only fetch top 5 employees emp_cursor%found -- be sure there is data LOOP DBMS_OUTPUT (c_emp_name || ` is paid ` || c_sal ); -- prints results to screen FETCH emp_cursor INTO c_emp_name, c_sal; END LOOP; CLOSE emp_cursor; -- closes the cursor END;
Page 530
A INSERT, UPDATE, and DELETE.
A The event that fires the trigger, the database table on which the trigger is defined, the optional WHEN clause, and the PL/SQL block containing the code to be executed.
A :OLD is used to refer to the values in a row before it is changed. :NEW is used to refer to the values after the row is changed.
A The USER_TRIGGERS view shows all triggers you own. In addition, you might want to look at the ALL_TRIGGERS view and the DBA_TRIGGERS view. The ALL_TRIGGERS view adds triggers that others own but which are defined on your tables. If you have database administrator privileges, the DBA_TRIGGERS view lists all triggers defined in the database.
A A mutating table is one that is in the process of being modified by the SQL statement which fired a trigger. Because the table is being changed it is not in a consistent state, and Oracle does not allow queries against it.
A Some possible uses for triggers are enforcing a business rule, enforcing security, logging changes, replication of data, and calculation of column values.
A Here is one solution:
INPUT/
OUTPUT
CREATE OR REPLACE TRIGGER emp_dept_names BEFORE INSERT OR UPDATE OF emp_id, dept_id ON emp_dept FOR EACH ROW DECLARE redundant_dept_name department.dept_name%TYPE; redundant_emp_name employee.emp_name%TYPE; BEGIN --Get the employee's name
Page 531
BEGIN
SELECT emp_name INTO redundant_emp_name
FROM employee
WHERE employee.emp_id = :NEW.emp_id;
EXCEPTION
--the employee record may not exist.
WHEN OTHERS THEN
redundant_emp_name := `';
END;
--Get the department name
BEGIN
SELECT dept_name INTO redundant_dept_name
FROM department
WHERE department.dept_id = :NEW.dept_id;
EXCEPTION
--the department record may not exist.
WHEN OTHERS THEN
redundant_dept_name := `';
END;
--Store the employee and department names in the emp_dept record.
:NEW.dept_name := redundant_dept_name;
:NEW.emp_name := redundant_emp_name;
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER department_emp_dept
AFTER UPDATE OF dept_name ON department
FOR EACH ROW
BEGIN
UPDATE emp_dept
SET emp_dept.dept_name = :NEW.dept_name
WHERE emp_dept.dept_id = :NEW.dept_id;
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER employee_emp_dept
AFTER UPDATE OF emp_name ON employee
FOR EACH ROW
BEGIN
UPDATE emp_dept
SET emp_dept.emp_name = :NEW.emp_name
WHERE emp_dept.emp_id = :NEW.emp_id;
END;
/
Trigger created.
ANALYSIS
The first trigger, emp_dept_name, handles inserts and updates on the
emp_dept table itself. Whenever a new record is inserted or an existing record updated,
the current employee and department names are retrieved from their respective
tables and stored with the emp_dept record. The second trigger,
department_emp_dept, ensures that any changes to a department's name are propagated to all the
related records in the emp_dept table. The third trigger does the same thing for changes
to employee names.
Page 532
Writing these triggers almost leads to a mutation problem. Recall the emp_dept_upd trigger shown in Listing 11.6. It is defined to fire only when the dept_id field is updated; in other words it is defined as AFTER UPDATE OF dept_id ON emp_dept. Removing the words OF dept_id would cause the trigger to fire whenever an emp_dept record was changed. In that case a change to a department name would fire department_emp_dept, which would issue an update against the emp_dept table. That would in turn fire the emp_dept_upd trigger, which would issue an update against the department table, which would be mutating because the SQL statement that started all this was an update against that table.
A This could be done as either one or two updates. Here is a solution done with one UPDATE statement:
UPDATE emp_dept ed
SET emp_name = (SELECT emp_name
FROM employee e
WHERE e.emp_id = ed.emp_id),
dept_name = (SELECT dept_name
FROM department d
WHERE d.dept_id = ed.dept_id);
A The cursor variable argument can have one of three different modes. These
modes are
A The cursor parameter is used to pass information into a cursor just as you would pass a parameter into a function or procedure. This can make your code more modular and maintainable. Likewise, you can establish initial or default values for these parameters to make your coding more simplistic.
Write a brief package declaration block and the beginning portion of the package body where you declare a cursor variable, and then use this variable in a procedure to open a cursor with a query.
Page 533
A Like many programming designs, your answer can vary slightly from the following. The idea is for you to get practice declaring cursor variables and parameters in the package specification and body./* Package Specification */ CREATE PACKAGE stock_quotes as TYPE stock_cur_type IS REF cursor; PROCEDURE get_quotes (stock_cv in out stock_cur_type); END stock_quotes; /* Package Body */ CREATE PACKAGE BODY stock_quotes as PROCEDURE get_quotes (stock_cv in out stock_cur_type) IS SELECT * from stocks; ... END get_quotes; END stock_quotes;
A A class, or object type as it is called by Oracle, serves as the blueprint for one or more objects. It is just a design, and you might compare it to a table definition. An object, on the other hand, represents an instance of a class. You can create many objects of a given type, just as you can create many records in a table.
A The allowed return values for an ORDER function are 0, _1, and 1. A 0 value means that the two objects being compared are equal. A value of _1 means that the object whose method was called is less than the other object. A value of 1 means that the object whose method was called is greater than the other object.
A The extra column in an object table is used to store the object identifier, which uniquely identifies that object in the database. It is an Oracle-generated value, and is automatically assigned to each object when it is first stored in the table.
A An object reference functions much like a pointer in a language such as C. It is used to store a reference from one object to another. It is only a pointer, and in order to access the referenced object, you must use that pointer in a query to retrieve the specified object.
Page 534
A Objects must have at least one attribute. They do not, however, have to have any methods at all.
A A MAP function can only return values of type NUMBER, VARCHAR2, or DATE.
A Here is one solution:
INPUT/
OUTPUT
1: CREATE OR REPLACE FUNCTION CreateBuilding ( 2: --This is an example of how you can work around the 3: --fact that you can't write your own "constructor" for 4: --the building object. This stored function serves 5: --as a psuedo-constructor. Note however, that Oracle can't 6: --force you to call this. 7: inBldgName VARCHAR2, 8: inBldgStreet VARCHAR2, 9: inBldgCity VARCHAR2, 10: inBldgStateAbbr VARCHAR2, 11: inBldgZip VARCHAR2, 12: inBldgMgr employee.emp_id%TYPE 13: ) RETURN building AS 14: TheNewBldg building; 15: NoFlag integer; 16: BEGIN 17: --Check to see if this building already exists in the database. 18: SELECT count(*) INTO NoFlag 19: FROM buildings 20: WHERE BldgName = inBldgName; 21: 22: IF NoFlag > 0 THEN 23: RETURN null; 24: END IF; 25: 26: --Check to see if the manager employee ID is valid. 27: SELECT count(*) INTO NoFlag 28: FROM employee 29: WHERE emp_id = inBldgMgr; 30: 31: IF NoFlag = 0 THEN 32: RETURN null; 33: END IF; 34: 35: --All validation checks have been passed, create the new 36: --building object. 37: TheNewBldg := building (inBldgName
Page 535
38: ,address (inBldgStreet 39: ,'' --no second addr line 40: ,inBldgCity 41: ,inBldgStateAbbr 42: ,inBldgZip 43: ,'') --no phone number 44: ,inBldgMgr); 45: 46: RETURN TheNewBldg; 47: END; 48: / Function created. 1: SET SERVEROUTPUT ON 2: DECLARE 3: a_building building; 4: BEGIN 5: --This will succeed 6: a_building := CreateBuilding(`The Red Barn', 7: `101 Pasture Lane', 8: `Mio','MI','48826',599); 9: dbms_output.put_line(`Created: ` || a_building.BldgName); 10: 11: --This will fail because the building exists. 12: a_building := CreateBuilding(`East Storage Shed', 13: `101 Pasture Lane', 14: `Mio','MI','48826',599); 15: dbms_output.put_line(`Created: ` || Ânvl(a_building.BldgName,'Nothing')); 16: 17: --This will fail because the manager does not exist. 18: a_building := CreateBuilding(`The Blue Barn', 19: `101 Pasture Lane', 20: `Mio','MI','48826',999); 21: dbms_output.put_line(`Created: ` || Ânvl(a_building.BldgName,'Nothing')); 22: 23: END; 24: / Created: The Red Barn Created: Nothing Created: Nothing PL/SQL procedure successfully completed.ANALYSIS
The CreateBuilding function takes five arguments: a building name, street address, city, state abbreviation, and manager ID. It returns an object of type building. The SELECT statement in lines 18_20 of the first segment first checks to see if a building with the same name already exists. Then the SELECT statement in lines 27_29 of the first segment checks to be sure that the manager ID is a valid employee ID. If everything checks out, the building constructor is called in lines 37_44 of the first segment to actually create the building object, which is then returned to the calling program (see line 46 of the first segment).
Page 536
The PL/SQL block at the end of the listing shows the results of three attempts to create building objects. The first succeeds. The second fails because a building with the same name already exists. The third also fails, but this time because the building manager ID does not represent a valid employee ID.
INPUT/
OUTPUT
A Here is one solution:
1: CREATE OR REPLACE TYPE building AS OBJECT ( 2: BldgName VARCHAR2(40), 3: BldgAddress address, 4: BldgMgr INTEGER, 5: MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER), 6: MAP MEMBER FUNCTION Compare 7: RETURN VARCHAR2 8: ); Type created. 1: CREATE OR REPLACE TYPE BODY building AS 2: MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS 3: BEGIN 4: BldgMgr := NewMgr; 5: END; 6: 7: MAP MEMBER FUNCTION Compare 8: RETURN VARCHAR2 IS 9: BEGIN 10: RETURN BldgName; 11: END; 12: END; 13: / Type body created.
ANALYSIS
This version of the building object is much the same as the one you first
created in Listing 13.7, except that it has a MAP function defined instead of an
ORDER function. This MAP function, declared in lines 6_7 of the first segment and
defined in lines 7_11 of the second segment, simply returns the building name.
When comparing objects of type building, Oracle will call this function and base
the comparison on the values returned.
A False. You might not even know logic errors exist until years later.
Page 537
A A logic error.
A 8. You perform the division calculation first and then add the result to 6.
A False. You can create one, purchase one, use pipes, or use the DBMS_OUT package.
A True. Proper planning helps you to improve the application and do it right the
first time.
A False. Comments improve readability of code and clarify the intent of the application programmer.
A False. Without proper formatting techniques, the code is hard to read and even harder to follow.
A One possible answer is
DECLARE
v_MyNumber NUMBER := 0;
BEGIN
DEBUG.ERASE;
LOOP
IF v_MyNumber = 7 THEN
EXIT;
END IF;
v_MyNumber := v_MyNumber + 2;
DEBUG.OUT(`v_MyNumber',v_MyNumber);
END LOOP;
END;
A One possible answer is
SET SERVEROUTPUT ON
DECLARE
v_MyNumber NUMBER := 0;
BEGIN
LOOP
IF v_MyNumber = 7 THEN
EXIT;
Page 538
END IF;
v_MyNumber := v_MyNumber + 2;
DBMS_OUTPUT.PUT_LINE(`The valye of v_MyNumber is: ` || v_MyNumber);
END LOOP;
END;
A Yes, but a large organizational chart will make for slower execution time than an alternative method.
A No. In fact, you will probably rarely use recursion.
A Four gigabytes.
A Currently, you can only read from external files.
A Not only is the newly created locator copied, but the entire LOB from the row is copied as well. If you have some 4-gigabyte objects, this table can eat up storage space fast!
Rewrite the code in Listing 15.1, but as a loop instead of a recursive function. Provide the result of 6 factorial (6!).
A One possible answer isDECLARE v_MyNum NUMBER := 6; v_Factorial NUMBER := 1; BEGIN LOOP IF v_MyNum = 0 THEN EXIT; ELSE v_Factorial := v_Factorial * v_MyNum; END IF; v_MyNum := v_MyNum - 1; END LOOP; DBMS_OUTPUT.PUT_LINE(`The factorial is: ` || v_Factorial); END;The factorial is: 720
Page 539
A A transaction is ended when it is committed or when it is rolled back.
A To summarize, row locks are enabled when a specific row is being modified by a DML statement. Likewise, a table lock is acquired either explicitly or implicitly when either a row or a table is being modified. Refer to the section in this chapter regarding locks, "Locking."
A Savepoints are like bookmarks within a transaction; they facilitate the rollback of a transaction to some intermediate point. This intermediate point is defined by the placement of the savepoint.
Write a PL/SQL block that establishes a savepoint, inserts a single record into the employee table, commits the data if the new record does not replicate an existing record, or rolls back the data if the new record insert fails.
A Here is one solution:
SAVEPOINT exercise; -- use this to roll back to
INSERT INTO employee VALUES (1, `Loraine Williams',2,4000.00,'S');
COMMIT; -- saves the data if insert was successful
-- this is not executed if there is an exception
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN -- exception handler
ROLLBACK; -- back out data from insert
A You must not end queries, DDL statements, or DML statements with a semicolon.
A Anonymous blocks require all punctuation.
A The largest value a PL/SQL variable can hold is 32KB.
Page 540
A Unlike fixed queries, these queries are created during runtime operations and executed in real time, thus making them dynamic.
A One possible answer is
DECLARE
/* The purpose of this PL/SQL block is to demonstrate
executing queries within PL/SQL through the use of the
DBMS_SQL package. We will simply display the output to
screen with the DBMS_OUTPUT package */
v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR
v_SelectRecords VARCHAR2(500); -- SQL stored as string to select
Ârecords
v_NUMRows INTEGER; -- Number of rows processed - of no use
v_MyNum INTEGER;
v_MyText VARCHAR2(50);
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
v_SelectRecords := `SELECT * from MyTable
WHERE MyRow >= 2'; -- SQL to view records
DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
/* Perform syntax error checking */
/* DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',1);
DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','One'); */
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
/* Execute the SQL code */
LOOP
IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
DBMS_OUTPUT.PUT_LINE(v_MyNum || ` ` || v_MyText);
END LOOP;
Page 541
EXCEPTION
WHEN OTHERS THEN
RAISE; -- raise if some other unknown error
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
END; -- End PL/SQL block
2 Two 3 Three 4 Four 4 Four
DECLARE
/* The purpose of this PL/SQL block is to demonstrate the use
of DML statements by deleting two records where MyRow
has a value of 4 */
v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR
v_DeleteRecords VARCHAR2(500); -- SQL stored as string to insert
Ârecords
v_NUMRows INTEGER; -- Number of rows processed - of no use
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
v_DeleteRecords := `DELETE FROM MyTable
where MyRow = :mynum';
/* Update the record */
DBMS_SQL.PARSE(v_CursorID,v_DeleteRecords,DBMS_SQL.V7);
/* Perform syntax error checking */
DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',4);
v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
/* Execute the SQL code */
DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
|| v_NUMRows);
EXCEPTION
WHEN OTHERS THEN
RAISE; -- raise if some other unknown error
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
COMMIT;
END; -- End PL/SQL block
Your data should look like:
MYROW MYDESC
----- --------------------------------------------------
1 One
2 Two
3 Three
Page 542
A One possible answer is
DECLARE
/* The purpose of this PL/SQL block is to demonstrate the use
of DML statements by updating one records where MyRow
has a value of 1 */
v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR
v_UpdateRecords VARCHAR2(500); -- SQL stored as string to insert
Ârecords
v_NUMRows INTEGER; -- Number of rows processed - of no use
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
v_UpdateRecords := `UPDATE MyTable
Set MyDesc = :mytext
where MyRow = :mynum';
/* Update the record */
DBMS_SQL.PARSE(v_CursorID,v_UpdateRecords,DBMS_SQL.V7);
/* Perform syntax error checking */
DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',1);
DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','It Worked');
v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
/* Execute the SQL code */
DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
|| v_NUMRows);
EXCEPTION
WHEN OTHERS THEN
RAISE; -- raise if some other unknown error
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
COMMIT;
END; -- End PL/SQL block
MYROW MYDESC
----- --------------------------------------------------
1 It Worked
2 Two
3 Three
A One possible answer is
DECLARE /* The purpose of this PL/SQL block is to delete a table called MyTable, which has two columns of type INTEGER and the second column of type VARCHAR2(50). This uses the DBMS_SQL package to execute DDL statements */
Page 543
v_CursorID NUMBER; -- Variable assigned to value from OPEN_CURSOR
v_CreateTableString VARCHAR2(500); -- SQL stored as string to drop
Âtable
v_NUMRows INTEGER; -- Number of rows processed - of no use
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
v_CreateTableString := `DROP TABLE MyTable'; -- Write SQL code to drop
Âtable
DBMS_SQL.PARSE(v_CursorID,v_CreateTableString,DBMS_SQL.V7);
/* Perform syntax error checking */
v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
/* Execute the SQL code */
EXCEPTION
WHEN OTHERS THEN
RAISE; -- raise if some other unknown error
DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
END; -- End PL/SQL block
A 1022 bytes.
A 1023 bytes.
A Even though the files are truly closed, the files are not marked as closed, so IS_OPEN will return a value of true.
A Either GET_LINE or GET_LINES will cause any form of PUT not to default to the screen.
Page 544
A 255 characters—any more will raise an error.
A 1,000,000 total characters. If the buffer limit is exceeded, an error is raised.
A One possible answer is
DECLARE
-- Variables to write out to text file called presexp.csv
v_SSNO pres.ssno%TYPE;
v_FirstName CHAR(20);
v_LastName CHAR(30);
--Declare filehandle
v_MYFileHandle UTL_FILE.FILE_TYPE;
-- Cursor declaration to fetch data from table, row by row
CURSOR c_PRES IS
SELECT * from PRES;
BEGIN
-- Open File for writing
v_MYFileHandle := UTL_FILE.FOPEN(`c:\','presfix.txt','w');
-- Open the cursor
OPEN c_PRES;
LOOP
-- Get each row, one at a time
FETCH c_PRES INTO v_SSNO,v_FirstName,v_LastName;
-- Output as FIXED BLOCK file
EXIT WHEN c_PRES%NOTFOUND;
UTL_FILE.PUT_LINE(v_MYFileHandle, SUBSTR(v_SSNO,1,3) || `-'
|| SUBSTR(v_SSNO,4,2) || `-' || SUBSTR(v_SSNO,6,4)
|| v_FirstName || v_LastName);
END LOOP;
-- Close Cursor
CLOSE c_PRES;
-- Close CSV file
UTL_FILE.FCLOSE(v_MYFileHandle);
END;
001-01-0111George Washington 005-12-4745Thomas Jefferson
PAGE 545
223-44-5726Richard Nixon 378-11-2475Jimmy Carter 541-12-9744Abraham Lincoln 587-14-5961John Kennedy 665-47-4112Theodore Roosevelt 725-13-9511George Bush 998-21-1247Ronald Reagan
A One possible answer is
DECLARE
-- Variables to write out to text file called presexp.csv
v_SSNO pres.ssno%TYPE;
v_FirstName CHAR(20);
v_LastName CHAR(30);
--Declare filehandle
v_MYFileHandle UTL_FILE.FILE_TYPE;
-- Cursor declaration to fetch data from table, row by row
CURSOR c_PRES IS
SELECT * from PRES;
BEGIN
-- Open File for writing
v_MYFileHandle := UTL_FILE.FOPEN(`c:\','presfix.csv','w');
-- Open the cursor
OPEN c_PRES;
LOOP
-- Get each row, one at a time
FETCH c_PRES INTO v_SSNO,v_FirstName,v_LastName;
-- Output as FIXED BLOCK file
EXIT WHEN c_PRES%NOTFOUND;
UTL_FILE.PUT_LINE(v_MYFileHandle, SUBSTR(v_SSNO,1,3) || `-'
|| SUBSTR(v_SSNO,4,2) || `-' || SUBSTR(v_SSNO,6,4)
|| v_FirstName || v_LastName);
DBMS_OUTPUT.PUT_LINE(v_SSNO || v_FirstName || v_LastName);
END LOOP;
-- Close Cursor
CLOSE c_PRES;
-- Close CSV file
UTL_FILE.FCLOSE(v_MYFileHandle);
END;
001010111George Washington 005124745Thomas Jefferson 223445726Richard Nixon 378112475Jimmy Carter 541129744Abraham Lincoln 587145961John Kennedy 665474112Theodore Roosevelt 725139511George Bush 998211247Ronald Reagan
Page 546
A No, because the new SYSDATE is assigned when the server is restored (Wednesday). The job will now be running every Wednesday until the job is altered, removed, or another problem occurs.
A When Oracle parses the data and removes the quotes, any part that does not have two sets of single quotes and is a string parameter will be stripped down to no single quotes and thus cause the job to fail.
A Only if you know his or her login and password and sign on as that person. In other words…no!
A Use ISUBMIT.
A SYSDATE + 1/24.
A The default of maxwait is 1000 days! A more typical approach is to set the wait period for up to 60 seconds.
A A maximum of 4096 bytes can be held in the message buffer before an overflow message will be displayed.
A One possible answer is
DECLARE
v_JobNum BINARY_INTEGER;
Page 547
BEGIN
DBMS_JOB.SUBMIT(v_JobNum,'PAYDAY(`'FRIDAY'',''BI_Monthly,6);',SYSDATE,
`NEXT_DAY(TRUNC(SYSDATE),''SATURDAY'') + 4/24');
END;
A One possible answer is
SELECT JOB,LAST_SEC,WHAT from USER_JOBS;
A One possible answer is
DECLARE
BEGIN
DBMS_JOB.ISUBMIT(200,'EASY;',SYSDATE,'SYSDATE + 1');
END;
(SYSDATE + 7).
A One possible answer is
BEGIN
DBMS_JOB.INTERVAL(200,'SYSDATE+7');
END;
A One possible answer is
BEGIN
DBMS_JOB.REMOVE(200);
END;
A One possible answer is
DECLARE
v_statpipe1 integer; -- status of private pipe
v_statpipe2 integer; -- status of public pipe
v_holdtype INTEGER; -- holds status of next item type
v_holdchar VARCHAR2(100);
v_holddate DATE;
v_holdnum NUMBER;
v_holdraw RAW(4000);
v_holdrowid ROWID;
BEGIN
-- start procedure of getting message from private pipe
v_statpipe1 := DBMS_PIPE.RECEIVE_MESSAGE(`myprivatepipe',15);
DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
DBMS_OUTPUT.PUT_LINE(v_holdchar);
-- display first datatype from message
DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
DBMS_OUTPUT.PUT_LINE(v_holdchar);
-- display second datatype from message
Page 548
-- start procedure of getting message from public pipe
v_statpipe2 := DBMS_PIPE.RECEIVE_MESSAGE(`mypublicpipe',10);
LOOP
v_holdtype := DBMS_PIPE.NEXT_ITEM_TYPE;
IF v_holdtype = 0 THEN EXIT;
ELSIF v_holdtype = 6 THEN
DBMS_PIPE.UNPACK_MESSAGE(v_holdnum);
ELSIF v_holdtype = 9 THEN
DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
ELSIF v_holdtype = 12 THEN
DBMS_PIPE.UNPACK_MESSAGE(v_holddate);
ELSIF v_holdtype = 11 THEN
DBMS_PIPE.UNPACK_MESSAGE_ROWID(v_holdrowid);
ELSIF v_holdtype = 23 THEN
DBMS_PIPE.UNPACK_MESSAGE_RAW(v_holdraw);
END IF;
END LOOP;
-- display all three types of data
DBMS_OUTPUT.PUT_LINE(v_holdchar || ` ` ||
v_holddate || ` ` || v_holdnum);
END;
A You would first create the table PIPETAB with
CREATE TABLE PIPETAB (
MyString VARCHAR2(100),
MyDate DATE,
MyNumber NUMBER)
/
DECLARE
v_statpipe2 integer; -- status of public pipe
v_holdtype INTEGER; -- holds status of next item type
v_holdchar VARCHAR2(100);
v_holddate DATE;
v_holdnum NUMBER;
v_holdraw RAW(4000);
v_holdrowid ROWID;
BEGIN
-- start procedure of getting message from public pipe
v_statpipe2 := DBMS_PIPE.RECEIVE_MESSAGE(`mypublicpipe',10);
LOOP
v_holdtype := DBMS_PIPE.NEXT_ITEM_TYPE;
IF v_holdtype = 0 THEN EXIT;
ELSIF v_holdtype = 6 THEN
DBMS_PIPE.UNPACK_MESSAGE(v_holdnum);
ELSIF v_holdtype = 9 THEN
DBMS_PIPE.UNPACK_MESSAGE(v_holdchar);
ELSIF v_holdtype = 12 THEN
Page 549
DBMS_PIPE.UNPACK_MESSAGE(v_holddate);
ELSIF v_holdtype = 11 THEN
DBMS_PIPE.UNPACK_MESSAGE_ROWID(v_holdrowid);
ELSIF v_holdtype = 23 THEN
DBMS_PIPE.UNPACK_MESSAGE_RAW(v_holdraw);
END IF;
END LOOP;
-- display all three types of data
DBMS_OUTPUT.PUT_LINE(v_holdchar || ` ` || v_holddate
|| ` ` || v_holdnum);
-- Insert Three Data Elements into the table
INSERT INTO PIPETAB(MyString,MyDate,MyNumber)
VALUES(v_holdchar,v_holddate,v_holdnum);
COMMIT;
END;
You can verify this by typing SELECT * from
PIPETAB;.
A 30 characters. It cannot begin with ORA$, which is reserved by Oracle.
A 1800 bytes.
A Only a string type VARCHAR2(1800) is passed and received.
A Unlike pipes, all 20 sessions will receive the alert.
A Commit
A One possible answer is
DECLARE
message VARCHAR2(1800); -- Display Incoming Message from Alert
alert_name VARCHAR2(30); -- Hold name of alert which was received
status INTEGER; -- Holds Status 0 if success, 1 if timed out
Page 550
BEGIN
DBMS_ALERT.REGISTER(`emp_change'); -- Registers for Alert emp_change
DBMS_ALERT.REGISTER(`my_test'); -- Registers for Alert my_test
DBMS_ALERT.REGISTER(`extra_alert'); -- Registers for Alert extra_alert
DBMS_ALERT.WAITANY(alert_name,message,status,600); -- Wait for alert
DBMS_OUTPUT.PUT_LINE(message); -- Display Message
DBMS_ALERT.REMOVEALL; -- Remove Registration for Alert
END;
A One possible answer is
DECLARE
IS_FIRE INTEGER := 0;
BEGIN
LOOP
IF IS_FIRE = 1 THEN
DBMS_ALERT.SIGNAL(`a_fire','A Fire has Broken Out');
EXIT;
END IF;
CHECKFIRE; -- procedure called to check for fire
END LOOP;
END;
A Java can access a relational database through the use of J/SQL or JDBC. J/SQL is the integration of SQL code within Java. JDBC is a driver-based approach to accessing the database from Java.
A There are several differences between PL/SQL and Java. To summarize them, I would say that Java lacks the data manipulation capabilities that PL/SQL provides. PL/SQL does not take full advantage of some common object-oriented features such as classes and inheritance. The differences between PL/SQL and Java are important to know because they will guide you to choosing the appropriate language for your application.
A JDBC allows Java applications to have data access from your Oracle database. JDBC uses a driver-based approach to database connectivity, which is very similar to the ODBC approach.