Create view in SQL with object identifier

Question

I have the following types and tables:

CREATE TYPE person AS OBJECT (
dni VARCHAR2(10),
name VARCHAR2(30),
surname VARCHAR2(100)
)NOT FINAL;

CREATE TYPE runner UNDER person (
runningClub VARCHAR2(100)
);

CREATE TYPE race AS OBJECT (
name VARCHAR2(50),
city VARCHAR2(50),
distance INTEGER
)NOT FINAL;

CREATE TYPE participation UNDER race(
    runner_id runner,
    year VARCHAR(4),
    time INTEGER
);

CREATE TABLE participations OF participation;

And now I need to create a VIEW and I tried this:

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (runner_id, year, time) AS
SELECT runner_id, time FROM PARTICIPATIONS WHERE DISTANCE = '10';

But the error I get is: Error SQL: ORA-01730: invalid number of column names specified

Anybody knows why? It's my first time with object-relational SQL and this is struggling me a lot. Thanks!

This seems like an odd model, even for an object demo. Each participation object repeats all the common fields from the race. Shouldn't participation have race and runner attributes - possibly as refs to tables of those; and shouldn't year (or the full race date) be part of the race definition? — Alex Poole
– Alex Poole, Commented Oct 3, 2016 at 16:04

Alex Poole · Accepted Answer · 2016-10-03 16:21:27Z

2

Your query needs to get a column value corresponding to each field in the object type you're using, including those from the parent type:

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (name, city, distance) AS
SELECT name, city, distance, runner_id, year, time
FROM PARTICIPATIONS WHERE DISTANCE = '10';

View AVGTIME10K created.

However, from the documentation:

Use the WITH OBJECT IDENTIFIER clause to specify a top-level (root) object view. This clause lets you specify the attributes of the object type that will be used as a key to identify each row in the object view. In most cases these attributes correspond to the primary key columns of the base table. You must ensure that the attribute list is unique and identifies exactly one row in the view.

The name, city, distance I've used doesn't uniquely identify an object, and all runners from a race will get the same object_id pseudocolumn value. You can add year, time to that, which will help unless two people get exactly the same time (which happens, of course); but you can't add runner_id - you'd get ORA-22971: invalid datatype for PRIMARY KEY-based object identifier.

edited Oct 3, 2016 at 16:21

answered Oct 3, 2016 at 16:01

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joanmacat Over a year ago

Thanks for your help I understand more things now. Thank you for your response.

Joanmacat Over a year ago

Then... how can I show the DNI, NAME and SURNAME from PERSON in the VIEW? I mean using REF()?

Alex Poole Over a year ago

Why do you want an object view? What are you really trying to achieve?

Joanmacat Over a year ago

Is an exercice for college, Database Architecture subject. We have the tables RUNNER, RACE and PARTICIPATION and we need to pass them to OBJECT TYPES. After that make a VIEW to show the DNI of the runner and the TIME which the race distance is 10.

Ignacio · Accepted Answer · 2016-10-15 20:36:33Z

Object views are used when you want to access object information trough a relational table.

I think that race and runner should have their own object type and object table, but participation must be a relational table. For example:

CREATE TYPE race_t AS OBJECT (
 ra_name VARCHAR(30),
 ....
);

CREATE TABLE race_o of race_t (ra_name PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE runner_t AS OBJECT (
ru_dni VARCHAR(10),
...
);

CREATE TABLE runner_o of runner_t (ru_dni PRIMARY KEY)
OBJECT IDENTIFIER IS PRIMARY KEY;

Also you must create a view for people that has made distance of 10. Km, miles?

You will need a relational table to use the object view. Also you will need a type for the view with the same atributes as your view

This will be the relational table

CREATE TABLE PARTICIPACIONS (
--You must use a REF with SCOPE for race, and a REF with SCOPE for runner
...
);

Object view to fill the view

CREATE TYPE AvgTime AS Object (
avg_runner_id VARCHAR(30),
avg_time      INTEGER
);

Object view for distance=10. Please note that you will need the same atributes and order in your select as in your object type, but not in the object identifier

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (avg_runner_id) AS
SELECT p.runner_id, p. time FROM PARTICIPATIONS P WHERE DISTANCE = '10';
)

Remember, you use the view to see objects trough a relational table.

MikkaRin · Accepted Answer · 2016-10-03 15:43:02Z

0

Hi Please match column it's simple

CREATE VIEW AvgTime10k OF PARTICIPATION WITH OBJECT IDENTIFIER (runner_id, year, time) AS
SELECT runner_id, year, time FROM PARTICIPATIONS WHERE DISTANCE = '10';

edited Oct 3, 2016 at 15:43

MikkaRin

3,08422 silver badges35 bronze badges

answered Oct 3, 2016 at 15:35

Ritesh Patel

3153 silver badges10 bronze badges

1 Comment

Alex Poole Over a year ago

That gets the same ORA-01730 error. participation is a subtype of race (though not sure it should be), so all the fields for the parent and child type have to be included for the object to be constructed.

Collectives™ on Stack Overflow

Create view in SQL with object identifier

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related