Varrays error

[babi_geek]

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production.

While trying to create a table using varrays it is showing errormessage for which I have given details below.


Create or replace type xx_va as varrays(5) of varchar2(25))

Type created

create table xx (name varchar2(25),
tools xx_va )


ORA-00902: invalid datatype

Could someone suggest where the mistake is ?

[debasisdas]

Try using this .

Create or replace type xx_va as varray(5) of varchar2(25);

create table xx (name varchar2(25),tools xx_va ) ;

Use Varray(5) not Varrays(5)

[babi_geek]

Then why without showing error , it has given message type created?

select * from user_types
where type_name ='XX_VA'

not displaying type details. How to see type details or type definition?

[susarlasireesha]

use this steps
CREATE or replace TYPE ttt AS OBJECT (xx_va varchar2(25));

CREATE or replace TYPE xx_va AS VARRAY(5) OF ttt;

create table xx (name varchar(25),
tools xx_va)

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production.

While trying to create a table using varrays it is showing errormessage for which I have given details below.


Create or replace type xx_va as varrays(5) of varchar2(25))

Type created

create table xx (name varchar2(25),
tools xx_va )


ORA-00902: invalid datatype

Could someone suggest where the mistake is ?

[babi_geek]

select * from user_types
where type_name ='XX_VA'

not displaying type details. How to see type details or type definition?

[debasisdas]

Then why without showing error , it has given message type created?

If your syntax is wrong , oracle compiler will never say that is correct.

select * from user_types
where type_name ='XX_VA'

not displaying type details. How to see type details or type definition?

That is because your type was never compiled successfully because of the syntax error in your code .

[susarlasireesha]

select * from user_types
where type_name ='XX_VA'

not displaying type details. How to see type details or type definition?

use this
select * from user_source where type='TYPE' and name='XX_VA'

[babi_geek]

I insert a record
insert into xx values('babi', xx_va('Ax','Screw Driver'));

Then I have given the following command
select * from xx

Output is

babi xx_vA()


Can we see the complete details of XX_VA() without writing a Pl/Sql Code?

[krishnaindia2007]

I insert a record
insert into xx values('babi', xx_va('Ax','Screw Driver'));

Then I have given the following command
select * from xx

Output is

babi xx_vA()


Can we see the complete details of XX_VA() without writing a Pl/Sql Code?

You may also use USER_COLL_TYPES instead of user_source.

I think in Varrays we need to querying data Via Pl/Sql instead of SQL Extensions.

To access data using SQL use nested tables instead of varrays.