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
ADVERTISEMENT
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
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
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
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
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
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
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
Jun 14, 2013
I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :
YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate
View 5 Replies
View Related
Nov 16, 2012
How to insert more than 30000 records in a table using oracle procedure where I am having a table with number,varchar,data fields and columns like mpno,ename,sal,date of joining,data of leaving.
Data should populate using procedure.is there any way of doing it by procedure
View 6 Replies
View Related
Oct 9, 2013
I have a table of 10 records ,out of 10 records 2 records are having null values by using anonymous block i need to move the successful record(excluding null) into 'abc' tableand null records into 'err'table.
View 3 Replies
View Related
Feb 20, 2013
I have a data from the source table like this :
IdDate1 No1 Date2 No2
12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680
I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)
View 4 Replies
View Related
Dec 17, 2011
I have one indexed column in a table.for performance improval purpose i created index to that column .
my doubt is..it may decrease the performance while inserting data into that column?
View 5 Replies
View Related
Mar 8, 2013
I got Scenario that, i need to insert the number of records loaded in target table into the log table.
But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.
/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......
View 7 Replies
View Related
May 8, 2012
which is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;
[code].....
View 4 Replies
View Related
Apr 1, 2012
i have a master-detail form.
detail block is tabular.
when-button-pressed trigger iam inserting records in another table.i write
insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)
it is inserting only one record in onhand_qty_location table that too the last record.
i want all the records which iam entering in detail block should get entered in onhand_qty_location table.
View 8 Replies
View Related
Dec 19, 2011
I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies
View Related
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
Oct 16, 2013
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
View 14 Replies
View Related
Apr 9, 2012
Find the code below,
DECLARE
L_DE_TGUP_ID BBP_ALLOC.DATA_ELEMENT.DATA_ELEMENT_ID%TYPE;
L_ZERO_EXP EXCEPTION;
L_DATA NUMBER;
BEGIN
SELECT DATA_ELEMENT_ID INTO L_DE_TGUP_ID FROM BBP_ALLOC.DATA_ELEMENT WHERE DATA_ELEMENT_CD = 'TGUP';
SELECT DECODE(UPPER('0.0028'),'',0,'0',0,'NULL',0) INTO L_DATA FROM DUAL;
[code]......
Here the relationship between the source and target tables is one to one. but still i am getting "unable to get the stable set of rows from the source tables" error. Source query retrieves only 2 rows which are distinct. but still getting this error.
View 14 Replies
View Related
Aug 30, 2010
When i am running the below merge statement it gives the error as below
MERGE INTO non_tab tt
USING (SELECT non_tab.acct,
non_tab.mis,
non_tab.n_gaap_skey,
non_tab.run,
non_tab.mit_ul AS t0,
CASE
WHEN (( CASE
[code]...
ORA-30926: unable to get a stable set of rows in the source tables.
View 5 Replies
View Related
Sep 15, 2013
When I am executing merge statement I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
MERGE INTO TAN_LIST t
USING (SELECT * FROM (SELECT row_number () over (partition by TANNO order by null) rn,
dno,
TANNO,
SOL,
[Code]....
The query is fectching below data.
SELECT dno,TANNO,SOL,DESC,class,ct_dt,tcost
FROM MAT_LIST;
DNOTANNOSOLDESC CLASS CT_DT TCOST
63007565ADclass A A12345 08/28/131
[Code]...
Intially thers is no records in the TAN_LIST table. When I run the merge statemnt I am able to insert all 15 records into that table.
When I run the same merge statemment second time I am getting the below error.
ORA-30926: unable to get a stable set of rows in the source tables
So that I have used partition by cluase in the slect statement and I am able to resolve the error.
But it's inserting only 14 records not all 15. How to process all 15 records without the error..
View 16 Replies
View Related
Jul 20, 2009
We are using Oracle Streams for replication.
Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).
Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.
View 1 Replies
View Related
Jun 21, 2012
I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.
create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
[Code]....
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex
View 2 Replies
View Related
Mar 7, 2013
I am trying to insert record from sql loader but this is inserting only single row.
View 3 Replies
View Related
Jun 29, 2012
I have a view SV (say) which holds approximately 33,000 records. But, when I try to insert these many records into a table SV_T (say) it is taking huge amount of time i.e. 2-3 hrs (approx.)
how reduce the time taken for the insert.
View 3 Replies
View Related
May 2, 2012
I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?
Let's consider an example with 5 records:
Table data:
CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/
[Code]...
So when we check the table, we get:
SELECT * FROM enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
What I would like to have when I run the select query:
SELECT * from enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.
View 6 Replies
View Related
Feb 15, 2009
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
View 2 Replies
View Related
Sep 25, 2012
There is an error after error, that's why I came here
What's is wrong? error message: virtual column not allowed here
CREATE TABLE test2(
id NUMBER PRIMARY KEY,
surname VARCHAR2(20) NOT NULL);
CREATE TABLE test1(
[Code]...
It would be nice to describe this situation and where does this error come from.
View 1 Replies
View Related
