From: douglashawthorne@yahoo.com.au   
      
   "Vishal"  wrote in message   
   news:3fc78ae.0404241954.6e2a0fa7@posting.google.com...   
   > Hello,   
   >   
   > I have the following types/table:   
   >   
   > create or replace type point as object(   
   > x      number(3),   
   > y      number(3),   
   > z      number(3)   
   > );   
   > /   
   >   
   > create or replace type cube as object(   
   > id number(5),   
   > bottom_corner point,   
   > top_corner point,   
   > member function volume return number,   
   > member function surface return number,   
   > pragma restrict_references(volume, WNDS),   
   > pragma restrict_references(surface, WNDS)   
   > );   
   > /   
   >   
   > create table Cubes of cube(   
   >        id    primary key,   
   >        bottom_corner not null,   
   >        top_corner not null   
   > );   
   >   
   > > Why does the following line in a procedure i have written   
   > give me these errors:   
   >   
   > create or replace procedure cube_intersect(ident IN NUMBER) as   
   > this_cube Cubes%ROWTYPE;   
   > ...   
   > ...   
   >   
   > begin   
   >   select * into this_cube from Cubes where id = ident;   
   >   
   > ^ERROR   
   >   
   >   
   > 36/3     PL/SQL: SQL Statement ignored   
   > 36/23    PL/SQL: ORA-00947: not enough values   
   >   
   > Thanks,   
   > Vishal   
      
   Vishal,   
      
   The primary reason appears to be the nested objects in the definition of the   
   CUBE object.  If I create a procedure as follows, then I have no compilation   
   or execution errors on 10.1.0.2 on WinXP Pro:   
      
   CREATE OR REPLACE PROCEDURE   
      cube_intersect(   
         p_ident IN NUMBER   
      )   
   AS   
   -- this_cube Cubes%ROWTYPE;   
      TYPE cubes_type IS RECORD (   
         id NUMBER,   
         bottom_corner   
            point,   
         top_corner   
            point   
      );   
      l_this_cube cubes_type;   
      l_this_point points%ROWTYPE;   
   BEGIN   
     SELECT * INTO l_this_cube FROM cubes WHERE id = p_ident;   
     SELECT * INTO l_this_point FROM points WHERE ROWNUM = 1;   
   END;   
   /   
      
   Firstly, I created a RECORD that matched the structure of the CUBE object.   
   There was no problem with the included objects.   
      
   Secondly, I created a table (POINTS) with the POINT object type.  The   
   compiler had no problem with one level of object definition.   
      
   Douglas Hawthorne   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)