Home » SQL & PL/SQL » SQL & PL/SQL » getting table of ref while inserting data
getting table of ref while inserting data [message #184132] Tue, 25 July 2006 08:35 Go to next message
björn
Messages: 1
Registered: July 2006
Junior Member
Hi,
my problem is to get a table of reference by a select-statement to insert it into another table.
I've got a datatype 'Surface_type' and a table
Surface_tab (Surface Surface_type, id VARCHAR2(40), parent_id VARCHAR2(40), CONSTRAINT Surface_pk PRIMARY KEY(id));
Now, some surfaces (those with same parent_id) belong to one CompositeSurface. I created it by:
CREATE TYPE CompositeSurface_type AS TABLE OF REF Surface_type;
The table for my CompositeSurfaces looks like this:
CREATE TABLE CompositeSurface_tab (CompositeSurface CompositeSurface_type, id VARCHAR2(40), parent_id VARCHAR2(40), CONSTRAINT CompositeSurface_pk PRIMARY KEY(id)) NESTED TABLE CompositeSurface STORE AS CompositeSurface_ntable;
Now I tried to insert a CompositeSurface x to this table by asking, which surfaces have the same parent_id as the id of my CompositeSurface x. This looks like this:
INSERT INTO CompositeSurface_tab VALUES (
CAST(MULTISET( SELECT Surface FROM SURFACE_TAB
WHERE parent_id = 'abcd') AS CompositeSurface_type)
, 'id_of_x', 'parent_id_of_x');
The error I'm getting is ORA-00932 (incosistent datatypes). That's not a big surprise, because I get Surface_type and need to get REF Surface_Type.
But how do I get just the references of my surfaces, sothat i just need one copy of them in my database?

Hope somebody can help me.
Thanks.
Björn.
Re: getting table of ref while inserting data [message #184336 is a reply to message #184132] Wed, 26 July 2006 03:49 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
As I understand it, REF must be able to uniquely reference an object in the database, so it can be used only in the context of an object-based table/view, not an object appearing in a column of a table/view. In your example, the surface types referenced by CompositeSurface_type must be tuples of a table of type surface_type.

With some redesign, here's how your case would work.

SQL> CREATE TYPE surface_type AS OBJECT
  2  (name     VARCHAR2(30),
  3   id       VARCHAR2(40), 
  4   parent_id VARCHAR2(40));
  5  /

Type created.

SQL> CREATE TABLE surface_tab OF surface_type (id PRIMARY KEY);

Table created.

SQL> CREATE TYPE CompositeSurface_type AS TABLE OF REF surface_type;
  2  /

Type created.

SQL> CREATE TABLE CompositeSurface_tab 
  2  (CompositeSurface CompositeSurface_type) 
  3  NESTED TABLE CompositeSurface STORE AS CompositeSurface_ntable;

Table created.

SQL> INSERT INTO surface_tab VALUES (surface_type('STA', 'id-1', 'abcd'));

1 row created.

SQL> INSERT INTO surface_tab VALUES (surface_type('STB', 'id-2', 'efgh'));

1 row created.

SQL> INSERT INTO surface_tab VALUES (surface_type('STC', 'id-3', 'abcd'));

1 row created.

  1  INSERT INTO CompositeSurface_tab
  2  VALUES (
  3     CAST(MULTISET(SELECT REF(s) FROM SURFACE_TAB s
  4                  WHERE s.parent_id = 'abcd')
  5                  AS CompositeSurface_type)
  6*        )
SQL> /

1 row created.

SQL> INSERT INTO CompositeSurface_tab
  2  VALUES (
  3     CAST(MULTISET(SELECT REF(s) FROM surface_tab s
  4                  WHERE s.parent_id = 'abcd')
  5                  AS CompositeSurface_type)
  6         );

1 row created.

SQL> SELECT * FROM CompositeSurface_tab;

COMPOSITESURFACE
--------------------------------------------------------------------------------
COMPOSITESURFACE_TYPE(00002202081977F199B38110FBE04013AC0A3F0FA51976D69E4DB5C9A5
E04013AC0A3F42C2, 00002202081977F199B38310FBE04013AC0A3F0FA51976D69E4DB5C9A5E040
13AC0A3F42C2)


SQL> SELECT c.COLUMN_VALUE.id,
  2         c.COLUMN_VALUE.parent_id,
  3         c.COLUMN_VALUE.name
  4  FROM TABLE
  5       (SELECT  compositesurface
  6        FROM    CompositeSurface_tab) c;

COLUMN_VALUE.ID                          COLUMN_VALUE.PARENT_ID                   COLUMN_VALUE.NAME
---------------------------------------- ---------------------------------------- -----------------
id-1                                     abcd                                     STA
id-3                                     abcd                                     STC

Previous Topic: Query is extremely slow (URGENT HELP NEEDED)
Next Topic: how to get server name
Goto Forum:
  


Current Time: Fri Dec 09 00:25:01 CST 2016

Total time taken to generate the page: 0.12057 seconds