error: ORA-01730: invalid number of column names specified

Question

Please help. I want to create an object view from the ffl tables but i keep getting the above error and can't find any solution.

create table COPY_BOOK (
  NUM number(4,0), 
  DATE_Purchase date, 
  PRICE number(5,2), 
  LOAN_code varchar2(20) ,
  STATUS varchar2(15) check (STATUS in ('GOOD','DAMAGED')), 
  CONSTRAINT CP_PK primary key (num) ,
  constraint Loan_code_D  check (LOAN_CODE in ('NO', 'LOAN')) 
);

create or replace type copy_book_t as object(
  num   number(4, 0),
  loan_code   varchar2 (20)
);
/

create or replace view Vcopy_book of copy_book_t
with object oid (num)
as select cb.num, cb.date_purchase, cb.price, cb.loan_code, cb.status
from copy_book cb;
/

Is there a problem with the type definition?

Your object type has only two properties and you are trying to create a view of that object type with the select statement, which has more than two elements in the select list — Nick Krasnov, Jan 04 '14 at 09:57
You don't need the `;` **and** the `/` for DDL like that: http://stackoverflow.com/questions/1079949/sql-the-semicolon-or-the-slash/10207695#10207695 — , Jan 04 '14 at 10:29

Alex Poole · Accepted Answer · 2014-01-04T10:05:02.630

From the documentation:

The procedure for defining an object view is:

Define an object type, where each attribute of the type corresponds to an existing column in a relational table.

Write a query that specifies how to extract the data from the relational table. Specify the columns in the same order as the attributes in the object type.

You have created your object type with two attributes, but you're trying to populate it with five columns from your relational table.

You either need to change your object type to have the same five attributes (demo), or only select the num and loan_code columns in the view (demo).

Incidentally, the documentation also recommends using with object identifier rather than with object oid.

error: ORA-01730: invalid number of column names specified

1 Answers1