Hi! Into my database (its part below), i'm trying to insert values.

CREATE TYPE t_vphonenos AS VARRAY(3) OF VARCHAR2(15);

CREATE OR REPLACE TYPE t_customer AS OBJECT (
id number(4),
name_1 varchar2(20),
name_2 varchar2(20),
surname varchar2(20),
nip varchar2(13),
regon varchar2(15),
pesel varchar2(11),
phone_nos t_vphonenos
);
/

CREATE TABLE tab_customers OF t_customer (
id PRIMARY KEY
)
OBJECT ID PRIMARY KEY
/

this works fine:

INSERT INTO tab_customers VALUES(t_customer
(1, 'Grzegorz', 'Adam', 'Jarzebski', '567nip', '678regon', '568pesel',
t_adress('Osinska', '23', '24', 'Tuwima', '32-424', 'Towowka'),
t_vphonenos('+48 565 565 565')));

but now i'd like to insert ("add") some data into t_vphonenos (i've
inserted one row already ). i've tried something like this, but it
didn't work:

INSERT INTO tab_customers (phone_nos) VALUES (
t_vphonenos (
('123123123'),
('345345345')
)
);
What i'm doing wrong? I've tried also:

INSERT INTO TABLE (SELECT phone_nos FROM tab_customers WHERE id = 1)
VALUES (t_vphonenos('123123','1231233'));

Still doesn't work. Someone would help me?

Thanks

I've also tried something like this. Now Oracle says
"Error at line 1:
ORA-00913: Too many values"

INSERT INTO TABLE (SELECT t.phone_nos FROM tab_customers t WHERE
t.id = 1) VALUES ('12ABC','23CDE','34EFG');

.
--
regards

Marcin 'dethar' Jurasz wrote:

> Hi! Into my database (its part below), i'm trying to insert values.
>
> CREATE TYPE t_vphonenos AS VARRAY(3) OF VARCHAR2(15);
>
> CREATE OR REPLACE TYPE t_customer AS OBJECT (
> id number(4),
> name_1 varchar2(20),
> name_2 varchar2(20),
> surname varchar2(20),
> nip varchar2(13),
> regon varchar2(15),
> pesel varchar2(11),
> phone_nos t_vphonenos
> );
> /
>
> CREATE TABLE tab_customers OF t_customer (
> id PRIMARY KEY
> )
> OBJECT ID PRIMARY KEY
> /
>
> this works fine:
>
> INSERT INTO tab_customers VALUES(t_customer
> (1, 'Grzegorz', 'Adam', 'Jarzebski', '567nip', '678regon', '568pesel',
> t_adress('Osinska', '23', '24', 'Tuwima', '32-424', 'Towowka'),
> t_vphonenos('+48 565 565 565')));
>
> but now i'd like to insert ("add") some data into t_vphonenos (i've
> inserted one row already ). i've tried something like this, but it
> didn't work:
>
> INSERT INTO tab_customers (phone_nos) VALUES (
> t_vphonenos (
> ('123123123'),
> ('345345345')
> )
> );
> What i'm doing wrong? I've tried also:
>
> INSERT INTO TABLE (SELECT phone_nos FROM tab_customers WHERE id = 1)
> VALUES (t_vphonenos('123123','1231233'));
>
> Still doesn't work. Someone would help me?
>
> Thanks

Examples of how to do this can be found at:
http://www.psoug.org
click on Morgan's Library
click on nested tables
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

On 2005-04-28, Marcin 'dethar' Jurasz <dethar@gmail.com> wrote:
> Hi! Into my database (its part below), i'm trying to insert values.
>
> CREATE TYPE t_vphonenos AS VARRAY(3) OF VARCHAR2(15);
>
> CREATE OR REPLACE TYPE t_customer AS OBJECT (
> id number(4),
> name_1 varchar2(20),
> name_2 varchar2(20),
> surname varchar2(20),
> nip varchar2(13),
> regon varchar2(15),
> pesel varchar2(11),
> phone_nos t_vphonenos
> );
> /
>
> CREATE TABLE tab_customers OF t_customer (
> id PRIMARY KEY
> )
> OBJECT ID PRIMARY KEY
> /
>
> this works fine:
>
> INSERT INTO tab_customers VALUES(t_customer
> (1, 'Grzegorz', 'Adam', 'Jarzebski', '567nip', '678regon', '568pesel',
> t_adress('Osinska', '23', '24', 'Tuwima', '32-424', 'Towowka'),
> t_vphonenos('+48 565 565 565')));
>
> but now i'd like to insert ("add") some data into t_vphonenos (i've
> inserted one row already ). i've tried something like this, but it
> didn't work:
>
> INSERT INTO tab_customers (phone_nos) VALUES (
> t_vphonenos (
> ('123123123'),
> ('345345345')
> )
> );
> What i'm doing wrong? I've tried also:
>
> INSERT INTO TABLE (SELECT phone_nos FROM tab_customers WHERE id = 1)
> VALUES (t_vphonenos('123123','1231233'));
>
> Still doesn't work. Someone would help me?

INSERT INTO tab_customers (id, phone_nos) VALUES (
1, // <=============================================
t_vphonenos (
('123123123'),
('345345345')
)
);


hth,
Rene

--
Rene Nyffenegger
http://www.adp-gmbh.ch/

Fri, 29 Apr 2005 07:35:30 +0000 (UTC), Rene Nyffenegger wrote:

> INSERT INTO tab_customers (id, phone_nos) VALUES (
> 1, // <=============================================
> t_vphonenos (
> ('123123123'),
> ('345345345')
> )
> );

Unique constraint violated :P. . I tried many combinations, also
modified from psoug.org, but... still got problem. Thanks anyway .
--
regards

UPDATE tab_customers SET phone_nos = t_vphonenos('1212','12123')
WHERE id = 5;

it works, but i'd like to do it by insert:

before insert | after insert
----------------------------
t_vphonenos | t_vphonenos
----------------------------
'1212' | '1212', '13143'

NESTED TABLE is (i think) not the same as VARRAY, because i already
did an insert into NESTED. Into VARRAY - by now - i can only update.

An example from psoug.org after I've modified, it seems I've doing ok
with my clause. But it still doesn't work. Oracle 8i. It could be a
problem?
--
regards

VARRAYs are "arrays" not "tables", as their name implies. INSERT
statements work on tables, not on arrays.

Neither VARRAYs nor nested tables are useful datatypes for creating
tables IMHO - what's wrong with creating a separate table for the phone
numbers?

Marcin 'dethar' Jurasz wrote:
> UPDATE tab_customers SET phone_nos = t_vphonenos('1212','12123')
> WHERE id = 5;
>
> it works, but i'd like to do it by insert:
>
> before insert | after insert
> ----------------------------
> t_vphonenos | t_vphonenos
> ----------------------------
> '1212' | '1212', '13143'
>
> NESTED TABLE is (i think) not the same as VARRAY, because i already
> did an insert into NESTED. Into VARRAY - by now - i can only update.
>
> An example from psoug.org after I've modified, it seems I've doing ok
> with my clause. But it still doesn't work. Oracle 8i. It could be a
> problem?
> --
> regards

29 Apr 2005 02:19:53 -0700, Tony Andrews wrote:

> Neither VARRAYs nor nested tables are useful datatypes for creating
> tables IMHO - what's wrong with creating a separate table for the phone
> numbers?

It's just my Professor's opinion - I must do it as He said. It is
a object-related database - I can't change principles .
--
regards

Marcin 'dethar' Jurasz wrote:
> It's just my Professor's opinion - I must do it as He said. It is
> a object-related database - I can't change principles .

Yes, that's the only place anyone ever uses these - in feature training
courses! They are practically useless in the real world.

Marcin 'dethar' Jurasz wrote:

> 29 Apr 2005 02:19:53 -0700, Tony Andrews wrote:
>
>
>>Neither VARRAYs nor nested tables are useful datatypes for creating
>>tables IMHO - what's wrong with creating a separate table for the phone
>>numbers?
>
>
> It's just my Professor's opinion - I must do it as He said. It is
> a object-related database - I can't change principles .

Post the DDL and repost your DML.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)