1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Returning into clause for insert select statements

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Jun 7, 2019.

Tags:
  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    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 :
    Code (Text):


    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

    Code (Text):


    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
    Revathi
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,685
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Thanks for your interest to resolve the issue .

    Finally WE ca say that RETURNING Clause will not give the support for INSERT SELECT Statement.

    Thanks
    Revathi.T