Solved

Cannot EXTEND Nested Table Collection in OBJECT

Posted on 2004-09-07
6
957 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
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
Technology Partners: 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

713 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