Home » SQL & PL/SQL » SQL & PL/SQL » Returning into clause for insert selectstatements (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Returning into clause for insert selectstatements [message #676398] Fri, 07 June 2019 07:28 Go to next message
saipradyumn
Messages: 400
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 #676402 is a reply to message #676398] Fri, 07 June 2019 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This unfortunately does not work (is not implemented).
The syntax would be (here for a single row insert):
SQL> DECLARE
  2    type t_id is table of t1.description%TYPE;
  3    l_ids t_id;
  4    i pls_integer;
  5  BEGIN
  6    INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR')
  7    RETURNING (SELECT CURRMVNAME FROM V$MVREFRESH WHERE CURRMVNAME ='MV_NAME')
  8    BULK COLLECT INTO l_ids;
  9    COMMIT;
 10    i := l_ids.first;
 11    loop
 12      exit when i is null;
 13      DBMS_OUTPUT.put_line('ID=' || l_ids(i));
 14      i := l_ids.next(i);
 15    end loop;
 16  END;
 17  /
ID=

PL/SQL procedure successfully completed.
But if you do this with an INSERT SELECT, you will get an error (using here ID instead of you subquery to show it does not come from it):
SQL> DECLARE
  2    type t_id is table of t1.description%TYPE;
  3    l_ids t_id;
  4    i pls_integer;
  5  BEGIN
  6    INSERT INTO t1  select  *  from  MV_NAME
  7    RETURNING id
  8    BULK COLLECT INTO l_ids;
  9    COMMIT;
 10    i := l_ids.first;
 11    loop
 12      exit when i is null;
 13      DBMS_OUTPUT.put_line('ID=' || l_ids(i));
 14      i := l_ids.next(i);
 15    end loop;
 16  END;
 17  /
  RETURNING id
            *
ERROR at line 7:
ORA-06550: line 7, column 13:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
You have to use a temporary storage if you want to do this (as I have no MV_NAME I use a query on DUAL):
SQL> DECLARE
  2    type t_id is table of t1.description%TYPE;
  3    l_ids t_id;
  4    i pls_integer;
  5    type t_rows is table of t1%rowtype;
  6    l_rows t_rows;
  7  BEGIN
  8    select * bulk collect into l_rows
  9    from (select level+10, to_char(level) from dual connect by level<=3);
 10
 11    i := l_rows.first;
 12    forall i in l_rows.first..l_rows.last
 13      INSERT INTO t1 values (l_rows(i).id, l_rows(i).description)
 14      RETURNING (SELECT CURRMVNAME FROM V$MVREFRESH WHERE CURRMVNAME ='MV_NAME')
 15      BULK COLLECT INTO l_ids;
 16    COMMIT;
 17
 18    i := l_ids.first;
 19    loop
 20      exit when i is null;
 21      DBMS_OUTPUT.put_line('ID=' || l_ids(i));
 22      i := l_ids.next(i);
 23    end loop;
 24  END;
 25  /
ID=
ID=
ID=

PL/SQL procedure successfully completed.
But now the question is: why don't you query V$MVREFRESH before and/or after your INSERT SELECT instead of inside it?

Re: Returning into clause for insert selectstatements [message #676453 is a reply to message #676402] Mon, 10 June 2019 01:54 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks for your detailed explanation.

But unfortunately the work around also will not work in my case.

Here my problem is when Materialized view refresh is happening,there are certain jobs which gets data from the same MV and insert into some temporary tables for further processing.

But few times some of the records are missing for processing due to MV Refresh.

I want to check when we are copying the data from mv to temp table ,
that refresh was happening or not so that i can wait for some time until the it gets the required data.


Thanks
SaiPradyumn
Re: Returning into clause for insert selectstatements [message #676454 is a reply to message #676453] Mon, 10 June 2019 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


You can:
1/ get the refresh date before and after your INSERT and if different then do it again
2/ gf you are using DBMS_SNAPSHOT/DBMS_MVIEW.REFRESH to refresh, set atomic_refresh to TRUE.

Re: Returning into clause for insert selectstatements [message #676455 is a reply to message #676454] Mon, 10 June 2019 02:54 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
We have one particular job which will run at same time for all different regions.

This job will do the following two activities

1) MV Refresh (By using dbms_mview.refresh and auto fresh false)
2) insert into the temp tables (only the region specific data)

Every refresh requires the latest data So I can't go with Date for refresh.Because one region MV Refresh job,Other region insertion was effecting

Please advise me the alternate solution

Thanks
SaPradyumn

Re: Returning into clause for insert selectstatements [message #676456 is a reply to message #676455] Mon, 10 June 2019 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In fact what you need is that a region cannot execute the job when another one is executing it.
So use a custom lock.

Re: Returning into clause for insert selectstatements [message #676767 is a reply to message #676456] Mon, 15 July 2019 00:26 Go to previous message
saipradyumn
Messages: 400
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
Previous Topic: Different Values of RAWTOHEX in Oracle 12c and 11g
Next Topic: MV Refresh On Commit
Goto Forum:
  


Current Time: Thu Nov 21 23:44:55 CST 2019