Forms :: Using Merge Statement In 6i?

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


ADVERTISEMENT

Forms :: Getting Error While Using MERGE Statement In Application

May 19, 2010

I am using MERGE statement first time in my application but I am facing error.

begin
merge into store.comm_dept a
using factory_data.comm_dept b
on (a.cd_dcode = b.cd_dcode)
when matched then
[code]........

ERROR IS: encountered the symbol 'INTO' when expecting on of the following:
:=.(@%;

View 7 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

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

View 5 Replies View Related

SQL & PL/SQL :: Merge Statement In Oracle

Jun 22, 2012

how to update or insert another (third table ) table with merge statement

View 6 Replies View Related

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?

View 3 Replies View Related

SQL & PL/SQL :: Merge Statement Required With Logic

Jul 10, 2013

Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records

Correct scenario records

F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3

Wrong scenario records

F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3

I want to write a Merge statement to update the negative values into 1.

View 6 Replies View Related

SQL & PL/SQL :: Merge Statement - Intermediate Commit?

Dec 12, 2012

I am using Merge statement to copy data from one table to other,

merge into tabA a
using tabB b
on a.id = b.id
when match
update (
)
when not matched
(insert )

This is working all fine, as SQL, but when there is large volume, it blows out as there are intermediate commits for this ?

View 3 Replies View Related

PL/SQL :: Merge Statement - Update Only When There Is Difference?

Feb 7, 2013

I have two tables have almost the same columns, how can I use merge statement to update the target table only when there is difference between source and target table. Is there any easier way not compare each column one by one? I am using Oracle 11.2

Here is the MERGE statement:

Merge into tb_trgt trgt using tb_src src
on (src.id = trgt.id)
when not matched then insert (trgt.id, trgt.nm, trgt.addr) values (src.id, src.nm, src.nm)
when matched then update set trgt.nm = src.nm, trgt.addr = src.addr
where trgt.nm <> src.nm or trgt.addr <> src.addr
;

Is there any easier way to specify the where clause in the NOT MATCHED? I don't want compare each column of the tables. Since I may have many columns in the tables.

View 3 Replies View Related

PL/SQL :: Merge Statement On Single Table

Oct 29, 2012

{code{

i want to apply merge stmt on single table.

CREATE TABLE TEST11(TNO NUMBER(5), TVAL VARCHAR2(100), TID VARCHAR2(10));
INSERT INTO TEST11 VALUES(1,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(2,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(3,'VIJAYA','TEST');
INSERT INTO TEST11 VALUES(4,'VIJAYA','');
INSERT INTO TEST11 VALUES(5,'VIJAYA','');

[Code]....

My requiremen is if record is exists then i wan to update some value, if record not existes the i wan to insert new record

View 3 Replies View Related

PL/SQL :: Merge Statement - Delete Clause?

Mar 14, 2013

I 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?

View 14 Replies View Related

Performance Tuning :: How To Use Index On (ON In MERGE Statement)

Apr 6, 2011

mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.

PK of mbr : mbr_id
PK of member : mbr_id

other columns are not PK, and have no index on it.

I'm wondering why the statment doesn't use index while ssn and citi_no have index.

MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
UPDATE SET t.asis_mbr_id = a.mbr_id
where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)

View 19 Replies View Related

SQL & PL/SQL :: Calling Pipelined Function In Merge Statement?

Dec 2, 2010

I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.

the pipelined function is:

FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor,
pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def)
RETURN CRCLI_AA
PIPELINED
PARALLEL_ENABLE(PARTITION pi_source_data BY ANY)
IS

[code].....

the error is pointing to the TABLE function in the USING clause of the merge statement.

View 7 Replies View Related

SQL & PL/SQL :: Can Have All Rows In A Table Updated At Once In Merge Statement

Aug 10, 2010

Can I have all the rows in a table updated at once in the merge statement?

MERGE INTO providermaster a
using
(
SELECT * FROM PROVIDERMASTER@INGEST) b
ON (b.MASTERPROVIDERID=a.MASTERPROVIDERID)
WHEN MATCHED THEN

UPDATE ....... I want to update all the other rows at once..

View 24 Replies View Related

SQL & PL/SQL :: ORA-30926 Occurs Only With PARALLEL Hint On MERGE Statement

Jun 26, 2013

I am writing below MERGE statement. In this cardinality between table_a and table_b is 1:2. I.e. each record in table_b corresponds to 2 records in table_a based on columns in ON clause.

Well this query throws below error.

----Error---

ORA-12801: error signaled in parallel query server P011

ORA-30926: unable to get a stable set of rows in the source tables

However, the same statement executes successfully when PARALLEL hint is removed altogether. (There are no duplicates in table_b based on unit,group,loc columns.)

-----Query--------

MERGE /*+ PARALLEL(8) */
INTO table_a a
USING table_b b
ON (a.unit = b.unit AND a.group = b.group AND a.loc = b.loc)

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Merge Statement In Procedure - Internal Error Code

Feb 9, 2011

I have a merge statement in one procedure , this job runs on daily . It is running successfully since Jan-19-2010 and giving the below error now.

ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgcur_9], [4224596], [1],
[4294967250], [2], [], [], []

It is giving error in the beginning of the MERGE statement.

View 11 Replies View Related

PL/SQL :: How To Implement Exception Handling On MERGE Statement In Oracle10g

Jul 19, 2012

How to handle the exception on below MERGE statement?

MERGE INTO COMM_EXSTS_COMIT_AGGR TARGET

USING
(
select  * from   ABC ) SRC
ON
(
SRC.COMMITMENT_ID = TARGET.COMMITMENT_ID
)
WHEN MATCHED THEN
UPDATE
WHEN NOT MATCHED THEN
INSERT ;

View 4 Replies View Related

SQL & PL/SQL :: Merge Statement - Unable To Get Stable Set Of Rows In Source Tables

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

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

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

View 2 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

Oct 31, 2011

I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,

[code].....

View 39 Replies View Related

Forms :: Case Statement In 6i

Mar 24, 2011

When I tried to have a Cursor with SELECT CASE statement in Forms 6i , it is not working. But the same query is working in SQL PLUS . We cannot use case in Forms 6i ?

View 5 Replies View Related

Forms :: Update Statement On Button?

Apr 16, 2010

I try to apply update statement on the button for the specific records, the command does executes if in the end i write execute_query it always asks for do u wanna save the records, yes or no. I want to remove that message. Also if i write commit it does remove that message but everytime i press the button it updates the old record fine but also adds one more entry for the same record.

View 5 Replies View Related

Forms :: Windows Won't Allow To Execute SQL Statement

Oct 25, 2011

reconciliation logic using Oracle

Version

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Current written logic for reconciliation:

1. Load data from source_a in a staging table using date filter

2. Load data from a file (Source_B) in temp table

3. Algo for reconciliation:

fetch value from source_B and if an entry exists in source_a then match say 10 columns if they match update reconciliation_oke = TRUE

there is an and written for testing all the 10 columns There is report generated out this which shows non matched columns and the entries which are missing....Now the requirement is to modify the logic in a way which shows which all columns are mismatched on the report in case present.

Since there are around 10 thousand records which would be reconciled on a daily basis, performance also needs to be taken care of...I guess i would be required to use PL/SQl tables...

View 3 Replies View Related

Forms :: How To Insert And Update With Single Statement

Mar 5, 2012

I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )

example of scenario...

date s1 s2 s3

in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.

View 8 Replies View Related

Forms :: How To Handle Multi Row Select Statement

Feb 3, 2011

I am doing simple project, where i need to check date(form which we give in run time) with database column date.problem i have written the code for program now i found that select statement is return multi rows.

code is

when_button_pressed trigger.

declare
fdcode dmas.dcode%type;
fcodno dmas.codno%type;
famtrs dmas.amtrs%type;
fdate donres.rdate%type;
begin
[code]......

View 4 Replies View Related

Forms :: Incorrect Result On Assignment Statement?

Mar 3, 2011

When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following

"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"

The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.

View 5 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

Forms :: Wait Event During Execution Simple Select Statement

Jul 19, 2013

Yesterday i got wait event when executed simple select from table.This select was like:

SELECT emp_number from employer where subs_id = 111

I got one row, select is very fast.In our Core Bank System we have package with function which returns such information. I tested this select on test DB, and nothing wrong. But when I executed such select and package on Production DB, DB Admin saw that 88 sessions waits when my session release the resource. But what can happen, it was simple select? I used PL/SQL developer to get information from table:

1) SELECT emp_number from employer where subs_id = 111 then
2) Package with this function

Another users used Oracle Forms screen to execute package. How simple select statement could stop all DB?

BANNER
1Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
2PL/SQL Release 10.2.0.5.0 - Production
3CORE 10.2.0.5.0 Production
4TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
5NLSRTL Version 10.2.0.5.0 - Production
[code]...

Forgot to say that after succeful execution on Prod DB I disconnected, and in EM my session was INACTIVE.

View 3 Replies View Related

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 View Related

SQL & PL/SQL :: Merge Two Table Into One

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

SQL & PL/SQL :: Merge Rows

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







Copyrights 2005-15 www.BigResource.com, All rights reserved