inserting into table - object view

[femina]

in object views
i have a table customer
CUSTID NOT NULL NUMBER
NAME VARCHAR2(25)
STREET VARCHAR2(25)
CITY VARCHAR2(25)
STATE VARCHAR2(25)
PIN NUMBER
then i had created ADT based on cust
[code=oracle]
create or replace type ADDRESS_TY as object (street varchar2(25), city varc har2(25), state varchar2(25), pin number);
[/code]
[code=oracle]
create or replace type PERSON_TY as object(name varchar2(25),ad dress ADDRSS_TY);
[/code]

NOW CAN I INSERT INTO CUSTOMER TABLE WITH CONSTUCTOR METHODS OR CAN I INSERT ONLY THORUGH NORMAL WAY OF INSERTION IN RELATIONAL TABLE

[amitpatel66]

in object views
i have a table customer
CUSTID NOT NULL NUMBER
NAME VARCHAR2(25)
STREET VARCHAR2(25)
CITY VARCHAR2(25)
STATE VARCHAR2(25)
PIN NUMBER
then i had created ADT based on cust

create or replace type ADDRESS_TY as object (street varchar2(25), city varc har2(25), state varchar2(25), pin number);
create or replace type PERSON_TY as object(name varchar2(25),ad dress ADDRSS_TY);

NOW CAN I INSERT INTO CUSTOMER TABLE WITH CONSTUCTOR METHODS OR CAN I INSERT ONLY THORUGH NORMAL WAY OF INSERTION IN RELATIONAL TABLE

I dont find you making use of objects in your table customer as per your table structure above. So you can insert using a simple insert statement, you need not use any constructor here.

[amitpatel66]

Hi Femina,

Please make sure you make use of CODE tags every time when you post and source code in the forum.

Thanks
MODERATOR

[femina]

SIR
LET ME ADD ONE MORE PONI TO THIS AND MAKE THE QUESTION CLEARER
I HAVE AN OBJECT VIEW ON CUSTOMER TABLE

[code=oracle]

create view CUST_OV(custid, Person) as select custid, PERSONN_TY(name , ADDRSS_TY (street, city,state,pin) ) from cust;
[/code]

(CUSTOMER TABLE) NOW PLEASE LET ME KNOW THE ANSWER

I dont find you making use of objects in your table customer as per your table structure above. So you can insert using a simple insert statement, you need not use any constructor here.

[femina]

HOW TO ADD CODE TAGS
is it like this
sorry since iam a new use please bear with me and clear my doubts

create object view CUST_OV(custid,Person) as select custid, PERSONN_TY(name, ADDRSS_TY (street, city,state,pin)) from cust;

[amitpatel66]

HOW TO ADD CODE TAGS
is it like this
sorry since iam a new use please bear with me and clear my doubts

create object view CUST_OV(custid,Person) as select custid, PERSONN_TY(name, ADDRSS_TY (street, city,state,pin)) from cust;

Please enclose your posted code in [code] tags (See How to Ask a Question).


MODERATOR

[debasisdas]

What you want to do in the view ?

[amitpatel66]

Check the below steps would answer your question:

[code=oracle]

SQL> ed
Wrote file afiedt.buf

1* CREATE OR REPLACE TYPE ADDRESS_TY AS object (street VARCHAR2(25), city varchar2(25), state VARCHAR2(25), pin NUMBER);
SQL> /

Type created.

SQL> create table cust(custid number,street varchar2(20),ci ty varchar2(10),st ate varchar2(10),pi n number);

Table created.

SQL> ed
Wrote file afiedt.buf

1* CREATE VIEW CUST_OV(custid, Address) AS SELECT custid, ADDRESS_TY (street, city,state,pin) FROM cust
SQL> /

View created.

-- Nos performing an insert in cust table and cust_ov view in both the ways, ie using a constructor and then using a simple insert:

SQL> insert into cust values(1,'stree t1','Chennai',' TN',600706);

1 row created.

-- the below using an object constructor does not work for cust table:

SQL> insert into cust values(2, ADDRESS_TY('str eet2','Chennai' ,'TN','20001')) ;
insert into cust values(2, ADDRESS_TY('str eet2','Chennai' ,'TN','20001'))
*
ERROR at line 1:
ORA-00947: not enough values

SQL> insert into cust_ov values(2, ADDRESS_TY('str eet2','Chennai' ,'TN','20001')) ;

1 row created.

SQL> commit;

SQL> select * from cust;

CUSTID STREET CITY STATE PIN
--------- -------------------- ---------- ---------- ---------
1 street1 Chennai TN 600706
2 street2 Chennai TN 20001


SQL> select * from cust_ov;

CUSTID
---------
ADDRESS(STREET, CITY, STATE, PIN)
----------------------------------------------------------------------------------------------------
1
ADDRESS_TY('str eet1', 'Chennai', 'TN', 600706)

2
ADDRESS_TY('str eet2', 'Chennai', 'TN', 20001)

SQL> insert into cust_ov values(3,'stree t3','chennai',' TN','3242343');
insert into cust_ov values(3,'stree t3','chennai',' TN','3242343')
*
ERROR at line 1:
ORA-00913: too many values

[/code]

I hope this helps!!

[femina]

Thanks A Lot For Your Answer Sir

[amitpatel66]

Thanks A Lot For Your Answer Sir

You are welcome :)

MODERATOR