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
ADVERTISEMENT
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
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
Nov 4, 2009
CREATE TABLE "SCOTT"."SEATALLOTMENT"
("YEAR" NUMBER(4,0),
"COLLEGECODE" CHAR(4 BYTE),
"COURSECODE" CHAR(3 BYTE),
[Code].....
Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger
CASE:
Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60
View 5 Replies
View Related
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
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
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
Feb 20, 2012
I have a table with following values in a column
Table A
col1
10
35
20
25
I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.
Table B- 90 records (10 + 35 + 20 + 25)
Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,
with first group having start value -1 and end value -10
second group - start value -11 and end value-45
third group - start value -46 and end value -65
fourth group - start value - 66 and end value-90
one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.
note that the values in Table A is dynamically changing, so not able to hard code values.
View 2 Replies
View Related
Nov 6, 2013
I have 2 tables
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
View 2 Replies
View Related
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
View Related
Nov 10, 2010
I need to work on this requirement.
There are FOUR tables ( T1 , T11 & T2, T22) ALL store order information.
One of four conditions are possible for each Supply Reorder Number:
•Both table queries return no records
oPopulate all the output fields with nulls
•T1 returns a record, but T2 returns no records
oPopulate output fields with values from the join of T1 and T11.
•T1 returns no records, but T2 returns one record
oPopulate output fields with values from the join of T2 and T22.
•T1 returns a record, and T2 returns a record
oIf the latest order is in T1, then populate output fields with values from the join of T1 and T11.
oIf order dates are equal from both join results, then populate output fields with values from the join of T1 and T11 .
oIf the latest order is in T2, then populate output fields with values from the join of T2 and T22.
How do we filter the dataset based on result of table join ?
View 1 Replies
View Related
Feb 2, 2012
I'm working with Oracle 10g.
I have a table like this;
ID Amount Date
123 5000 Oct-07-2011
123 null Oct-09-2011
124 7000 Oct-14-2011
124 null Oct-17-2011
124 null Oct-24-2011
What I'm trying to do here is loop thruogh the records and update the amount that's null with the previous amount with the same ID.
View 3 Replies
View Related
Jul 29, 2013
I need to update more than 1million records in a table. I am using Oracle Warehouse Builder to do this Job. it is taking more than 3 hours still running. I don't have any Indexes on this table.
View 11 Replies
View Related
May 26, 2011
i have a table in my PD database which have more than 30,000 records .some records in a column say p_code is not tagged with code like '9876543'while other records are tagged in this column with code such as '19022345678'.
Now i want to update these records with tagging 1902 with each one .
View 26 Replies
View Related
Sep 21, 2012
Lets take the basic emp table for our Referenece and lets assume that it contains around 60000 Records and all the deptno in that table are Initially 10. Please provide an update statement which would update deptno column of EMP table((based on) order by EMPNO) in for every 120 records incrementing by 1.(DeptNo to be incremented by 1,like 10 ,11 , 12 etc).
First 120 Records deptno should be 10,
Next 120 Records deptno should be 11, and so on.
.
.
.
.
.
.
For Last 120 records deptno should be updated with 500.
View 3 Replies
View Related
Nov 20, 2009
want to update 230 records in ins_spr table but its returning error..
Sql statement in blue color returning 230 records.
------------------------------------------------
update ins_spr set
SPR_EXCC = 'NORSk'
where spr_code = (select distinct spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS') and spr_stuc in (select distinct a.sqe_stuc from srs_sqe a where a.SQE_EQEC = 'NP3M' and a.SQE_SQSC = 'BE' and a.sqe_stuc in ( select distinct b.sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
OR
update ins_spr set
SPR_EXCC = 'NORSK'
where spr_code = (select spr_code from ins_spr where spr_levc = 'N' and spr_facc = 'ROS' and (sts_code = 'AP' or sts_code = 'LS')
and exists (select sqe_stuc from srs_sqe a where sqe_stuc = substr(spr_code,1,8) and SQE_EQEC = 'NP3M' and SQE_SQSC = 'BE'
and exists ( select sqe_stuc from srs_sqe b where a.sqe_stuc=b.sqe_stuc and SQE_EQEC = 'NP3S' and SQE_SQSC = 'BE')))
View 1 Replies
View Related
Sep 16, 2013
what are the type of ways to update the 10 million records table with certain condition?
View 2 Replies
View Related
Mar 25, 2013
My table have duplicate records. I want to impose a primary key on that table. For that I have to replace duplicate values of a column with next value of a sequence.
View 2 Replies
View Related
Nov 29, 2012
below requirement..
We have certain records like SQL, PL/SQL, Reports, Forms, OAF etc in a table. We wanted to capture rating for each of these criteria. So we want a form to be displayed dynamically..
SQL 1 2 3 4 (1,2,3,4 represent radio buttons)
PL/SQL 1 2 3 4
Reports 1 2 3 4
Forms 1 2 3 4
OAF 1 2 3 4
If we add another row, XMLP in that table additional radio button should be displayed automatically..
View 4 Replies
View Related
Apr 16, 2010
I am having a table with 4 columns as mentioned below
For a particular prod the value greater less than 5 should be rounded to 5 and value greater than 5 should be rounded to 10. And the rounded quantity should be adjusted with in a product starting with order by of rank with in a prod else leave it
Table1
Col1prodvalue1rank
1A21
2A62
3A53
4B61
5B32
6B73
7C41
8C22
9C13
10C74
Output
Col1prodvalue1rank
1A51
2A52
3A33
4B101
5B02
6B63
7C51
8C52
9C03
10C44
I have taken all the records in to a cursor. Once after rounding the request of 1st rank and adjusting the values of next rank is done. Trying to round the value for 2nd rank as done for 1st rank. Its not taking the recently updated value(i,e adjusted value in rounding of 1st rank).
This is because of using a cursor having a value which is of old value. Is there any way to handle such scenario's where cursor records gets dynamically updated when a table record is updated.
View 9 Replies
View Related
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
May 18, 2011
I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL
and if it is not null ,i shud get the records of MAX date.
How would i do this. Below is my query,by which i can get records based on either maxdate or Null date
select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........
View 10 Replies
View Related
Jan 25, 2013
create table test_exp (oid number, ioid number, status varchar2(20));
Below are the insert statements.
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...
Now my problem is we should fetch the data based on the below rules
If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.
The expected output will be like below:
OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW
View 4 Replies
View Related
Aug 31, 2012
I have a table with with 2 colums serialnumber and brand .
each brand may have multiple serialnumber .
I want to extract 10 serialnumber for each brand .
View 2 Replies
View Related
Jun 7, 2012
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
View 3 Replies
View Related
Dec 5, 2012
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
View 5 Replies
View Related
Apr 22, 2013
I have a sets of data in table ow_ship_det , from which i want to group all the records which are having same sl_desc but with the condition that sl_qty is not more than 1000 and sl_wt not more than 50000, i managed to do it but the problem is i want the wieght(sl_wt) and qty(sl_qty) to be evenly distributed among groups or boxes for example take the first four records which have common sl_desc 'H170' Where the qauntities are 15000,15000,10000,10000 as per the condition and loop written in program it will bring the 2 boxes or serial numbers with first 3 weights into 1 box as 40000 and other box as 10000, which i dont want instead i want to have them as 25000 each.
CREATE TABLE OW_SHIP_DET (SL_PM_CODE VARCHAR2(12),SL_DESC VARCHAR2(20), SL_WT NUMBER,SL_QTY NUMBER);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('A','H170',15000,300);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('B','H170',15000,300);
insert into ow_ship_det(sl_pm_code,sl_desc,sl_wt,sl_qty) values ('C','H170',10000,300);
[code]...
--if you see above the weight is not balanced properly in batches 0001 for H170 Desc it should get divided equally as below
ob_batch OB_PM_CODE OB_DESC OB_QTY OB_WT
0001 A H170 300 15000
0001 C H170 300 10000
0002 B H170 300 15000
0002 D H170 300 10000
[code]...
View 9 Replies
View Related
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
Jun 28, 2010
I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.
Sample Table Script:
CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...
View 8 Replies
View Related
Dec 20, 2012
I have a table with below syntax and insert statements.
-- Table creation
-----------------------
create table order_test (oid number,
orderno number,
ordername varchar2(100),
orderstatus varchar2(50),
orderseq number);
----------------------------------
-- Insert statements
[Code]....
The Output looks like below
OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
1 |100 |ORD1 |INPROGRESS |1
7 |100 |ORD1 |START |4
3 |100 |ORD1 |START |2
4 |100 |ORD1 |INPROGRESS |3
6 |101 |ORD2 |CANCELLED |3
2 |101 |ORD2 |INPROGRESS |2
5 |101 |ORD2 |COMPLETE |1
8 |102 |ORD3 |COMPLETE |1
9 |103 |ORD4 |CANCELLED |1
10 |103 |ORD4 |CANCELLED |2
Now, the below rules should be applied to get the output..
1) If status is same, then fetch max(seq) record
2) If status is different, then apply below priority to fetch the records
Start - Priority 1 (High)
In Progress - Priority 2
Cancelled - Priority 3
Complete - Priority 4 (Lowest)
Means, the output for each order should look like below.
OID|ORDERNO|ORDERNAME|ORDERSTATUS|ORDERSEQ
3 |100 |ORD1 |START |2
2 |101 |ORD2 |INPROGRESS |2
8 |102 |ORD3 |COMPLETE |1
10 |103 |ORD4 |CANCELLED |2
how to do it.
View 1 Replies
View Related
