SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inserting Records Into Target Table From Three Source Tables?

Dec 14, 2012

I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.

SQL> create or replace
2 PACKAGE
casadm.sis_load_cpl_sis_reb_pgm_hist
3 IS
4 /**********************************************************************
******************

[code]....

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

View 16 Replies View Related

SQL & PL/SQL :: Insert Into Target Table And Delete From Source

Jan 2, 2013

We have a requirement to archive and purge the tables dynamically based on the control table input. For that we have to design a control table to gather the necessary information and passed to generate the queries.

I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.

Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables, so first required data will be inserted into target table and delete from source parent and child tables. so before deleting from parent we have to delete data from all 2 child tables.

Suppose one table needs to be purged and that table is parent table and it is having 5 child tables, so before deleting from parent we have to delete data from all 5 child tables.

To handle this scenario how can I design my control table.

For archive and purge the query like this.
INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

for purge the quey is like this.
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);

This is my control table and I have 300 tables list to archive and purge.

CID SOWNER_NAME STABLE_NAME TOWNER_NAME TTABLE_NAME CONDITION_COLUMN PERIOD UNIT TYPE
1 wedb_au OFFER_HEADER wedb_au OFFER_HEADER LAST_DATE 30 D A
1 wedb_sa OFFER_CUSTOMER wedb_sa OFFER_CUSTOMER LAST_DATE 60 D A
1 wedb_au OFFER_SERVICE LAST_DATE 1 Y P
1 wedb_us OFFER_CUSTOMER LAST_DATE 90 D P
1 wedb_cn OFFER_CARDS UPDATE_DT 2 Y P
2 wedb_au ORDER_HEAD wedb_au ORDER_HEAD LAST_DATE 120 D A
2 wedb_us ORDER_CUSTOMER wedb_us ORDER_CUSTOMER LAST_DATE 150 D A
2 wedb_sa ORDER_HEAD wedb_sa ORDER_HEAD CREATION_DT 1 Y A
3 wedb_us DELIVERY_HEAD wedb_us DELIVERY_HEAD UPDATE_DT 50 D A
3 wedb_au DELIVERY_CARDS wedb_au DELIVERY_CARDS UPDATE_DT 200 D A
3 wedb_au DELIVERY_SERVICE wedb_au DELIVERY_SERVICE LAST_DT 100 D A

WHERE TYPE=P means insert and delete
TYPE=A means only delete

wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.
wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER
wedb_sa.OFFER_CUSTOMER Stand alone table no relationship
wedb_us.OFFER_CUSTOMER Stand alone table no relationship
[code].......

View 2 Replies View Related

SQL & PL/SQL :: Transfer Partition Of Table From Source To Target DB?

Apr 6, 2011

I currently try to transfer a partition of a table from a source to a target DB. For first test purposes I take both SYS users to avaoid privilege problems. I created below procedure from code fragments out of the net.The partition CSS_201001 from table CTRL_SETTLED_SHIPMENTS shall be transferred (I tried both with already existing partition and non existing on target destination), but I always get the following error at DBMS_DATAPUMP.OPEN:

Exception breakpoint occurred at line -1 of DBMS_SYS_ERROR.pls.
$Oracle.EXCEPTION_ORA_39001:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3043
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4769
ORA-06512: at "SYS.TEST_DP", line 20
ORA-06512: at line 2

Listing:

create or replace
procedure test_dp is
-- Handle -- unique identifier for the datapump job
my_handle number;
ind NUMBER; -- Loop index
percent_done NUMBER; -- Percentage of job complete

[code].....

View 7 Replies View Related

SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source

Jun 1, 2010

I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.

TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....

View 9 Replies View Related

SQL & PL/SQL :: Loading Data From Source To Target DB Using DBLink

Jul 21, 2010

Source and Target db version : 10.2.0.4.0
Source Os :Cent OS 5.4 and Target OS:Sun OS 5.10

We are loading data from source DB to target DB using dblink.Source db is having 15.4 crore records of number and varchar2 data types.

Using the logic as follows.
insert into table1 as select * from table1@dblinkname[/email];

DB link is working.If I give "select * from scott.REPORT@DBLINK[/email]" in target db alone i could retrieve records.

Actual query:

Create Or replace procedure test_abcd as
begin
Insert into test select *
from scott.REPORT@DBLINK[/email]
dbms_output.put_line('Hello world');
end;

When i give the above query in sqlplus ,it is hanging. When i see the wait events i could find

"Wait Event: SQL*Net more data from dblink"

How to get the above things working.? when checked with network team they says there is no issue in the network. Do we need to modify any database/network level parameter settings.there is no firewall between source and target db.

View 9 Replies View Related

SQL & PL/SQL :: Pull In Last Loaded Data From Staging Table To Target Table?

Mar 9, 2011

I have a staging table and a target table. How do I pull in last loaded data from staging table to target table?

View 4 Replies View Related

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

SQL & PL/SQL :: Table Comparison - Loaded Data From Source To Destination

Apr 29, 2013

i have 2 tables in two different sources.

I have loaded data from source to destination.

some rows are missed while loading.

i want to know the missing rows

View 11 Replies View Related

Replication :: Table Data Modification From Source To Destination Database

Jan 11, 2011

I want to replicate single table data/modification from source db to destination DB.procedure or steps how to create replication between source and dest db for single table.

View 18 Replies View Related

PL/SQL :: Load Data From Flat File To Target Table With Scheduling In Loader

Sep 7, 2013

I have requirement as follows.  I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table.  If first week I loaded AP data, then second week on Saturday karnatak, etc.

Provide code also how can i schedule the data load with every Saturday  with different state column values automatically.

View 2 Replies View Related

SQL & PL/SQL :: Inserting Data From One Table To Another?

Oct 23, 2010

I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0

TEST22:
-----------
|sno |sname |
-----------
| | |
-----------

TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------

Required outcome is

TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------

Also this should be applicable for more than one value in the column col2 of table TEST22P.

ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------

I used decode & pivot insert for this,but the result is a failure.

SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;

SQL>
sno sname
--------
1 null
null arun

AND

SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier

View 4 Replies View Related

SQL & PL/SQL :: Source Does Not Have A Runnable Target

Dec 18, 2012

While i am trying to execute this proc,i am facing source does not have a runnable target issue.

CREATE OR REPLACE
PROCEDURE GET_CHILDS_SUB
( nid IN VARCHAR2
) AS
n varchar2;
CURSOR cur is SELECT node_id FROM test_tbl WHERE parent_id = nid;
BEGIN
[code]....

View 14 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 Replies View Related

PL/SQL :: Inserting Data Into Updated Table

Jul 22, 2012

I have a table created with the following code:

CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),

[Code]...

I have altered the table to have the following:

ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);

I am trying to insert new data into said table that was updated:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;

Then I get the following error:

Error starting at line 1 in command:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:   
*Action:

View 3 Replies View Related

PL/SQL :: Extracting Data From XML And Inserting Into Table

Aug 8, 2013

I have an XML  of the following format:

<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students> 

Now i have to insert this xml into DB , the table consist of following columns  ( row number , property name , value )  Expected out put is   (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is

1. how to get the tag names  and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?

View 8 Replies View Related

PL/SQL :: Inserting Data Into Nested Table

Jul 22, 2013

I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.

CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);

---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........

View 4 Replies View Related

Performance Very Slow Inserting Data Into Table

Jun 25, 2013

I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query

INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;

Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.

View 4 Replies View Related

Forms :: Inserting Into A Table Using Data From A Text Box

Feb 16, 2010

I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.

I tried:

begin
insert into StaffName values ('addstaffmember');
end;

Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.

View 5 Replies View Related

SQL & PL/SQL :: FORALL For Inserting Data (with Some Constant Value) Into Table

May 13, 2011

using FORALL for inserting data into table.

Below pl/sql works fine when we write all the cursor data together:

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

but I want to insert including some constant value while inserting the data like

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

How to do this by using forall, or how to do without hiting performance ( more than 5000 rows i have to write.

View 5 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

Forms :: Reading Excel Data And Inserting Into Table Using 6i?

Feb 16, 2004

how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.

View 3 Replies View Related

RAC & Failsafe :: Inserting Large Data Locks The Destination Table In RAC

Oct 18, 2010

Scenario:

Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.

What we are performing is:

Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;

Issues:

my_table_rpt get locked

We have found the workaround by disable locking in destination and subsequent to the insert enable locking

ALTER TABLE my_table_rpt DISABLE TABLE LOCK;

Insert the data to the reports database table

Then

ALTER TABLE my_table_rpt ENABLE TABLE LOCK

Question:

Why does the large destination table (my_table_rpt) get locked in the RAC environment?

View 2 Replies View Related

PL/SQL :: Inserting Values In Table From Other Table

Aug 3, 2012

I have a table AUD$ (containts some data) and aud_new(exactly structure same as AUD$ table with no data)

i want to schedule a job which will insert all data from AUD$ into table aud_new$ from aud$ table on next day again data from aud$ should be append to aud_new table and aud$ table should be truncated again.

The job should run on every day at midnight at 2 am

(note : If insertion in table aud_new get fail due to any reason , the aud$ table must not get truncated )

View 3 Replies View Related

SQL & PL/SQL :: MAX DATE - Getting Records Out Of Source Table

Sep 28, 2011

Below is my requirement,

Source Table: SRC

COL1 DATE_CREATED CREATED_BY
1 27-SEP-2011 GURU
1 28-SEP-2011 SANKAR

Target Table:TGT

COl1 DATE_CREATED CREATED_BY
1 28-SEP-2011 SANKAR

I need to take the MAX of date_created record and store it in target.

I tried,

select max(date_created), col1, created_by from src
group by col1, created_by,date_created

Which is giving me 2 records which i don't want. How to get only one record out of that source table?

View 2 Replies View Related

PL/SQL :: How To Find Source Code Of The Table

Sep 4, 2013

i have a existing table called table_1 to did some changes to it, but i need to do a source search to find where all  in the code that we reference this code to ensure that there is not a variable declaration that sets this to a specific number. how do i do a source search. i alter a existing column (overbook_max) to number(2) tonumber (3)

View 8 Replies View Related

SQL & PL/SQL :: To Insert The Rows Into A Table Target

Oct 3, 2011

I have a table named SOURCE with about 1.000.000 rows looking like this

date varchar2 varchar2 varchar2
DATE ID1 ID2 AMOUNT
---------- ----------- --------- --------
2010-07-03 1403 1403 1500
2010-07-13 2015438 etc 188608 6074
2010-08-28 1151927 410,4222 1750
2010-08-28 13622012 41026 178.99
2010-08-28 1600246 John 65

I want to insert the rows into a table TARGET

date integer integer number
DATE ID1 ID2 AMOUNT

There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.

View 3 Replies View Related

SQL & PL/SQL :: Insert Or Update Records Into Target Table

Dec 21, 2012

I am trying to insert rec into target table if those rec are not existing and trying to update those rec if they already exists from three source tables.I had seen in posts that merge cannot be used with cursor.

SQL> create or replace
2 PACKAGE sis_l_cpl_sis_reb_pgm_hist_pkg
3 IS
4 /********************************************************************
******************
5 PACKAGE: sis_load_cpl_sis_reb_pgm_hist
6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_p
h_dtl,cmply_sis_sls_dtl,
7 cmply_sis_excl_dtl(intial load)
8 *********************************************************************
******************/
[code].......

Package created.

SQL> create or replace
2 PACKAGE BODY
sis_l_cpl_sis_reb_pgm_hist_pkg
3 IS
4 /**********************************************************************
******************
5 PACKAGE: sis_l_cpl_sis_reb_pgm_hist_pkg
6 PURPOSE: Load CMPLY_SIS_REB_PGM_HIST with data from cmply_sis_pur
h_dtl,cmply_sis_sls_dtl,
7 cmply_sis_excl_dtl(intial load)
[code].......

Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY SIS_L_CPL_SIS_REB_PGM_HIST_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
67/7 PL/SQL: SQL Statement ignored
75/19 PL/SQL: ORA-00926: missing VALUES keyword

View 10 Replies View Related

PL/SQL :: Populate Target Table - Dynamic Rows

Jun 21, 2013

create table src(id number,val number,data varchar2(100)) insert into src values (1,1,'SUN');
insert into src values (2,2,'WED');
insert into src values (3,3,'MON');     
create table trg(id number,val number,data varchar2(100)) required rows to be inserted in the target table. 

  insert into trg values (1,1,'SUNDAY');
insert into trg values (2,0,NULL);
insert into trg values (2,0,NULL);
insert into trg values (3,0,NULL);
insert into trg values (3,0,NULL);insert into trg values (3,0,NULL); 

 {code} based on the column value  of the source table src's column val , i need to populate my target table trg . If the value of val is 1 then  only one target row is created in the target .If the value of val in the source table src is 2 then the target is populated with 2 rows .The values of the target columns are mapped as follow:

1)id -as it is

2)val - if the val of src is 1 then map the val as it is .If the value of val is more than one then create as many rows as the value of val ,id will be as it is and the  value of val and data will be null

3)data - if the val of src is 1 then expand the abbreviation else null . 

View 17 Replies View Related

SQL & PL/SQL :: HTML Source Code Inserted Into Table As CLOB

Sep 24, 2010

I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.

View 12 Replies View Related







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