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
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
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
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
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
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
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
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
Jun 4, 2013
What is the real time uses for Associative array, nested table and varray ?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Feb 6, 2008
How can I move a table to another schema in Oracle?”
View 33 Replies
View Related
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
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
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