Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support multi-row INSERT .. RETURNING for Oracle using PL/SQL FORALL .. INSERT .. RETURNING .. BULK COLLECT INTO #5863

Closed
lukaseder opened this issue Feb 10, 2017 · 2 comments

Comments

Projects
None yet
1 participant
@lukaseder
Copy link
Member

commented Feb 10, 2017

This is how we can INSERT .. RETURNING in Oracle:

SET SERVEROUTPUT ON
DECLARE
  -- Input bind value(s)
  in_c2_p sys.ora_mining_number_nt := sys.ora_mining_number_nt(12, 13);
  
  in_c2  dbms_sql.varchar2_table;
  out_c1 dbms_sql.number_table;
  out_c2 dbms_sql.varchar2_table;
  
  cur_c1 sys_refcursor;
  cur_c2 sys_refcursor;
BEGIN
  FOR i IN 1 .. in_c2_p.COUNT LOOP
    in_c2(i) := in_c2_p(i);
  END LOOP;
  
  -- The FORALL loop inserts individual rows and fetches all results
  FORALL i IN 1 .. in_c2.COUNT
    INSERT INTO t_identity_pk (val)
    VALUES (in_c2(i))
    RETURNING id, val BULK COLLECT INTO out_c1, out_c2;
  
  -- Output bind value(s)
  OPEN cur_c1 FOR SELECT * FROM TABLE (out_c1);
  dbms_sql.return_result(cur_c1);
  
  OPEN cur_c2 FOR SELECT * FROM TABLE (out_c2);
  dbms_sql.return_result(cur_c2);
END;
/

Some more inspiration here: http://www.oracle-developer.net/display.php?id=413

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented Sep 20, 2017

This needs much more research, also in terms of performance. We'll postpone this to 3.11

@lukaseder

This comment has been minimized.

Copy link
Member Author

commented May 2, 2018

This emulation has already been implemented for UPDATE and DELETE through #5191. Should be an easy fix for INSERT

lukaseder added a commit that referenced this issue May 2, 2018

[#5863] [#7452] Support multi-row INSERT .. RETURNING for Oracle usin…
…g PL/SQL FORALL .. INSERT .. RETURNING .. BULK COLLECT INTO

lukaseder added a commit that referenced this issue May 2, 2018

@lukaseder lukaseder added the R: Fixed label May 2, 2018

@lukaseder lukaseder closed this May 2, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.