I tried to view records in an table that has one of its columns with an abstract datatype. I created the following type:
create or replace type marks_ty as object (m1 number(3), m2 number(3), m3 number(3), member function totmarks(m1 in number,m2 in number,m3 in number) return number); /Then I created type body as follows:
create or replace type body marks_ty as member function totmarks(m1 number,m2 number,m3 number) return number is begin return (m1+m2+m3); end; end; /Then I created the following table:
create table stdmarks ( student_id number(4), marks marks_ty);Then I inserted a couple of records. After that I issued the following statement:
select marks.totmarks(marks.m1,marks.m2,marks.m3) from stdmarks;But I get the following error:
select marks.totmarks(marks.m1,marks.m2,marks.m3) from stdmarks * ERROR at line 1: ORA-00904: invalid column namePlease help me. I tried the same in Oracle 8i, as well as 9i enterprise edition.
One of the lesser knowns facts about your operation is that you need to alias the table name, and use that alias when calling the member function. So change your query to the following:
SELECT s.marks.totmarks(marks.m1,marks.m2,marks.m3) FROM stdmarks s;
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.