CODEWITHSUNDEEP
sqloracle-database
Ralph Blackk
Ralph Blackk

Reputation: 51

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

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?

Upvotes: 0

Views: 22522

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

From the documentation:

The procedure for defining an object view is:

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

  2. 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.

Upvotes: 5

Related Questions