Returning into clause for insert selectstatements [message #676398] |
Fri, 07 June 2019 07:28 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I want to capture the some back ground activity when one insertion is going on with the help of insert select statement with the help of
RETURNING INTO Clause .
Code Snippet :
DROP TABLE t1;
DROP TABLE t;
DROP SEQUENCE t1_seq;
CREATE TABLE t1 (
id NUMBER(10),
description VARCHAR2(50),
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE T AS
SELECT * FROM t1 WHERE 1=2;
CREATE SEQUENCE t1_seq;
INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE');
INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO');
INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE');
COMMIT;
SET SERVEROUTPUT ON
DECLARE
l_id t1.description%TYPE;
BEGIN
INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
RETURNING (SELECT CURRMVNAME FROM V$MVREFRESH WHERE CURRMVNAME ='MV_NAME') INTO l_id;
COMMIT;
DBMS_OUTPUT.put_line('ID=' || l_id);
END;
/
Above code was working fine . But our requirement was INSERT SELECT should also give the support for this RETURNING Clause
INSERT INTO t1 select * from MV_NAME
RETURNING (SELECT CURRMVNAME FROM V$MVREFRESH WHERE CURRMVNAME ='MV_NAME') INTO l_id;
This is not working.
Can we capture the same mv refresh was going on or not when we are performing the DML operations from the same table.
Thanks
SaiPradyumn
|
|
|
|
|
|
|
|
Re: Returning into clause for insert selectstatements [message #676767 is a reply to message #676456] |
Mon, 15 July 2019 00:26 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
In order to resolve the parallel issue we had applied following two concepts.
1) Acquired the lock on the table with the help FOR UPDATE wait clause. Created a new table which contains names of the all MV.
Before going the refresh of that MV acquiring the lock only on that row .
2) Considered these two activities as single transaction with PRAGMA_AUTONOMUS (Materialized view refresh & Copying into temp table from Mv)
After implementing the above two changes to the existing procedure , I am bale to achieve the parallelism
Thanks for your guidance .
Thanks
SaiPradyunb
|
|
|