Solved

Cannot EXTEND Nested Table Collection in OBJECT

Posted on 2004-09-07
6
952 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now