SQL & PL/SQL :: Merge With Returning Clause
Feb 21, 2013Does 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
ADVERTISEMENT
PL/SQL :: Merge Statement - Delete Clause?
Mar 14, 2013I was reading about merge statement and tried some variations,
create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);
insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;
[code]...
why is result different in this querys?
SQL & PL/SQL :: Dynamic SQL Returning Clause Into Collection
Nov 8, 2010I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 comp NEW YORK
20 Compt DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 Comp Ahaaa
80 data ab
80 data ab
80 data ab
80 data ab
9 rows selected.
s@ORCL> ed
Wrote file afiedt.buf
1 declare
2 type tp_dept is table of number;
3 arr_dept tp_dept;
4 begin
5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]'
6 returning into arr_dept;
7 dbms_output.put_line('The count is '||arr_dept.count);
8* end;
s@ORCL> /
returning into arr_dept;
*
ERROR at line 6:
ORA-06550: line 6, column 19:
PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
PL/SQL :: Model Clause Returning Phantom Value
Sep 25, 2012I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.
DROP TABLE MORTGAGE_FACTS
CREATE TABLE mortgage_facts (
C_USER1 CHAR(15),
C_USER9 NUMBER,
C_USER11 NUMBER,
C_USER10 NUMBER,
C_B1 NUMBER(19,2),
C_B2 NUMBER(19,2),
C_BUDGET NUMBER(19,2),
C_USER12 NUMBER(19,2));
INSERT INTO mortgage_facts VALUES ('Jones', 131828.81, 3.348, 72, 0, 0, 0, 0)
--BEGIN AMORTIZATION
SELECT
P,
C_USER1,
C_USER9,
C_USER11,
C_USER10,
C_B1,
C_B2,
[code]........
The first two results look like this:
P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55
As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.
SQL & PL/SQL :: Function Returning A Table Inside Where Clause?
Apr 5, 2010DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?
May 24, 2013I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[code]....
SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause
Sep 13, 2013In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
SQL & PL/SQL :: OrderBy Clause Before From Clause?
Apr 23, 2010can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
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?
SQL & PL/SQL :: Merge Two Table Into One
Sep 17, 2011i 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
SQL & PL/SQL :: Merge Rows
Nov 23, 2012the 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
SQL & PL/SQL :: Returning Old Value During Update?
Jan 2, 2012In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
SQL & PL/SQL :: Returning Row With Same Values
Mar 22, 2012create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)
My requirement is to return
id key
1 10
1 10
because both their id and key are same
PL/SQL :: Returning Same Value Set In A Group
Jan 24, 2013I have the below requirement,
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
PL/SQL :: Returning A Single Row
Jun 5, 2013I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order
select employee_id from
(select employee_id
,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest
from employees
group by employee_id;
but this seems to return more than one entrty:
table date below:
drop table employee_list;
drop table employee_historic;
[code]...
What is the best way to return a single row, if the primary key is not available / applicable.
Merge Over Multiple Schema
Aug 31, 2007Procedure 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.
SQL & PL/SQL :: Merge Into Table Name Using Cursor
Jun 28, 2010I 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]......
SQL & PL/SQL :: Merge Statement In Oracle
Jun 22, 2012how to update or insert another (third table ) table with merge statement
View 6 Replies View RelatedSQL & PL/SQL :: Merge Multiple Rows Into One Row
Apr 7, 2011How 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
SQL & PL/SQL :: Merge Rows On View
Aug 19, 2012I 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]....
SQL & PL/SQL :: Merge And Add Rows Values Into One?
Jun 4, 2012CREATE 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.
SQL & PL/SQL :: Executing Merge Statement?
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?
Forms :: Using Merge Statement In 6i?
May 9, 2011in 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.
SQL & PL/SQL :: Match And Merge Records?
Aug 28, 2012I 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
SQL & PL/SQL :: DML Statements - How Does Merge Works
Jul 23, 2013My 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 RelatedSQL & PL/SQL :: Merge Is Not Working Properly?
Apr 23, 2013I 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].....
SQL & PL/SQL :: Merge Columns Of 2 Tables?
Apr 14, 2011i 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
SQL & PL/SQL :: Merge Two Table With Distinct Value
Sep 4, 2012select * 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?
SQL & PL/SQL :: Using Table Type With Merge?
May 31, 2007Can 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
SQL & PL/SQL :: Merge Vs Cursor Processing
Apr 2, 2010My 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.