The error message contains information about the line number and column in which the error occurred and about the type of error.
To recreate a type after fixing the compilation errors, use
CREATE OR REPLACE ....
instead of DROP TYPE ... and CREATE. But this will only work if the type does not yet have dependent tables. A type with dependent tables can only be ALTERed, not REPLACEd.
The following example shows a type job2, which contains the same columns as the type job (from last week's exercises), but also declares a method "evaluate_qualification".
CREATE OR REPLACE TYPE job2 AS OBJECT (
MEMBER FUNCTION evaluate_qualification RETURN STRING
The details of "evaluate_qualification" are defined in the type body. It prints "too bad" for all jobs that have less than 2 years of experience, "OK" for 2 years of experience and "great" for more than two years of experience.
CREATE OR REPLACE TYPE BODY job2 AS
MEMBER FUNCTION evaluate_qualification RETURN STRING IS
IF self.years_of_experience < 2 THEN
RETURN 'too bad';
ELSIF self.years_of_experience = 2 THEN
PL/SQL is fairly easy to use. If a method needs any variables, they must be declared between IS and BEGIN. Datatypes are STRING, REAL and NUMBER (not INT or VARCHAR). The word "self" refers to the current object, with which the method is called.
If j is a table alias for a table of type job2, then this method can be invoked with
The brackets are always required even if the method has no arguments. SQL select statements can only invoke methods that do not change the data in the tables. Therefore, only functions, which return values, but not procedures, which change data, can be invoked in queries.
The job type from last week's exercises can be altered:
ALTER TYPE job
ADD MEMBER FUNCTION evaluate_qualification RETURN STRING
"CASCADE" makes certain that the alterations apply to existing dependent object tables. It is necessary to logout of SQLPLUS and login again after altering types with dependent tables.
The type body for "job" can then be defined in the same manner as for "job2".
The following map method orders the objects of type job (or job2) by years_of_experience multiplied with salary_amount.
ALTER TYPE job
ADD MAP MEMBER FUNCTION sorting RETURN NUMBER
CREATE OR REPLACE TYPE BODY job AS
MAP MEMBER FUNCTION sorting RETURN NUMBER IS
RETURN self.years_of_experience * self.salary_amount;
It is now possible to compare objects from job_table (for example, j1.sorting() < j2.sorting()) or to use sorting() in ORDER BY, GROUP BY and other statements.
select * from job_table j order by j.sorting() desc;