SQL & PL/SQL :: Move Data From Varray Column To A Table

Mar 28, 2013

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_SEQNUMBER(5),
BT_DETAILBT_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

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Move Data From One Partition To Other In A Table

Jul 22, 2010

I want to move data from one partition to another partition in a same table.

Ex: Table A has Partitions P1 and P2. I want to move data from P1 to P2.

Is this is possible?

View 3 Replies View Related

SQL & PL/SQL :: Using VArray For Known Column Names?

May 2, 2013

I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.

Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.

Here is my
declare
to_num number;
field_name varchar2(15);
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Table And VArray

Aug 23, 2011

i have a doubt in plsql table and varray.. is this two things data will write on disc or it will perform through ly a memory level? if its in memory level i need a document about that.

View 14 Replies View Related

Loading Data Via Sql Loader Into Varray Tables

Aug 19, 2010

Is it Possible doing SQL LOAD into Varray table having two inner objects.

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
);
/

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: Fill VARRAY Using Table Fields And While Loop?

Jun 13, 2011

I am using arrays on this procedure but would like to use a WHILE LOOP to fill the array. The first element uses a balance forward amount unlike the next 11 elements so I would think I have no other choice for the first element.

jde_account_balances is the table name that stores the amounts.

Here is what I currently have coded:

X INTEGER;
Y INTEGER;
Z INTEGER;

[code]...

View 7 Replies View Related

Server Utilities :: Loading Data Via SQL Loader Into VArray Tables

Aug 19, 2010

Is it Possible doing SQL LOAD into Varray table having two inner objects.

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 )

[Code].....

View 3 Replies View Related

Forms :: When User Change / Delete Any Record / Row In Forms Data Automatically Move To Other Table

Dec 25, 2011

when a user change or delete any record or row in forms data automatically move to other table because i want to compare old and new record.

View 8 Replies View Related

PL/SQL :: Uses And Advantages - Associative Array / Nested Table And Varray?

Jun 4, 2013

What is the real time uses for Associative array, nested table and varray ?

View 2 Replies View Related

Move Data From Physical StandBy To Data Warehouse?

Oct 1, 2012

We have this archicture:

OLPT DB --> OLPT DB (Physical Standby, active dataguard) --> Data warehouse DB

We only allowed to connect to OLPT DB (Physical Standby, active dataguard) from Data warehouse DB. If there is possibility to use some of Oracle "native" method of data extraction (replication) from OLPT DB (Physical Standby, active dataguard) to Data warehouse DB.

As far as I know we cannot create materialized view log in OLPT DB (Physical Standby, active dataguard) in order to do data replication, but maybe there is some others ways?

View 3 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

Move Data From Old Tables Into New?

Aug 19, 2010

I have to move data from old tables into new tables. I use a sqlplus script and execute multiple 'insert from select'. I thought the SQL statements are executed in sequence, one after the other. I run into this problem: Let's take this script for an example

insert into A (a,b,c,....
select a,b,c......
from X;
insert into B (a,b,....
select a,b,....
from A joined with Y;
commit;

After executing the script in our development DB

select count (*) from A
returns 200

When the script is executed in production, A table record count is 5

select count (*) from A joined with Y; (used in the second insert)

returns 200.

It seems that the two statements are executed at the same time and table A is not fully populated when the select in statement two is executed.I suspect this is due to parallel execution in production.

Will executing

ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PARALLEL QUERY ;

View 8 Replies View Related

SQL & PL/SQL :: How To Move Only Subset Of Data From One Database To Another

Mar 13, 2012

The requirement details are as below,

1) The database contains static as well as transactional data for telecom domain.

2) We have to move region-wise data from one database to another.

3) There are around 10 regions.

4) The region wise data extraction from source db is based on around 40 to 50 tables. Rest of the tables contains static data & it will not change.

5) The volume is around 1 million subscribers per region.

6) The migration is required because

a) The client is upgrading its base product which uses this database

b) There is a change in structure of static tables

c) Hardware upgrade

d) The client want to start with single region on new database & rest of the regions will be operated from old database.

7) Keep execution time to very minimum.

I am thinking to have solution as below,

1) Create destination database with upgraded db structure (as mentioned in 6b)

2) Create database links to access source db in destination db.

3) Write SQL queries to fetch data from all the respective tables for a specific region

4) Write separate PL/SQL blocks for each table to fetch data from source db & insert into respective table in destination db

a) Use FOR ALL & bulk collect to improve the performance

b) Divide table data into multiple chunks & execute parallel batches (around 10) to insert the data

5) Validate pre & post counts to verify the success of migration

View 6 Replies View Related

Windows :: Move Data From One DB To Other Without DBlink And Using Dot Net

Oct 30, 2012

I have database A and B , we are strictly restricted to not to create any DB links between these database. In database A based on complex logic we in a stored procedure we populate the data to table type..now dot net will take this table type data and insert the same in database B.

Question here :

1.When we try to bulk collect all the data into table type , i am getting error operand exceeds limit, but it is mandate to put all data in one short to table type as dot net will take that and insert into B database.
2.Is there any good logical method to achieve this in a simple way.

View 3 Replies View Related

PL/SQL :: How To Move Data Between Two Similar Tables

Jul 3, 2012

I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.

Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.

INSERT INTO TB_B
SELECT *, sysdate from TB_A;

View 9 Replies View Related

PL/SQL :: Move Partition From One Tablespace To Other Along With Lob Data?

Jan 4, 2013

I have a range partitioned table with one lob column. Each partion is on a separate tablespace except two partitions which are on same tablespace. Now I want to move a partition from one tablespace to another tablespace along with lob data. By using a simple alter table move partition will also move the lob data or there is some special procedure to adopt.

View 9 Replies View Related

SQL & PL/SQL :: How To Join A Table Column Names With Data From Other Table

Jul 18, 2012

I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.

CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......

View 6 Replies View Related

Forms :: To Move Data Block Detail

May 4, 2011

i made a master-deatil data block and when i completed all field on master-data block i can`t move to data block detail.

when i press (TAB OR ENTER) on last field, the cursor go back to first field.

View 3 Replies View Related

Server Utilities :: Move Data From One Schema To Another

Feb 7, 2013

I am trying to move data from one schema in database A to another schema in database B and only move data not others (procedure, view, function). What is the best way to do it? I am thinking database link then insert into select from database link. Is there a better way?

View 8 Replies View Related

Server Utilities :: How To Move All Data From One Tablespace To Another

Aug 23, 2012

I want to move all data from my users tablespace to anohter new tablespace i have created name test1

so how can i do this using expdp

i want to move all objects from this my users tablespace to this new tablespace

View 4 Replies View Related

SQL & PL/SQL :: How To Move Table To Customer DB

Apr 23, 2012

We want to provide data dump from 3 tables Like A,B,C.

In C we have more than 3 million records.

What is the best way to move this data from our DB to customer DB.

Customer is running on MySql

View 12 Replies View Related

Possible To Move Data At Storage Level Without Using Convert Command?

Apr 12, 2011

We are migrating database from HP to AIX. Data is all on SAN.I can do it through RMAN convert. What I want to know is if it is possible to move data at storage level without using Convert command?

View 2 Replies View Related

Client Tools :: Sql Developer Online Data Move

Dec 1, 2011

I am using SQL developer 2.1 to migrate tables from Sybase 12 database to oracle 11g. I have used online data move option for moving sybase data into oracle tables, but even after data move is completed not all rows have been moved from sybase tables to corresponding oracle tables. Some rows are missing but still there is no error message being displayed, how to find out what's going wrong.

View 3 Replies View Related

Best Tool / Method - Move Data In Real Time?

Sep 22, 2010

We have oracle 9i database named A with 2 schema A1 and A2.User enters data at A1 schema and the incremental data moved to A2 schema after some verfications performed by some scheduled job on A1 schema.

we want to move this incremental data to B1 schema of another oracle 9i database B at the real time when data gets entered to A2 schema of A oracle 9i database. We have access to A2 schema of A database and B1 schema of B database.way to do it or best practice to do this activity or if there is any third party tool or any available oracle utility to perform it?

Note: A2 schema of A database and B1 schema of B database has one to one mapping.We want to avoid using trigger on A2 schema, how data gets populated from A1 to A2 schema .

View 2 Replies View Related

Fastest Way To Move Table With Indexes?

Jan 18, 2011

I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?

View 4 Replies View Related

SQL & PL/SQL :: Oracle - How To Move Table To Another Schema

Feb 6, 2008

How can I move a table to another schema in Oracle?”

View 33 Replies View Related

SQL & PL/SQL :: Move Partition Of One Fact Table To Another?

Aug 30, 2011

I have two fact table and both are partitioned on same key and have same columns. Is it possible to move the partition of one fact table to another.

View 2 Replies View Related

Using Column Data As Table Name?

Nov 5, 2012

I have some tables. TABLE1 contains columns called query_id and a column called List_name TABLE2 holds a list of data and the TABLE_NAME = value in TABLE1 held under LIST_NAME TABLE3 contains column with Query_ID and other info.

Is it possible to grab the entry in TABLE1 under LIST_NAME as a TABLE_NAME in a data source?

View 1 Replies View Related

Server Administration :: Move Indexes (around 300) From Data To Index Tablespace

May 18, 2011

I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved