Home » SQL & PL/SQL » SQL & PL/SQL » object types in oracle.
object types in oracle. [message #186517] Tue, 08 August 2006 05:23 Go to next message
Messages: 1
Registered: August 2006
Location: delhi
Junior Member

I have few queries regarding accessing object types in oracle.

1) How can i pass a particular object of some type in a function and manipulate its value int function using some checks then after returning from the function manipulated value of object should be visible.

2) How to use variable of super type like in following case if i declare a variable to type Student_t then using that variable how can i access attribute name of Person_t.

If there is no way todo this then what is the best method for passing all the attributes of both types in a function using for some processing in Databases.

CREATE TYPE Person_t AS OBJECT ( ssn NUMBER,name VARCHAR2(30),address VARCHAR2(100)) NOT FINAL;

CREATE TYPE Student_t UNDER Person_t ( deptid NUMBER,major VARCHAR2(30));

Pls help me i have donen alot of R&D on it but got nothing. Thanks in advance
Re: object types in oracle. [message #186580 is a reply to message #186517] Tue, 08 August 2006 10:18 Go to previous message
Messages: 173
Registered: January 2006
Senior Member
1) What have you tried so far, and what problems/errors are you facing?

2) If the variable is of type Student_t, simply use the dot notation <subtype object instance>.<supertype attribute> to refer to Person_t attributes.
  2      i_st    Student_t := Student_t(1, 'hobbes', 'Jungle', 10, 'English');
  3    BEGIN
  4      -- Access attributes of Person_t
  5      dbms_output.put_line('SSN:    '||i_st.ssn);
  7      -- Access attributes of Student_t
  8      dbms_output.put_line('DeptId: '||i_st.deptid);
  9  END;
 10  /
SSN:    1
DeptId: 10

PL/SQL procedure successfully completed.
If the variable is of type Person_t, you might need to use the TREAT function
  2      i_st    Student_t;
  3      i_p     Person_t  := Student_t(1, 'hobbes', 'Jungle', 10, 'English');
  4    BEGIN
  5      i_st := TREAT(i_p AS Student_t);
  7      -- Access attributes of Person_t
  8      dbms_output.put_line('SSN:    '||i_st.ssn);
 10      -- Access attributes of Student_t
 11      dbms_output.put_line('DeptId: '||i_st.deptid);
 12  END;
 13  /
SSN:    1
DeptId: 10

PL/SQL procedure successfully completed.

Previous Topic: Createing a schema with execute immediate
Next Topic: Still not getting it ~ Please help
Goto Forum:

Current Time: Thu Jan 19 11:19:00 CST 2017

Total time taken to generate the page: 0.10380 seconds