Solved

Cannot EXTEND Nested Table Collection in OBJECT

Posted on 2004-09-07
6
958 Views
Last Modified: 2011-08-18
What is wrong with the following (Oracle 8i and Oracle 9.2)

CREATE OR REPLACE TYPE OrderDetail AS OBJECT(
  Line NUMBER,
  Description VARCHAR2( 35),
  Qty NUMBER,
  Price NUMBER
);
/
CREATE OR REPLACE TYPE OrderDetailTable AS TABLE OF OrderDetail;
/
CREATE OR REPLACE TYPE OrderDetails AS OBJECT(
  m_Details OrderDetailTable,
  MEMBER PROCEDURE clear,
  MEMBER FUNCTION addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY OrderDetails AS
  MEMBER PROCEDURE clear IS
  BEGIN
    m_Details := OrderDetailTable();
  END;
  MEMBER FUNCTION addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN NUMBER IS
    v_Size NUMBER;
  BEGIN
    m_Details.EXTEND; -- Error here
    v_Size := m_Details.LAST;
    RETURN v_Size;
  END;
END;
/

The TYPE BODY gives the following error

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5     PLS-00363: expression 'SELF.M_DETAILS' cannot be used as an
         assignment target

11/5     PL/SQL: Statement ignored


Line 11 is m_Details.EXTEND; -- it's not an assignment at all.

Is there another way to accomplish this (having a collection inside an object. It must work in Oracle 8i).
0
Comment
Question by:mjzalewski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 12004638
Does this help you ?

CREATE OR REPLACE TYPE OrderDetailTable AS TABLE OF OrderDetail;
/
CREATE OR REPLACE TYPE OrderDetails AS OBJECT(
  m_Details OrderDetailTable,
  constructor FUNCTION OrderDetails( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY OrderDetails AS
  CONSTRUCTOR FUNCTION OrderDetails( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN SELF AS RESULT IS
  BEGIN
    self.m_Details.EXTEND(1);
    RETURN;
  END;
END;
/
0
 
LVL 3

Author Comment

by:mjzalewski
ID: 12005263
Not quite what I need. But it may have got me thinking along the right lines.

I need the function addItem to return a NUMBER, not the whole object. So it can't be a CONSTRUCTOR. But I found that I can change MEMBER FUNCTION to MEMBER PROCEDURE like this:

CREATE OR REPLACE TYPE OrderDetails AS OBJECT(
  m_Details OrderDetailTable,
  MEMBER PROCEDURE clear,
  MEMBER PROCEDURE addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER, p_Size OUT NUMBER)
);
/
CREATE OR REPLACE TYPE BODY OrderDetails AS
  MEMBER PROCEDURE clear IS
  BEGIN
    m_Details := OrderDetailTable();
  END;
  MEMBER PROCEDURE addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER, p_Size OUT NUMBER) IS
    v_Size NUMBER;
  BEGIN
    m_Details.EXTEND;
    v_Size := m_Details.LAST;
    -- More computation here, possibly EXTEND more times
    p_Size := v_Size;
  END;
END;
/

Why does MEMBER FUNCTION fail and MEMBER PROCEDURE succeeds? Is it true that a FUNCTION must not change the state of the object? Is there a PRAGMA that will allow a FUNCTION to change the object state?

0
 
LVL 12

Expert Comment

by:geotiger
ID: 12006017
The OrderDetailTable is a datatype not a value. I think that you need to use it to define data type for m_details not assign the type to it.


CREATE OR REPLACE TYPE BODY OrderDetails AS
  MEMBER PROCEDURE clear IS
  BEGIN
    m_Details OrderDetailTable();
  END;
  MEMBER PROCEDURE addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER, p_Size OUT NUMBER) IS
    v_Size NUMBER;
  BEGIN
    m_Details.EXTEND;
    v_Size := m_Details.LAST;
    -- More computation here, possibly EXTEND more times
    p_Size := v_Size;
  END;
END;
/
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 12006330
I think it is because you can select a function within a sql expression.  Prob too difficult for the PL/SQL implementors to ensure that  the underlying data is not being changed.  A procedure, I surmise, implies a pl/sql context.
0
 
LVL 3

Author Comment

by:mjzalewski
ID: 12009231
I think earthman2 has the best answer so far. Can anyone explain why m_Details.EXTEND works in a MEMBER PROCEDURE or CONSTRUCTOR FUNCTION,  but gives the error in a MEMBER FUNCTION?

geotiger: I don't understand your comment. You spit out the same TYPE BODY as I posted above (the version that works). My problem was trying to do the same thing in a MEMBER FUNCTION, which gives the error I reported at the top of the thread. Of course I know OrderDetailTable is a type (and m_Details is the actual variable). Did I not define it in the second line of the TYPE statement? Do you think I need something that matches in the TYPE BODY?

CREATE OR REPLACE TYPE OrderDetails AS OBJECT(
  m_Details OrderDetailTable,
  MEMBER PROCEDURE clear,
  MEMBER FUNCTION addItem( p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN NUMBER
);
/

Anyway, the problem seems to be that 'addItem' cannot be a MEMBER FUNCTION if it also alters the object state. I don't see where this is explained in the documentation. But I have to believe that earthman2 is close to the correct answer when he says 'you can select a function within an SQL expression'.
0
 

Expert Comment

by:PabloHawk
ID: 36252133
I know this is an old question, but I struggled earlier today with a very similar scenario and found a solution elsewhere that I adapted to fix my issue.  It seems that when using a nested collection object that you are trying to alter, you must pass in the implied SELF parameter to the function, i.e.:

CREATE OR REPLACE TYPE OrderDetail AS OBJECT(
  Line NUMBER,
  Description VARCHAR2( 35),
  Qty NUMBER,
  Price NUMBER
);
/
CREATE OR REPLACE TYPE OrderDetailTable AS TABLE OF OrderDetail;
/
CREATE OR REPLACE TYPE OrderDetails AS OBJECT(
  m_Details OrderDetailTable,
  MEMBER PROCEDURE clear,
  MEMBER FUNCTION addItem( SELF IN OUT NOCOPY OrderDetails
        , p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY OrderDetails AS
  MEMBER PROCEDURE clear IS
  BEGIN
    m_Details := OrderDetailTable();
  END;
  MEMBER FUNCTION addItem( SELF IN OUT NOCOPY OrderDetails
        , p_Line IN NUMBER, p_Description IN VARCHAR2
        , p_Qty IN NUMBER, p_Price IN NUMBER)
    RETURN NUMBER IS
    v_Size NUMBER;
  BEGIN
    SELF.m_Details.EXTEND; -- Fixes Error here
    v_Size := m_Details.LAST;
    RETURN v_Size;
  END;
END;
/

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question