Trouble with SELECT INTO In Oracle OBJECT TYPE Member Function

Question

I'm running into a bit of an issue with using oracle object types. My member function is compiling with errors.

Here's the type:

 CREATE OR REPLACE TYPE t_Customer as OBJECT
 (custID NUMBER 
  ,fname varchar2(50)
  ,lname varchar2(50)
  ,MEMBER FUNCTION getHighest RETURN INTEGER
 );

 CREATE OR REPLACE TYPE t_Order AS OBJECT
 (OrderID NUMBER, custID REF t_Customer, quantity INTEGER);

 CREATE TABLE Order_Tbl of t_Order; 
 CREATE TABLE Customer_Tbl of t_Customer;

 CREATE OR REPLACE TYPE BODY t_Customer AS
  MEMBER FUNCTION getHighest RETURN INTEGER IS
   v_max integer;
   BEGIN
      SELECT Max(Order.quantity) INTO v_max FROM Order WHERE Order.CustID = self.custID;
       return v_max;
  end;

My SELECT INTO is not working. It's telling me I have invalid identifiers. If I want to write a member function to return the highest quantity order for a customer, do I query the table of Order, or can I use the object reference? I also tried creating views to no avail.

This is the easiest I could simplify it, I also am going to need to write some other methods, but none of the ones where I need to SELECT INTO work as of now.

Errors are SELF.custid INVALID IDENTIFIER and Component 'custid' must be declared. Thanks

EDIT: SELECT INTO pl/sql queries to access object types and their views must be aliased. After adding aliases, my problems were solved. Thanks for the help though - I posted the solution and a couple of examples.

Answer 1

You must use an alias to access Object fields in Oracle. (11g)

Examples:

Wrong SELECT Order.quantity FROM Customers

Wrong SELECT Customers.Order.quantity FROM Customers

Right SELECT cc.Order.quantity FROM Customers cc

I assumed that the second option in the list would work, but it doesnt. You have to use an alias.

Working example as per request:

       DROP Table tblCustomer;
DROP Table tblOrders;
CREATE OR REPLACE TYPE t_Customer as OBJECT
  (custID varchar2(20)
  ,lastname varchar2(50)
  ,firstname varchar2(50)
  ,member function getHighest RETURN NUMBER
  );
 / 
 CREATE OR REPLACE TYPE t_Orders AS OBJECT
  (OrderID Number
  ,Customer REF t_Customer
  ,quantity NUMBER
 );
  /
 CREATE TABLE tblOrders of t_orders;
 CREATE TABLE tblCustomer of t_Customer;

 CREATE OR REPLACE VIEW OrderOV(ord) AS
   SELECT t_orders(OrderID, Customer, quantity) FROM tblOrders;
   /
    CREATE OR REPLACE VIEW CustomerOV(cust) AS
   SELECT t_customer(custID, lastname, firstname) FROM tblCustomer;
   /

    CREATE OR REPLACE TYPE BODY t_Customer AS
      MEMBER Function getHighest RETURN NUMBER IS
      v_maxval NUMBER;

      BEGIN

    SELECT max(orderOV.ord.quantity) INTO v_maxval FROM OrderOV WHERE OrderOV.ord.custID = self.CustID;
      END;
     end;
    /

The line inside the function body can be switched out for the correct aliased version. SELECT Max(e.ord.quantity) INTO v_maxval FROM OrderOV e WHERE e.ord.customer.custID = self.custID;

You can paste this whole script to test, it compiles when you switch the line in question with the correct line I listed.