Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error viewing records in table that has abstract column datatype

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 name 
 
Please 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


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close