Home » RDBMS Server » Server Utilities » Loading data via Sql Loader into Varray Tables
Loading data via Sql Loader into Varray Tables [message #472322] Thu, 19 August 2010 20:21 Go to next message
Pavan Pusuluri
Messages: 2
Registered: August 2010
Location: HYderabad India
Junior Member
Hi Every body


Is it Possible doing SQL LOAD into Varray table having two inner objects. If it is not posssible could you please throw some light on why it is not possible.

Please find the structure of the table and its types for your reference

CREATE OR REPLACE TYPE OB_TEST_INFO AS OBJECT (
AGE NUMBER ( 3 ),
NAME VARCHAR2 ( 14 )
);
/

CREATE OR REPLACE TYPE OB_TEST_INFO_VARRAY AS VARRAY( 400 ) OF OB_TEST_INFO ;
/

CREATE OR REPLACE TYPE OB_TEST_MAINTENANCE AS OBJECT (
BREAKOUT_TYPE NUMBER ( 1 ),
EXISTING_STRIPS_FLAG NUMBER ( 1 ),
OB_TEST_INFO OB_TEST_INFO_VARRAY
);
/

CREATE OR REPLACE TYPE OB_TEST_MAINT_VARRAY AS VARRAY( 4 ) of OB_TEST_MAINTENANCE ;
/

CREATE TABLE VA_TEST_MAINTENANCE
(
DOMAIN_ID NUMBER ( 9 ),
USER_ID NUMBER ( 10 ),
OB_TEST_MAINT_INFO OB_TEST_MAINT_VARRAY
)
Re: Loading data via Sql Loader into Varray Tables [message #472323 is a reply to message #472322] Thu, 19 August 2010 20:30 Go to previous messageGo to next message
BlackSwan
Messages: 22709
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

What advantage does TYPE have over basic datatypes?

Alternatively you could use EXTERNAL TABLE
Re: Loading data via Sql Loader into Varray Tables [message #472324 is a reply to message #472323] Thu, 19 August 2010 20:43 Go to previous messageGo to next message
Pavan Pusuluri
Messages: 2
Registered: August 2010
Location: HYderabad India
Junior Member
Hi

The application is designed with Varrays and nested objects.It is already running in UAT and i am afraid if we can do anything about the design.

Coming back to the issue we are trying to insert data into the Varray which is having two inner objects through SQl loader.What i would like to know hereby any means is it possible to insert the data via sql loader.

Oracle version i am using is 10.2.0.4 and the underlying OS is Windows
Re: Loading data via Sql Loader into Varray Tables [message #472329 is a reply to message #472324] Thu, 19 August 2010 23:43 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The following section of the 10g online documentation deals with how to load nested tables and varrays using SQL*Loader.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#i1007180

and this one deals with loading nested column objects.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#i1008543

[Updated on: Thu, 19 August 2010 23:45]

Report message to a moderator

Previous Topic: SQLLDR WHEN condition with IN CLAUSE
Next Topic: How to concatenate 2 fields
Goto Forum:
  


Current Time: Wed Aug 27 14:44:59 CDT 2014

Total time taken to generate the page: 0.08850 seconds