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].....
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
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:
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
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].......
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
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
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.
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.
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 .
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.
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')))
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.
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..
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
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.
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]....
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.
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.
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.
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
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.
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]...
