Creating a VARRAY column type within a table [message #3293] |
Wed, 18 September 2002 03:25 |
Mark Grimshaw
Messages: 73 Registered: June 2002
|
Member |
|
|
I currently have a number of tables with columns whose types are of a user defined nature. For example I might have a type defined as TYPE MyArrayType AS VARRAY(10) OF VARCHAR2(20). This works fine. However, we are experimenting with Visual Basic trying to get records from a table and are having problems with VARRAY fields. Someone suggested whether instead of specifying a type for a VARRAY column type we could just define the table along the line sof:-
CREATE TABLE MyTable
(
name VARCHAR2(30),
address VARRAY(4) OF VARCHAR2(30),
...
)
I am having problems doing this and have tried various permutations. Can I in fact do it this way or do I have to define a type beforehand.
TYIA
Mark Grimshaw
|
|
|
Re: Creating a VARRAY column type within a table [message #3312 is a reply to message #3293] |
Wed, 18 September 2002 23:00 |
Rajeev Dhingra
Messages: 5 Registered: June 2002
|
Junior Member |
|
|
In SQL*Plus, you have to define object type Project, as follows:
SQL> CREATE TYPE Project AS OBJECT (
2 project_no NUMBER(2),
3 title VARCHAR2(35),
4 cost NUMBER(7,2));
Next, you define VARRAY type ProjectList, which stores Project objects:
SQL> CREATE TYPE ProjectList AS VARRAY(50) OF Project;
Finally, you create relational table department, which has a column of type ProjectList, as follows:
SQL> CREATE TABLE department (
2 dept_id NUMBER(2),
3 name VARCHAR2(15),
4 budget NUMBER(11,2),
5 projects ProjectList);
This procedure you need to follow to create a table with a VARRAY variable.
|
|
|