Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cannot EXTEND Nested Table Collection in OBJECT

Posted on 2004-09-07
6
Medium Priority
?
962 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
Industry Leaders: 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 1500 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

618 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