Re: Two challenges with the SQL*Loader tool [message #71425] |
Thu, 31 October 2002 07:17 |
Mark Grimshaw
Messages: 73 Registered: June 2002
|
Member |
|
|
Mahesh,
The only problem is one of a time issue - I have about 50 VARRAY types that are of the same format in my database. Do you agree that my original problem with the loading of data where my column is of a VARRAY type cannot actually be solved - I haven't tried the 'Ask Tom' site yet?
Ok, lets say that I worked some overtime and redefined my columns as VARRAYS of objects like you mention. What are the implications of this. More specifically could you give me an example of the following saving me much time in wading through documentation : -
1) An example of a View that selects and "flattens" a field from a table that is defined as a VARRAY of objects like you outlined earlier.
2) An SQL INSERT statement that adds a record to such a table.
Thank You for your responses.
Mark
|
|
|
Re: Two challenges with the SQL*Loader tool [message #71428 is a reply to message #71425] |
Thu, 31 October 2002 08:55 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
ofcourse, there are lot of PAIN with varrays.
restriction are there (u cant use dml etc)..
lookinto docs
SQL> create type myarray is VARRAY(5) of varchar2(10);
2 /
Type created.
SQL> create table mytable (c1 number, c2 myarray);
Table created.
SQL> insert into mytable values (1, myarray('magvivek','haddock','pgwoodhous')) ;
1 row created.
SQL> column c2 format a50
SQL> select * from mytable;
C1 C2
---------- --------------------------------------------------
1 MYARRAY('magvivek', 'haddock', 'pgwoodhous')
SQL> create type mytype_table is table of varchar2(10);
2 /
Type created.
SQL> ed
Wrote file afiedt.buf
1 select column_value
2* from THE (select CAST(c2 AS mytype_table) from mytable)
3 /
COLUMN_VAL
----------
magvivek
haddock
pgwoodhous
SQL> insert into mytable values (2, myarray('ss','aa','xx')) ;
1 row created.
SQL> commit;
Commit complete.
SQL> ed
Wrote file afiedt.buf
1 select column_value
2* from THE (select CAST(c2 AS mytype_table) from mytable)
SQL> /
from THE (select CAST(c2 AS mytype_table) from mytable)
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL> ed
Wrote file afiedt.buf
1 select column_value
2* from THE (select CAST(c2 AS mytype_table) from mytable where c1=2)
SQL> /
COLUMN_VAL
----------
ss
aa
xx
|
|
|