Home » SQL & PL/SQL » SQL & PL/SQL » Loading data into a VARRAY
Loading data into a VARRAY [message #2114] Fri, 21 June 2002 05:03 Go to next message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
hello,

I am having difficulty with the syntax for loading into a VARRAY column type of a table whose element type is of a NUMBER. The length of the VARRAY is 5.

Any help would be appreciated.
Re: Loading data into a VARRAY [message #2116 is a reply to message #2114] Fri, 21 June 2002 05:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is this helping you?
SQL> ed
Wrote file afiedt.buf

  1* CREATE OR REPLACE TYPE zipcode AS VARRAY(5) OF number(5)
SQL> /

Type created.
SQL> ed
Wrote file afiedt.buf

  1  create table employe
  2  (name varchar2(10),
  3* phone zipcode)
SQL> /

Table created.
SQL> ed
Wrote file afiedt.buf

  1  insert into employe values
  2* ('Cp.Haddock',zipcode(03079,02212))
SQL> /

1 row created.

SQL> select * from employe;

NAME       PHONE
---------- ---------------------------------------
Cp.Haddock ZIPCODE(3079, 2212)
Re: Loading data into a VARRAY [message #2135 is a reply to message #2116] Mon, 24 June 2002 01:37 Go to previous messageGo to next message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
Thanks Mahesh,

Maybe I should go into even more detail with my question. My data is in the following form and I don't have any control over its format :-(

To take your example table but with the VARRAY in the middle of two fields.
Input file format: -

cp.haddock 111 222 333 555 66 anothertextfield
whoami 3 5 4 0 0 anothertextfield

As you can see the fields are separated by a space but the lengths of each field and each record in the data file are fixed which is helpful. However, after much experimentation I can't seem to get the data to load correctly.

TYIA

Mark
Re: Loading data into a VARRAY [message #2138 is a reply to message #2116] Mon, 24 June 2002 02:45 Go to previous message
Mark Grimshaw
Messages: 73
Registered: June 2002
Member
Thanks Mahesh,
Maybe I should go into even more detail with my question. My data is in the following form and I don't have any control over its format :-(

To take your example table but with the VARRAY in the middle of two fields.
Input file format: -

cp.haddock 111 222 333 555 66 anothertextfield
whoami 3 5 4 0 0 anothertextfield

As you can see the fields are separated by a space but the lengths of each field and each record in the data file are fixed which is helpful. However, after much experimentation I can't seem to get the data to load correctly.

TYIA

Mark
Previous Topic: Help: Why ORA-01037: maximum cursor memory exceeded occured?
Next Topic: comparing triggers and procedures
Goto Forum:
  


Current Time: Fri Mar 29 09:08:58 CDT 2024