Home » SQL & PL/SQL » SQL & PL/SQL » Loop SP out cursor inside a stored procedure (11.0.2.10)
Loop SP out cursor inside a stored procedure [message #662707] Wed, 10 May 2017 02:26 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi All,

I have a situation where I need to call a stored procedure that returns a ref cursor from another stored procedure and loop over the returned cursor to insert with a condition that prevents duplication of a certain key.

My example is as follows:

create table temp_src_int
(
  id number,
  v1 varchar2(10),
  v2 varchar2(10),
  ddate date
);

insert all 
  into TEMP_SRC_INT values (1,10,20,sysdate)
  into TEMP_SRC_INT values (2,20,30,sysdate)
  into TEMP_SRC_INT values (3,110,210,sysdate)
  into TEMP_SRC_INT values (4,201,301,sysdate)
  into TEMP_SRC_INT values (5,170,910,sysdate)
  into TEMP_SRC_INT values (6,801,101,sysdate)
select * from dual;


create table temp_dst_int
(
  id number,
  v1 varchar2(10),
  v2 varchar2(10),
  ddate date
);

insert all 
  into TEMP_DST_INT values (1,10,20,sysdate)
  into TEMP_DST_INT values (3,110,210,sysdate)
select * from dual;

create or replace PROCEDURE P_get_src_test
  ( 
     mcur out sys_refcursor
  )
  IS
  
  BEGIN

  open mcur for
    select * from TEMP_SRC_INT where id <> 4;
END P_get_src_test;

create or replace PROCEDURE P_insert_dst_test
  ( 
    S_APP_USER    IN VARCHAR2
  )
  IS
   i_counter number :=1;
   MCUR SYS_REFCURSOR;
  
  BEGIN

    P_get_src_test(MCUR);

    FOR MREC IN MCUR     --<-- problem 1: how to loop over mcur
    LOOP 
      insert into TEMP_DST_INT(id, v1, v2, ddate) 
      values (MREC.id, MREC.v1, MREC.v2,MREC.ddate);
      where id <> mrec.id;  --<----  problem 2: how to add a condition to prevent duplication of id value

    END LOOP;
    
END P_insert_dst_test;


I have a problem in looping over the cursor and another problem regarding the way I refer to cursor field to compare them to table field in order to avoid duplication.

Many thanks,
Ferro
Re: Loop SP out cursor inside a stored procedure [message #662709 is a reply to message #662707] Wed, 10 May 2017 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Problem 1: use a loop on FETCH call.
Problem 2: use INSERT SELECT (where not exists) not INSERT VALUES but to prevent from duplication there is a thing named UNIQUE constraint.

[Updated on: Wed, 10 May 2017 02:36]

Report message to a moderator

Re: Loop SP out cursor inside a stored procedure [message #662710 is a reply to message #662709] Wed, 10 May 2017 02:39 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Michel,

Thanks for your reply

Quote:
Problem 1: use a loop on FETCH call.
But this way i will have to declare a variable for each cursor field to fetch, is there any other way?

Thanks,
Ferro
Re: Loop SP out cursor inside a stored procedure [message #662711 is a reply to message #662710] Wed, 10 May 2017 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can declare just one record variable but you need a strong ref cursor declaration:
SQL> declare
  2    type deptrefcursor is ref cursor return dept%rowtype;
  3    rc deptrefcursor;
  4    rec dept%rowtype;
  5  begin
  6    open rc for select * from dept;
  7    loop
  8      fetch rc into rec;
  9      exit when rc%notfound;
 10      dbms_output.put_line('dept: '||rec.deptno||' '||rec.dname);
 11    end loop;
 12    close rc;
 13  end;
 14  /
dept: 10 ACCOUNTING
dept: 20 RESEARCH
dept: 30 SALES
dept: 40 OPERATIONS

PL/SQL procedure successfully completed.
[Edit: add close to proper code]

[Updated on: Wed, 10 May 2017 02:53]

Report message to a moderator

Re: Loop SP out cursor inside a stored procedure [message #662712 is a reply to message #662711] Wed, 10 May 2017 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But instead of looping on the cursor it should be better to bulk collect it:
SQL> declare
  2    type deptrefcursor is ref cursor return dept%rowtype;
  3    type deptarray is table of dept%rowtype;
  4    rc deptrefcursor;
  5    res deptarray;
  6  begin
  7    open rc for select * from dept;
  8    fetch rc bulk collect into res;
  9    for i in 1..res.count loop
 10      dbms_output.put_line('dept: '||res(i).deptno||' '||res(i).dname);
 11    end loop;
 12    close rc;
 13  end;
 14  /
dept: 10 ACCOUNTING
dept: 20 RESEARCH
dept: 30 SALES
dept: 40 OPERATIONS

PL/SQL procedure successfully completed.
Re: Loop SP out cursor inside a stored procedure [message #662713 is a reply to message #662712] Wed, 10 May 2017 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So to complete my example using a UNIQUE constraint to avoid duplication (here DEPT primary key):
SQL> select * from dept;
                                            DEPTNO DNAME          LOC
-------------------------------------------------- -------------- -------------
                                                10 ACCOUNTING     NEW YORK
                                                20 RESEARCH       DALLAS
                                                30 SALES          CHICAGO
                                                40 OPERATIONS     BOSTON

4 rows selected.

SQL> declare
  2    type deptrefcursor is ref cursor return dept%rowtype;
  3    type deptarray is table of dept%rowtype;
  4    rc deptrefcursor;
  5    res deptarray;
  6    x exception;
  7    pragma exception_init (x, -24381);
  8  begin
  9    open rc for select * from dept;
 10    fetch rc bulk collect into res;
 11    close rc;
 12    forall  i in res.first..res.last save exceptions
 13      insert into dept (deptno, dname) values (res(i).deptno+10, res(i).dname);
 14    exception
 15      when x then -- optional handling to show how it works could be just NULL to ignore
 16        for i in 1..sql%bulk_exceptions.count loop
 17          dbms_output.put_line('Exception inserting row '||sql%bulk_exceptions(i).error_index||
 18                               ': '||sqlerrm(-1*sql%bulk_exceptions(i).error_code));
 19        end loop;
 20  end;
 21  /
Exception inserting row 1: ORA-00001: unique constraint (.) violated
Exception inserting row 2: ORA-00001: unique constraint (.) violated
Exception inserting row 3: ORA-00001: unique constraint (.) violated

PL/SQL procedure successfully completed.

SQL> select * from dept;
                                            DEPTNO DNAME          LOC
-------------------------------------------------- -------------- -------------
                                                10 ACCOUNTING     NEW YORK
                                                20 RESEARCH       DALLAS
                                                30 SALES          CHICAGO
                                                40 OPERATIONS     BOSTON
                                                50 OPERATIONS

5 rows selected.
Re: Loop SP out cursor inside a stored procedure [message #662716 is a reply to message #662713] Wed, 10 May 2017 03:27 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks a million Michel,

In your last example, assume that your line:
open rc for select * from dept;
-- is actually
open rc for select d1.1f1, d2.f2 from dept d1, dept2 d2 where d1.id = d2.id; -- <----
In this case I will have to declare a new user object type:
CREATE OR REPLACE TYPE OBJ_MycurType AS OBJECT
(

   f1 NUMBER,
   f2 NUMBER    
);
and replace:
type deptrefcursor is ref cursor return dept%rowtype;
--with
type deptrefcursor is ref cursor return OBJ_MycurType%rowtype;
Is that correct?

Thanks,
Ferro
Re: Loop SP out cursor inside a stored procedure [message #662721 is a reply to message #662716] Wed, 10 May 2017 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
%ROWTYPE means to get a record type definition from a table description, so can't be applied to an object type.
In addition, to declare it in ref cursor the base type must be a record:
SQL> declare
  2    TYPE OBJ_MycurType is record
  3    (
  4       f1 NUMBER,
  5       f2 NUMBER
  6    );
  7    type deptrefcursor is ref cursor return OBJ_MycurType;
  8    type deptarray is table of OBJ_MycurType;
  9    rc deptrefcursor;
 10    res deptarray;
 11  begin
 12    open rc for select d1.deptno f1, d2.deptno from dept d1, dept d2 where d1.deptno = d2.deptno;
 13    fetch rc bulk collect into res;
 14    for i in 1..res.count loop
 15      dbms_output.put_line('dept: '||res(i).f1||' '||res(i).f2);
 16    end loop;
 17    close rc;
 18  end;
 19  /
dept: 10 10
dept: 20 20
dept: 30 30
dept: 40 40

PL/SQL procedure successfully completed.
They could be types declared in a package specification you can refer in your procedures.

[Edit: typo]

[Updated on: Thu, 11 May 2017 01:20]

Report message to a moderator

Re: Loop SP out cursor inside a stored procedure [message #662749 is a reply to message #662721] Thu, 11 May 2017 06:17 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks a lot Michel this is another post I learned from.

For the sake of completing the example with Where Does Not Exists part, here is my full example:
CREATE OR REPLACE PACKAGE test_pack AS


 TYPE MycurType is record
      (
        id number,
        v1 varchar2(10),
        v2 varchar2(10),
        ddate date
      );
  
  type Myrefcursor is ref cursor return MycurType;
  type Myrefarray is table of MycurType;


  PROCEDURE P_get_src_test
  ( 
     mcur out sys_refcursor
  );
  
  PROCEDURE P_insert_dst_test
  ( 
    S_APP_USER    IN VARCHAR2
  );  


END test_pack;

CREATE OR REPLACE PACKAGE BODY test_pack AS

  
  PROCEDURE P_get_src_test
    ( 
       mcur out sys_refcursor
    )
    IS
    
    BEGIN
  
    open mcur for
      select * from TEMP_SRC_INT where id <> 4;
  END P_get_src_test;


  PROCEDURE P_insert_dst_test
  ( 
    S_APP_USER    IN VARCHAR2
  )
  IS
   i_counter number :=1;
   rc Myrefcursor;
   res Myrefarray;
  
  BEGIN

   P_get_src_test(rc);

   fetch rc bulk collect into res;
   for i in 1..res.count loop
    INSERT INTO TEMP_DST_INT
    SELECT res(i).id, res(i).v1, res(i).v2, res(i).ddate
      FROM dual 
     WHERE NOT EXISTS (SELECT id 
                         FROM TEMP_DST_INT
                        WHERE id = res(i).id
                      );    
   end loop;
   close rc;

  END P_insert_dst_test;

END test_pack;

Thanks a lot,
Ferro

[Updated on: Thu, 11 May 2017 06:18]

Report message to a moderator

Re: Loop SP out cursor inside a stored procedure [message #662760 is a reply to message #662749] Thu, 11 May 2017 07:43 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: SP2-0552: Bind variable "LIMIT" not declared.
Next Topic: Using REGEXP_LIKE
Goto Forum:
  


Current Time: Thu Mar 28 07:18:33 CDT 2024