Home » SQL & PL/SQL » SQL & PL/SQL » Create tabled of type Varray (Oracle 11G R2, Win 7)
Create tabled of type Varray [message #580786] Thu, 28 March 2013 04:02 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

I have to move the data from a Varray column to a table.

Lets say I have created a Varray as described below,

create or replace TYPE "BT_TYPE"    AS OBJECT (
R_ID    NUMBER,
P_EVENT    VARCHAR2(100))
/

create or replace TYPE "BT_VR"  AS varray(20) of  BT_TYPE
/


And I have a used this Varray as the column datatype in table

Create table BT_MASTER(
BT_ID_SEQ	NUMBER(5),
BT_DETAIL	BT_VR);


And this table contains data.

I want to move the data in the VARRAY column BT_DETAIL to another table. I have create a staging table BT_STG which contains a surrogate key column and the columns from the VARRAY. I am creating this staging table at run time.

Create Table BT_STG(
BT_STG_ID     NUMBER(5),
R_ID    NUMBER(5),
P_EVENT    VARCHAR2(100)
);


One way to create this staging table is to query the data dictionary views to get the VARRAY object's columns, datatyeps and create it.

Is there any other simpler way by which I could create a table similar to a VARRAY/Object?

Something similar to,

create table test as select * from  BT_VR
Re: Create tabled of type Varray [message #580788 is a reply to message #580786] Thu, 28 March 2013 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT use collections in stored table.
It is bad to write code.
It is bad to find bugs in this code.
It is bad to maintain.
It is bad for performances.
It is out of the relational model.
There is NO profit to use them.

Regards
Michel
Re: Create tabled of type Varray [message #580791 is a reply to message #580788] Thu, 28 March 2013 04:46 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Michel, I agree with you.

The usecase I am trying to address here is, I have a live application which stores collection in stored tables.

I have to migrate this table to SQL server. As SQL server 2008 doesnt support VARRAYs, I am planning to move all the coleciton data to seperate table and then migrate the tables to SQL server.

I am trying to move against the approach of using collections in stored table Smile

Let me know if you have a better idea of implementing this migration.

--Gokul
Re: Create tabled of type Varray [message #580795 is a reply to message #580791] Thu, 28 March 2013 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
insert into BT_STG 
select BT_ID_SEQ,B.R_ID,B.P_EVENT from BT_MASTER, table(BT_MASTER.BT_DETAIL) b;


Regards
Michel
Re: Create tabled of type Varray [message #580877 is a reply to message #580795] Fri, 29 March 2013 01:56 Go to previous message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Thanks Michel!!! Your suggestion was very much helpful.
Previous Topic: Parsing BLOB Contents
Next Topic: Migration
Goto Forum:
  


Current Time: Tue Sep 23 17:50:14 CDT 2014

Total time taken to generate the page: 0.09379 seconds