Member Procedure -Object type

also post the table structure...

desc table_name

 Here's the table Structure

STUDENT_ID  NOT NULL  NUMBER(6)  
FIRST_NAME     VARCHAR2(30)  
LAST_NAME     VARCHAR2(30)  
DOB     DATE  
AGE     NUMBER(2)  
MODE_OF_ATTENDANCE     VARCHAR2(20)  
WIDENING_PARTICIPATION_ID     NUMBER(2)  
COURSE_CODE     VARCHAR2(7)  
LEVEL_ID     VARCHAR2(1)  
DESTINATION_CODE     VARCHAR2(2)  
OUTCOME_ID     NUMBER(1)  
DISABILITY_CODE     VARCHAR2(2)  
LDIFFICULTY_CODE     VARCHAR2(2)

Cheers

Hi,

Here's a quick sample. I think you can adjust it to fit your needs. (btw. it's not actually necessary to include all attributes in the Object Type, it must have at least one)

SQL> create table t
  2  (id number(6)
  3  ,name varchar2(30)
  4  )
  5  /

Table created.

SQL> create type student_ot as object
  2  (id number(6)
  3  ,name varchar2(30)
  4  ,member procedure addStudent (p_id in number
  5                               ,p_name in varchar2
  6                               )
  7  ,member procedure deleteStudent (p_id in number)
  8  ,member procedure amendStudent (p_id in number
  9                                 ,p_new_name in varchar2
 10                                 )
 11  )
 12  /

Type created.

SQL> create or replace type body student_ot
  2  is
  3  member procedure addStudent (p_id in number
  4                               ,p_name in varchar2
  5                               )
  6  is
  7  begin
  8     insert into t
  9        (id
 10        ,name
 11        )
 12     values
 13        (p_id
 14        ,p_name
 15        );
 16  end;
 17  member procedure deleteStudent (p_id in number)
 18  is
 19  begin
 20     delete from t
 21     where  id = p_id
 22     ;
 23  end;
 24  member procedure amendStudent (p_id in number
 25                                ,p_new_name in varchar2
 26                                )
 27  is
 28  begin
 29     update t
 30     set    name = p_new_name
 31     where  id   = p_id
 32     ;
 33  end;
 34* end;
SQL> /

Type body created.

SQL>

Alex

Thanks Alex I will try it out and let you know.

CREATE OR REPLACE type body Student1_objtyp as
member procedure addStudent(p_First_Name varchar2, p_Last_Name varchar2,p_DOB date,p_age number default null, p_Mode_of_Attendance number, p_Widening_Participation number,p_Course_ID number , p_Destination_Code number,  p_Outcome_ID number,p_Disability_Code number, p_LDifficulty_Code number)
   is
   begin
      insert into Student1_objtyp(Student_ID, First_Name, Last_name , DOB, Age, Mode_of_Attendance, Widening_Participation, Course_ID , Destination_Code ,  Outcome_ID,Disability_Code , LDifficulty_Code)
     values
        (Student_sequence.nextval ,p_First_Name,p_Last_Name, p_DOB , p_Age, p_Mode_of_Attendance , p_Widening_Participation ,p_Course_ID  , p_Destination_Code ,  p_Outcome_ID ,p_Disability_Code,p_LDifficulty_Code);
end addstudent;


member procedure DeleteStudent(p_id IN students_ID%TYPE)
is
begin
delete from Student1_objtyp
where  Student_ID = p_id;
end DeleteStudent;


member procedure AmendStudent(p_id IN students_ID%TYPE, p_First_Name varchar2, p_Last_Name varchar2,p_DOB date,p_age number default null, p_Mode_of_Attendance number, p_Widening_Participation number,p_Course_ID number , p_Destination_Code number,  p_Outcome_ID number,p_Disability_Code number, p_LDifficulty_Code number)
 is
   begin
    update Student1_objtyp
   set    First_Name  = p_First_Name
     where  Student_ID    = p_id;
End AmendStudent;

member procedure CalcCurrentAge(p_current_age date)
is
begin
p_current_age := trunc(SYSDATE - DOB) / 365.25);
End CalcCurrentAge;
end;
/
show errors


I managed to write the code but this following error keps on appearing on the screen
can anyone please help.
thanks

Warning: Type Body created with compilation errors.
Errors for TYPE BODY STUDENT1_OBJTYP:
 
  PLS-00103: Encountered the symbol ")" when expecting one of the f ollowing: * & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between ||  

Now that I manage to get over the last error a whole new list of errors has cropped up.

The list of errors
2/18  PLS-00539: subprogram 'ADDSTUDENT' is declared in an object type body and must be defined in the object type specification  

3/18  PLS-00538: subprogram or cursor 'ADDSTUDENT' is declared in an ob ject type specification and must be defined in the object type bo dy
 
4/18  PLS-00538: subprogram or cursor 'DELETESTUDENT' is declared in an object type specification and must be defined in the object type body  

5/7  PL/SQL: SQL Statement ignored  

5/19  PL/SQL: ORA-04044: procedure, function, package, or type is not a llowed here  
6/18  PLS-00538: subprogram or cursor 'AMENDSTUDENT' is declared in an object type specification and must be defined in the object type body  

7/20  PLS-00538: subprogram or cursor 'CALCCURRENTAGE' is declared in a n object type specification and must be defined in the object typ e body  

11/8  PL/SQL: Item ignored  

11/18  PLS-00539: subprogram 'DELETESTUDENT' is declared in an object ty pe body and must be defined in the object type specification  

11/40  PLS-00201: identifier 'STUDENTS_ID' must be declared  
19/8  PL/SQL: Item ignored  

19/18  PLS-00539: subprogram 'AMENDSTUDENT' is declared in an object typ e body and must be defined in the object type specification  

19/39  PLS-00201: identifier 'STUDENTS_ID' must be declared  
27/18  PLS-00539: subprogram 'CALCCURRENTAGE' is declared in an object t ype body and must be defined in the object type specification  
LINE/COL  ERROR  
30/1  PLS-00363: expression 'P_CURRENT_AGE' cannot be used as an assign ment target  
30/1  PL/SQL: Statement ignored  

Can anyone offer any help to solve these errors.

With Objects, you can't anchor variables to database tables. (as in %type)

The members in Type and Type Body must be exactly the same.


p_current_age is an IN-parameter, you can't assign values to IN-Parameters. (why don't you make it a function?)

This will take care of most of the compilation errors you're getting.

Alex

I took your advice and made it a fucntion, however I keep on getting the following error.

PLS-00103: Encountered the symbol ";" when expecting one of the f ollowing: constant exception <an identifier> <a double-quo ted delimited-identifier> table LONG_ double ref char time tim estamp interval date binary national character nchar The symbo l "exception" was substituted for ";" to continue.

This is the code


 CREATE OR REPLACE type body Student1_objtyp as
Member Procedure AddStudent(Student_ID number, First_Name varchar2, Last_Name varchar2,DOB date,age number, Mode_of_Attendance number, Widening_Participation number,Course_ID number , Destination_Code number,  Outcome_ID number,Disability_Code number, LDifficulty_Code number)is
   begin
    insert into Student1_objtyp(Student_ID, First_Name, Last_name , DOB, Age, Mode_of_Attendance, Widening_Participation, Course_ID , Destination_Code ,  Outcome_ID,Disability_Code , LDifficulty_Code)
     values
        (Student_sequence.nextval ,p_First_Name,p_Last_Name, p_DOB , p_Age, p_Mode_of_Attendance , p_Widening_Participation ,p_Course_ID  , p_Destination_Code ,  p_Outcome_ID ,p_Disability_Code,p_LDifficulty_Code);
end addstudent;


Member Procedure DeleteStudent(Student_ID NUMBER)
is
begin
delete from Student1_objtyp
where  Student_ID = p_id;
end DeleteStudent;


Member Procedure AmendStudent(Students_ID number, First_Name varchar2, Last_Name varchar2,DOB date,age number, Mode_of_Attendance number, Widening_Participation number,p_Course_ID number , Destination_Code number,  Outcome_ID number,Disability_Code number, LDifficulty_Code number)is
   begin
    update Student1_objtyp
   set    First_Name  = p_First_Name
     where  Student_ID    = p_id;

End AmendStudent;

Member function CalcCurrentAge(DOB date)return number
is
p_current_age;
begin
p_current_age := round((sysdate - DOB)/365,0);
return p_current_age;
End CalcCurrentAge;
end;

Hi,

Here's a sample that should work...
The error you mention originated from the calcCurrentAge function (usually no one listens when I suggest to change something, thanks!). The local variable (p_Current_age) doesn't have a datatype.

I noticed that the parameter names are different than the ones you're using in the procedures and functions. Naming parameters can be an issue, especially with things like:
Procedure DeleteStudent(Student_ID NUMBER)
is
begin
   delete from Student1_objtyp
   where  Student_ID = student_id; --<----------
end DeleteStudent;

Even though you mean the Student_id parameter, the delete statement will completely clean out the Student1_objtyp table....

Alex

create or replace type Student1_objtyp as object
(x int
,member procedure addStudent (p_first_name in varchar2
                             ,p_dob        in date
                             )
,member procedure deleteStudent(p_student_id in number)
,member procedure amendStudent(p_Student_ID in number, p_First_Name in varchar2)
,member function CalcCurrentAge(DOB in date) return number
);
/
CREATE OR REPLACE type body Student1_objtyp as
Member Procedure AddStudent(p_First_Name varchar2
                           ,p_DOB in date
)is
   begin
    insert into Student1_objtyp (Student_ID, First_Name, dob)
     values
        (Student_sequence.nextval ,p_First_Name, p_DOB);
end addstudent;


Member Procedure DeleteStudent(p_Student_ID in NUMBER)
is
begin
   delete from Student1_objtyp
   where  Student_ID = p_student_id; --<---
end DeleteStudent;


Member Procedure AmendStudent(p_Student_ID in number, p_First_Name in varchar2)
is
begin
    update Student1_objtyp
    set    First_Name  = p_First_Name
    where  Student_ID  = p_student_id;
End AmendStudent;

Member function CalcCurrentAge (DOB in date)
   return number
is
   p_current_age number; --<--
begin
   p_current_age := round((sysdate - DOB)/365,0);
   return p_current_age;
End CalcCurrentAge;
end;
/

This is also pretty cool (if I say so myself...)

  1  create or replace type stud_ot as object
  2  (id number
  3  ,fname varchar2(50)
  4  ,lname varchar2(50)
  5  ,member procedure addStud
  6  ,member procedure removeStud
  7* )
SQL> /

Type created.

SQL> create table t of stud_ot
  2  /

Table created.

  1  create or replace type body stud_ot
  2  is
  3     member procedure addStud
  4     is
  5     begin
  6        insert into t
  7        values (self);
  8     end addStud;
  9     member procedure removeStud
 10     is
 11     begin
 12        delete from t
 13        where  id = self.id;
 14     end removeStud;
 15* end;
SQL> /

Type body created.

SQL> declare
  2     s stud_ot := stud_ot (1, 'Alex', 'Nuijten');
  3     another stud_ot := stud_ot (2, 'Cassius', 'Duke');
  4  begin
  5     s.addStud;
  6     s.removeStud;
  7     another.addStud;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from t
  2  /

        ID FNAME                     LNAME
---------- ------------------------- -------------------------
         2 Cassius                   Duke

...and in 9i, you can even have Type Evolution: (this is neat stuff...)
SQL> alter type stud_ot add attribute (dob date) cascade
  2  /

Type altered.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 ID                                                 NUMBER
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(50)
 DOB                                                DATE

SQL> select *
  2  from   t
  3  /

        ID FNAME                     LNAME                     DOB
---------- ------------------------- ------------------------- ---------
         2 Cassius                   Duke

thanks Alex
I will try the code and let you know
thanks again

I followed your procedures and type the following but still it showing the erros shown below, I donot know what it means. and also how can I test the procedures to make sure that they are working?

Can you please help!
thanks

Type
drop type Student1_objtyp ;
create type Student1_objtyp as object

(Student_ID Number(6), First_Name varchar2(30), Last_name varchar2(30), DOB Date, Age number(2), Mode_of_Attendance varchar2(20), Widening_Participation_ID Number(2), Course_ID varchar2 (8), Destination_Code varchar2(2),Outcome_ID Number(1),Disability_Code varchar2(2),  LDifficulty_Code varchar2(2),

Member Procedure AddStudent(p_First_Name varchar2, p_Last_Name varchar2,p_DOB date,p_age number default null, p_Mode_of_Attendance number, p_Widening_Participation number,p_Course_ID number , p_Destination_Code number,  p_Outcome_ID number,p_Disability_Code number, p_LDifficulty_Code number),
Member Procedure DeleteStudent(Student_ID NUMBER, p_id number),

Member Procedure AmendStudent(p_Student_id number, p_First_Name varchar2)
, Member function CalcCurrentAge(DOB date)return number);
/
show errors






Body

CREATE OR REPLACE type body Student1_objtyp as

Member Procedure AddStudent(p_First_Name varchar2, p_Last_Name varchar2,p_DOB date,p_age number, p_Mode_of_Attendance number, p_Widening_Participation number,p_Course_ID number , p_Destination_Code number,  p_Outcome_ID number,p_Disability_Code number, p_LDifficulty_Code number)is

   begin
    insert into Student1_objtyp(Student_ID, First_Name, Last_name , DOB, Age, Mode_of_Attendance, Widening_Participation, Course_ID , Destination_Code ,  Outcome_ID,Disability_Code , LDifficulty_Code)
     values
        (Student_sequence.nextval ,p_First_Name,p_Last_Name, p_DOB , p_Age, p_Mode_of_Attendance, p_Widening_Participation ,p_Course_ID, p_Destination_Code ,  p_Outcome_ID ,p_Disability_Code,p_LDifficulty_Code);
end addstudent;


Member Procedure DeleteStudent(Student_ID NUMBER, p_id number)
is
begin
delete from Student1_objtyp
where  Student_ID = p_id;
end DeleteStudent;


member procedure AmendStudent( p_Student_id number, p_First_Name varchar2)
 is
   begin
    update Student1_objtyp
   set    First_Name  = p_First_Name
     where  Student_ID = p_Student_id;
End AmendStudent;

Member function CalcCurrentAge(DOB date)return number
is
p_current_age number;
begin
p_current_age :=round((sysdate - DOB)/365,0);
return p_current_age;
End CalcCurrentAge;
end;
/
show errors



LINE/COL  ERROR  
6/5  PL/SQL: SQL Statement ignored  
6/17  PL/SQL: ORA-04044: procedure, function, package, or type is not a llowed here  
15/1  PL/SQL: SQL Statement ignored  
15/13  PL/SQL: ORA-04044: procedure, function, package, or type is not a llowed here  
23/5  PL/SQL: SQL Statement ignored  
23/12  PL/SQL: ORA-04044: procedure, function, package, or type is not a llowed here

Thanks Alex, I will try this solution and let you know!

Alex,

I noticed that in the last piece of code you wrote you created a table.  The thing is I did already have a student table created.  I also wanted to know why can't I use a sequence to automatically add student ID.

CREATE OR REPLACE type body Student1_objtyp as

Member Procedure AddStudent(p_First_Name varchar2, p_Last_Name varchar2,p_DOB date,p_age number, p_Mode_of_Attendance number, p_Widening_Participation number,p_Course_ID number , p_Destination_Code number,  p_Outcome_ID number,p_Disability_Code number, p_LDifficulty_Code number)is

   begin
    insert into Student1_objtab(Student_ID, First_Name, Last_name , DOB, Age, Mode_of_Attendance, Widening_Participation, Course_ID , Destination_Code,  Outcome_ID, Disability_Code, LDifficulty_Code)
     values
        (Student1_sequence.nextval ,p_First_Name,p_Last_Name, p_DOB , p_Age, p_Mode_of_Attendance, p_Widening_Participation, p_Course_ID, p_Destination_Code,  p_Outcome_ID ,p_Disability_Code,p_LDifficulty_Code);
end addstudent;


Member Procedure DeleteStudent(Student_ID NUMBER, p_id number)
is
begin
delete from Student1_objtab
where  Student_ID = p_id;
end DeleteStudent;


member procedure AmendStudent( p_Student_id number, p_First_Name varchar2)
 is
   begin
    update Student1_objtab
   set    First_Name  = p_First_Name
     where  Student_ID = p_Student_id;
End AmendStudent;

Member function CalcCurrentAge(DOB date)return number
is
p_current_age number;
begin
p_current_age := round((sysdate - DOB)/365,0);
return p_current_age;
End CalcCurrentAge;
end;
/
show errors

6/5      PL/SQL: SQL Statement ignored
8/10     PL/SQL: ORA-02289: sequence does not exist

I decided not to use a sequence although I would have liked to, but without the sequence I can now create the body without any errors, thanks to you.

Do you know how can I test these methods, I am using oracle 9i forms, so I will have a student form where the user could add, delete and amend the students details.

But I need to test the methods before I can use them on the form, do you know how can I test these methods.


CassiusDuke

This is a test using SQL*Plus (and using a sequence):
create sequence student_seq
/
declare
   stud01 student1_objtyp;
   seq      pls_integer;
begin
   select student_seq.nextval into seq from dual;
   stud01 := := student1_objtyp (seq
                                             ,'Cassius'
                                             ,'Duke'
                                             ,to_date ('10-02-1975','dd-mm-yyyy')
                                             ,98
                                             ,'Good'
                                             ,77
                                             ,'WeIUt90'
                                             ,'VA'
                                             ,2
                                             ,'Re'
                                             ,'P3'
                                             );
   stud01.addStudent;
end;
/
select * from student
/

I don't know how to use Objects with Forms 9i. Can't you just use the Student table as a base table block?

... If you're going to use Forms, then why are you using Objects in the first place? Why don't you use an "ordinary" table as your base table?

Alex

I am using objects because I am creating an object-oriented database system, and in some parts of the system I need to be able to use overidding methods which can be easiliy done with objects.
I still need to write those methods mind you!
Anyways thanks for the above code.

Cassius

Maybe you can use unbound items in Forms and buttons to call the appropriate methods to fill/update the database tables. I haven't tried it, but I don't see why it wouldn't work.

... but what do you do when the end-user wants to get information on a Student?

Hi,

I have created the Student form in oracle 9i forms developer and placed different button which would add/ delete/update/query student details etcc.. and they all work. so if the user wants to get information on a student they can just put the student_id and click on one of the buttons to get the student details. I could have done it without your help though!

I wondered if you could advise me on how to write the following methods
I have a student_support object type, table and specification
 
drop type Student_Support_objtyp;
create type Student_Support_objtyp as object
(Student_ID Number(6),
  Tutor_ID number(3),
  Support_type varchar2 (30),
  Start_Date Date,
  Finish_Date Date,
  Pre_entry_activity_cost Number(8,2),
  Initial_Assessment_cost number(8,2),
  LD_Assessment_Cost Number(8,2),
  Number_in_group number(2),
  Hours_per_week number(3),
  Weeks_Per_Year number(3), Number_of_terms number(1),
  Exam_invigilator_cost number(8,2),
  Consumables number(8,2),
  Computer_Training number (8,2),
  Note_Taker number(8,2),
  Reader number(8,2),
  Amanuensis Number(8,2),
  Equipment_Depreciation_cost number(8,2),
  Admin_cost number(8,2),
  ReviewReport_cost number(8,2),
  Total_cost number(8,2),
  Member function GetTutorsrate(Tutor_id number, Rate_per_hour number)return number,

 member procedure CalcSupportCost(rate_per_hour number, Support_type varchar2,Hours_per_week number,
 Weeks_Per_Year number,
 Number_of_terms number,Number_in_group number,ReviewReport_cost number),
 
 Member Procedure CalccTotalCost(cost_of_support number,Total_cost number,Exam_invigilator_cost number, Consumables number,Computer_Training number, Note_Taker number,Reader number, Amanuensis Number, Equipment_Depreciation_cost number, Admin_cost number),
 member procedure calcRate(Total_cost number ,Rate number))not final;
/
Show errors

create table Student_Support_objtab of Student_Support_objtyp;



Create or replace type body Student_Support_objtyp as Member function GetTutorsrate
(Tutor_id number, Rate_per_hour number) return number
is
rate_per_hour number;
begin
select Rate_per_hour
from Tutor_objtab
where Student_support_objtab.Tutor_id = Tutor.Tutor_id;
return rate_per_hour;
end GetTutorsrate;


member procedure CalcSupportCost(rate_per_hour number, Support_type varchar2,Hours_per_week number,
 Weeks_Per_Year number, Number_of_terms number,Number_in_group number,ReviewReport_cost number, Support_cost number)
 is
 begin
 Support_cost :=((Hours_per_week * Weeks_Per_Year)/ Number_in_group)+(ReviewReport_cost * Number_of_terms));
 end CalcSupportCost;
 
 
 Member Procedure CalccTotalCost(cost_of_support number,Total_cost number,Exam_invigilator_cost number, Consumables number,Computer_Training number, Note_Taker number,Reader number, Amanuensis Number, Equipment_Depreciation_cost number, Admin_cost number)
 is
 begin
 
 Total_cost:= (cost_of_support + Exam_invigilator_cost+ Consumables + Computer_Training + Note_Taker + Reader + Amanuensis + Equipment_Depreciation_cost + Admin_cost);
end CalccTotalCost;
 
 member procedure calcRate(Total_cost number ,Rate number) is
 begin
select rate
 from lookup
 where Student_support.total_cost = lookup.total_cost;
 end calcRate;
 end;
/
Show errors

I tried to write the body for the student_support type but it doesnot work.  There must be something wrong with my code.
Can you please help!
Thanks.
Cassiusduke

" I could have done it without your help though!"... well... if that's the case ;-)

The first Error I got (on Type Body):
LINE/COL ERROR
-------- -----------------------------------------------------------------
17/107   PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         * & = - + ; < / > at in is mod not rem <an exponent (**)>
         <> or != or ~= >= <= <> and or like between || year DAY_

tells me the parens are imbalanced on line 17. (and they are)

Then we run into more trouble:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/44     PLS-00410: duplicate fields in RECORD,TABLE or argument list are
         not permitted

1/44     PL/SQL: Item ignored
13/18    PLS-00539: subprogram 'CALCSUPPORTCOST' is declared in an object
         type body and must be defined in the object type specification

17/2     PLS-00363: expression 'SUPPORT_COST' cannot be used as an
         assignment target

17/2     PL/SQL: Statement ignored
25/2     PLS-00363: expression 'TOTAL_COST' cannot be used as an
         assignment target

25/2     PL/SQL: Statement ignored
25/19    PLS-00538: subprogram or cursor 'CALCSUPPORTCOST' is declared in
         an object type specification and must be defined in the object
         type body

30/1     PL/SQL: SQL Statement ignored
31/7     PL/SQL: ORA-00942: table or view does not exist


The parameter names in the first function are equal to attributes of the Object. This is not allowed. Suggestion: prefix your parameters with p_ so it's easy to distinguish between parameters and attributes.

The implementation of a member procedures is missing in the Type Body.
CalcSupportCost is different in number of parameters.

SUPPORT_COST is a parameter in one of the procedures, it defaults to an IN-type. You cannot assign values to IN -parameters. It seems you want to retrieve a value using this procedure. You can do either two things:
1) make it an OUT parameter
2) make it a FUNCTION

Hope this helps you on the way,
Alex

Alex,

I am really really Sorry, I meant to say I couldn't have done it with out you (I hope you gathered that).

I will try out what you have suggested and see if it works.

Thanks for all your help

cassiusduke

... yes I gathered that, hence the ";-)"  at the end of the line.

Alex