Merge Operation By ROW-ID?
Sep 8, 2011
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
View 8 Replies
ADVERTISEMENT
Mar 11, 2010
I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
l_fun_ret_value NUMBER;
v_src_id NUMBER;
rec_jci_contract_dim jci_contract_dim%ROWTYPE;
rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE;
err_row VARCHAR2(4000);
ind NUMBER(1) := 1;
[code]....
View 2 Replies
View Related
Sep 17, 2011
i HAVE TWO TABLES
EMP_NAME_DETAILS
EMP_ID EMPNAME
EMP_ADD_DETAILS
EMP_ID EMPCITY
NOW I WANT TO MERGE/COMBINE THIS TWO TABLES INTO ONE FLAT TABLE SO MY REPORTS WILL RUN ONLY FROM ONE TABLE INSTEAD OF TWO.
MY DESIRED OUTPUT TABLE WOULD BE
EMP_ID EMPNAME EMPCITY
View 2 Replies
View Related
Nov 23, 2012
the query ..
Below are the codes
create table testing1
(
id number,
data varchar2(20)
)
insert all into testing1 values (1,'100')
into testing1 values (2,'Ram')
[Code]....
I want the output as
100 Ram Pune
200 Shyam Mumbai
300 Kamal bangalore
View 8 Replies
View Related
Jun 12, 2013
I am using the dblink to merge the data. I am using the following merge statement.
merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t
Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s
on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE)
when not matched then
insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME)
values ('','test','TESTxxx',null, null, null, null);
I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.
View 1 Replies
View Related
Apr 29, 2010
What are all the DML operation can be done in DUAL table.?
View 5 Replies
View Related
Jun 12, 2013
I have a source view where I have some invalid records and those should be found based on codes present in another table.
For eg. from source the records come like
****************SIINNSFDFD****FDFDF2******8
**********TABLE****************FDFSFSSFASFAS********
and if my reference table has values
SIINNSFDFD
TABLE
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
View 2 Replies
View Related
May 5, 2010
i have created an file in my local system (sys name : System1). i have created a directory as below
SQL>CREATE DIRECTORY test_dir AS '\System1TEMP';
I gave rights as
SQL>GRANT ALL ON DIRECTORY TEST_DIR TO PUBLIC;
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
IF utl_file.is_open(fileHandler) THEN
utl_file.fclose_all;
END IF;
[code]......
but while executing the above procedure, Invalid File Operation error occurs.
View 10 Replies
View Related
Dec 2, 2011
I want to create one sql script with the followings-
1.Show the values before the operation is performed
2.Display the values after the operation is performed.
How it can be done in a proper sql script format.
View 10 Replies
View Related
Apr 26, 2013
Frequently getting below error in alert log file. to sort out this issue.
Database: Active standby database
OS: Windows Server
Db Version: 11.2.0.2
Error:
***********************************************************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
[code]...
View 1 Replies
View Related
Jul 25, 2012
I have a car table with the following attribute sale_code,model_car etc. the format of sale_code is year/number example when a client buy a car(model_car=mercedes) her code is 2012/0125.
I want to calculate the number of cars that were sold in the year of 2011 grouped by the model_car attribute.
select count (sale_code),model_car
from car
where sale_code like '2011%'
when I tested this query i received an error.
My question is :
how to resolve the issue when we use an attribute in where and we use the same attribute in count operation ?
View 9 Replies
View Related
Aug 31, 2007
Procedure A and Table A are located in Schema A. Procedure A performs a merge between View B of Schema B and Table A. Procedure A is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D
Code excerpt
---------------------
MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET
C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, ...
Permissions given are as follows
Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A
Schema B
----------------
ViewB - Select on User A, Select on UserARole
I believe appropriate permissions have been given.
View 1 Replies
View Related
Jun 28, 2010
I am trying to merge into test table using a cursor. If the contract number in the test table matches with the contract number from the cursor then i need to update the creation_date else i need to insert values from the cursor into the test table.
MERGE INTO test
USING cursortest
ON (i.contract_number = test.contract_number)
WHEN MATCHED THEN
[code]......
View 9 Replies
View Related
Jun 22, 2012
how to update or insert another (third table ) table with merge statement
View 6 Replies
View Related
Apr 7, 2011
How am i able to combine multiple rows into one row without using cursor if possible. I am currently using SQLPlus.
My current result set:
user id|user name|group name
1|user1|group1
1|user1|group2
2|user2|group1
2|user2|group3
What i wish to achieve:
user id|user name|group name
1|user1|group1,group2
2|user2|group1,group3
View 7 Replies
View Related
Aug 19, 2012
I have problem with merging rows into view. I have created "INSTEAD OF" triggers on the view for insert/delete/update of rows. However, the merge is not working and it needs rowid to work. Is there any way how to make MERGE working here ? I must use merge, because I am changing the structure of database used for large java application. And it uses tons of merge commands,changing them to insert/update is inefficient because of development-time and of course resulting execution-time.
create table val_00 (
id number(10),
data varchar2(100),
constraint pk_val_00 primary key (id) validate
) organization index;
[code]....
View 4 Replies
View Related
Jun 4, 2012
CREATE TABLE emp
(
code NUMBER(4),
name VARCHAR2(15),
salary NUMBER(5),
ovt NUMBER(4),
sal_mon DATE,
dept number (3)
)
[code]....
9 rows selected.
Required Output is
CODE NAME SALARY OVT SAL_MON DEPTMerge Codes
---------- --------------- ---------- ---------- --------- ---------- -----------
1068 ALI 18500 2385 31-OCT-11 1021068,1793
1908 ASGHAR 17000 1900 31-OCT-11 1021908,1802
1960 ZAHID 16000 775 31-OCT-11 1021960,1952
1188 RAZA 9500 2910 31-OCT-11 1111188
1275 RAHIL 15300 3480 31-OCT-11 1111275,1176
Merge 02 rows of same department having sum(salary) <=20000. select those rows who have lower salary.
View 10 Replies
View Related
Jul 18, 2011
how to use the MERGE Statement. actually I've used oracle Merge Statement before and it works very well. However today I tried to use and perform a command like that:
Merge into myTable mt using ( select 'data' field1, 'data2' field2, ect from dual
union
select 'data' field1, 'data2' field2, ect from dual
union
[code]...
This has not worked.What am I doing wrong?What could I do to solve this problem and axecute this statement sucessfully?
View 3 Replies
View Related
May 9, 2011
in Toad I am using this Merge statement , working accordingly.I want to use this merge statement in my form where can I use it.
I created a Procedure in Form Named Proc_merge and call it when validate item of :bankcode
PROCEDURE proc_merge IS
BEGIN
declare
v_com varchar2(5000);
BEGIN
V_COM := '
[code]...
when matched then
update set
trg.v_chq = src.cheque_no, trg.v_bankcode = src.bank_code ,trg.v_debit=src.debit, trg.v_credit=src.credit, trg.v_narration=src.narration
when not matched then
insert (trg.v_type, trg.v_no, trg.v_date, trg.v_chq, trg.v_bankcode, trg.v_debit, trg.v_credit, trg.v_narration)
values (src.voucher_type, src.voucher_no, src.voucher_date, src.cheque_no, src.bank_code, src.debit, src.credit, src.narration)
';
FORMS_DDL(V_COM);
end;
END;
no any result.
View 2 Replies
View Related
Aug 28, 2012
I have some records as below would like to match and merge them
ID1 ID2
101 103
101 104
205 103
206 103
205 106
151 222
157 222
151 223
156 222
134 223
134 435
156 438
Output should be. All matching records should be grouped together and generate result by new seq
ID1 ID2 Result
101 103 1
101 104 1
205 103 1
206 103 1
205 106 1
151 222 2
157 222 2
151 223 2
156 222 2
134 223 2
134 435 2
156 438 2
View 15 Replies
View Related
Jul 23, 2013
My teacher taught the lesson of DML statemnts, he told us how does merge works , but he did not give us any query for that,provide query for Merge and if possible then explain it too , I am using Oracle 10g Sql Plus.
View 3 Replies
View Related
Feb 21, 2013
Does the Oracle 11g supports 'RETURNING INTO' clause in MERGE statement? if it was not available is there any alternate to achieve the same ?
View 3 Replies
View Related
Apr 23, 2013
I am using the following merge statement it's not working properly. If I tried to insert (or) update the existed record. I am getting unique key constraint violated error.
DECLARE
p_buid NUMBER;
p_ordernum NUMBER;
[Code].....
View 12 Replies
View Related
Apr 14, 2011
i wanted the query for the followin example
sample1
A B C D E F
--------------------------
1 1 1 1 1 1
2 2 2 2 2 2
3 3 3 3 3 3
4 4 4 4 4 4
5 5 5 5 5 5
sample2
G H I J
----------------
1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
consider the above tables what i want is some thing like this
output
G H I J A
-----------------
this can also be done through a select statement through choosing the columns which you want i don want thatbut what i want is columns of entire table sample2 and only one column in sample1
View 5 Replies
View Related
Sep 4, 2012
select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'
when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?
View 1 Replies
View Related
May 31, 2007
Can i use table type in the using clause of merge statement ?
Attached is the code of the procedure
create or replace procedure fnd_sample as
cursor fnd_c is select * from fnd_columns;
type test_t is table of fnd_columns%rowtype;
[Code]....
(APPLICATION_ID,TABLE_ID,COLUMN_ID,COLUMN_NAME,USER_COLUMN_NAME,COLUMN_SEQUENCE,
LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,
COLUMN_TYPE,WIDTH,NULL_ALLOWED_FLAG,TRANSLATE_FLAG,FLEXFIELD_USAGE_CODE) values
(f.APPLICATION_ID,f.TABLE_ID,f.COLUMN_ID,f.COLUMN_NAME,f.USER_COLUMN_NAME,
f.COLUMN_SEQUENCE,f.LAST_UPDATE_DATE,f.LAST_UPDATED_BY,f.CREATION_DATE,f.CREATED_BY,
f.LAST_UPDATE_LOGIN,f.COLUMN_TYPE,f.WIDTH,f.NULL_ALLOWED_FLAG,f.TRANSLATE_FLAG,f.FLEXFIELD_USAGE_CODE);
end loop;
exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
I am getting an error saying that
/19 PL/SQL: SQL Statement ignored
8/60 PL/SQL: ORA-00942: table or view does not exist
View 18 Replies
View Related
Apr 2, 2010
My requirement is ..
SQL> CREATE TABLE EMP (EMPID VARCHAR2(100), U_EMPID VARCHAR2(100));
Table created.
SQL> INSERT INTO EMP VALUES ('DUMMY1','J0001');
1 row created.
SQL> INSERT INTO EMP VALUES ('DUMMY2','J1002');
1 row created.
SQL> INSERT INTO EMP VALUES ('DUMMY3','J1003');
1 row created.
SQL> SELECT * FROM EMP;
EMPID U_EMPID
------------------
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003
in this example empid is dummy empid (which will be issued for contractors), once the contractors are considered to be taken into payroll that u_empid will be updated by HR manually, we need to replace empid with u_empid. then data will become like this.
SQL> SELECT * FROM EMP;
EMPID U_EMPID
---------------------------
DUMMY1 J0001
DUMMY2 J1002
DUMMY3 J1003
J1001 NULL
J1002 NULL
J1003 NULL
now my requirement is there are approximately 60 child tables are there for EMP table, and EMPID is the foreign key for all of them with "on delete cascade" defined on those. Now first i need to create records in all the child tables with actual employee id's (which is u_empid). if the actual id is already exists then just need to update rest of the columns in that table except the EMPID.
Once the records are created with actual empid, then i will issue a delete command to remove dummy employee id's in emp table which will take care of deletion in child tables as well.
I was thinking of two options, one is go with Merge other is write a cursor and handle the update in exception block using when duplicate value on index feature.
View 2 Replies
View Related
Sep 4, 2012
I need to calculate total number of months within a year that account existed for each SSN.
SSN CODE RECORD_DATE PAYMENT
--- ---- ----------- ----------------------
AAA 00 01-FEB-89 50
AAA 01 01-AUG-89 50
BBB 00 01-FEB-89 0
BBB 01 01-AUG-89 50
CCC 00 01-FEB-89 50
CCC 01 01-AUG-89 0
A non-zero payment indicates that the account was open on that date. A zero payment indicates that the account is closed and assumes that it was open since Jan-01.
AAA: code 00 was open in February, so it was open for 11 months (Feb-Dec)
code 01 was open in August, so it was open for 5 months (Aug-Dec)
So the result should be 11
BBB: 00 - closed in February, existed for 1 month
01 - open in August, existed for 5 months
Result should be 6.
CCC: 00 - 11 months(Feb to Dec)
01 - 7 months (Jan to Aug)
Result: 12 months
Need to get:
AAA 11
BBB 6
CCC 12
Here is the source table:
SELECT 'AAA' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'AAA' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'BBB' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'BBB' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'CCC' SSN, '00' CODE, '01-FEB-89' RECORD_DATE, 50 Payment from dual union all
SELECT 'CCC' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'DDD' SSN, '00' CODE, '01-OCT-89' RECORD_DATE, 0 Payment from dual union all
SELECT 'DDD' SSN, '01' CODE, '01-AUG-89' RECORD_DATE, 50 Payment from dual
View 12 Replies
View Related
Apr 12, 2011
I'm trying to write a package which will allow my users to send emails, based on templates, from our system. The email body templates are stored as text in a CLOB column in a EMAIL_TEMPLATES table, along with an email_id. Here's an example:
Quote:Dear [dearname],
I write with regard to your child, [childname] who is attending [schoolname].
Now, I'm trying to write a procedure that will take an email_id and the fields such as dearname, childname, etc., merge the data into the template and return the final body of the email.
The email body will then finally be inserted into a standard HTML email template and be sent using UTL_MAIL.
I was planning on using a series of nested REPLACE functions but I'm sure there must be a tidier way than that. Particularly as I would like the procedure to be as flexible as possible to allow me to easy adapt it to receive additional fields for different email templates in future.
View 3 Replies
View Related
Sep 10, 2013
What is the easiest way to add 2 rows together? I need this results set below as is except I need rows with activity codes 0 and 30 to be merged together into Activity Code 0. So, Regular hours would be 7466.75. Do I need to do a Union or is there a more elegant solution?
My current sql results set looks something like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT
0 0: Business As Usual 7409.25 305.5 1603.25 Information Technology
10 10: Special Projects 190.75 17 0 Information Technology
20 20: Strategic Initiatives 1067 52 0 Information Technology
30 0: Business As Usual 57.5 0 0 Information Technology
and I want my new results to look like this:
Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT
0 0: Business As Usual 7466.75 305.5 1603.25 Information Technology
10 10: Special Projects 190.75 17 0 Information Technology
20 20: Strategic Initiatives 1067 52 0 Information Technology
View 7 Replies
View Related
