Setting the primary key of a object type table in Oracle

Question

I have two Oracle questions.

1. How can I set the primary key of a table when the table is made up of an object type? e.g.

   CREATE TABLE object_names OF object_type

2. I have created a Varray type,

   CREATE TYPE MULTI_TAG AS VARRAY(10) OF VARCHAR(10);

   but when I try to do

   SELECT p.tags.count FROM pg_photos p;

   I get an invalid identifier error on the "count" part. p.tags is a MULTI_TAG, how can I get the number of elements in the MULTI_TAG?

Answer 1

Hi Rek,

First of all I wouldn't recommend storing data in Object tables. Objects are a great programmatic tool but querying Object tables leads to complicated SQL. I would advise storing your data in a standard relationnal model and using the objects in your procedures.

Now to answer your questions:

1. A primary key should be immutable, so most of the time an Object type is inappropriate for a primary key. You should define a surrogate key to reference your object.

2. You will have to convert the varray into a table to be able to query it from SQL

For example:

SQL> CREATE TYPE MULTI_TAG AS VARRAY(10) OF VARCHAR(10);
  2  /
Type created

SQL> CREATE TABLE pg_photos (ID number, tags multi_tag);
Table created

SQL> INSERT INTO pg_photos VALUES (1, multi_tag('a','b','c'));
1 row inserted

SQL> INSERT INTO pg_photos VALUES (2, multi_tag('e','f','g'));
1 row inserted

SQL> SELECT p.id, COUNT(*)
  2    FROM pg_photos p
  3         CROSS JOIN TABLE(p.tags)
  4   GROUP BY p.id;

        ID   COUNT(*)
---------- ----------
         1          3
         2          3

Answer 2

1)
A primary key is a constraint, to add constrains on object tables check this link:
http://download-west.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjdes.htm#i452285

2)
The COUNT method can't be used in a SQL statement:
REF LINK IN COMMENTS

So in my case I had to do

SELECT p.pid AS pid, count(*) AS num_tags FROM pg_photos p, TABLE(p.tags) t2 GROUP BY p.pid;