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?
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]....
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.
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.
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
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 ?
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
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]......
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]....
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?
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.
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.
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
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.
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.
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
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.
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
