oracle call procedure in a cursor loop

Question

I have the following SELECT statement:

SELECT AUDITORID 
        FROM SOL.GROUPS WHERE STATUS = 1;

I need for each AUDITORID that returns the statemnet to call the procedure INSERT_LD_PER_AUDITOR (VAUDITORID)

how to do that?

Do you mind sharing what insert_ld_per_auditor does? I suspect you would be much better off changing the procedure to execute on a whole data set at once instead of individual "auditors". That way, you can avoid cursors all together, which would be much better. — sstan
– sstan, Commented Oct 7, 2016 at 12:18

sstan · Accepted Answer · 2016-10-07 13:15:24Z

4

If you're sure you want loop you can use:

begin
  for x in (SELECT AUDITORID FROM SOL.GROUPS WHERE STATUS = 1) loop
      INSERT_LD_PER_AUDITOR(x.AUDITORID);
  end loop;
end;

but as sstan wrote it is probably better to refactor procedure to process all data.

edited Oct 7, 2016 at 13:15

sstan

36.8k7 gold badges54 silver badges72 bronze badges

answered Oct 7, 2016 at 13:08

Kacper

4,8182 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Piankov · Accepted Answer · 2016-10-07 14:50:25Z

0

You cant call procedure via SQL statement And you have at least 2 ways how to resolve it. 1) Create pl_sql function and set call procedure into function

create or replace function F_INSERT_LD_PER_AUDITOR (VAUDITORID NUMBER) 
   return NUMBER
is 
begin 
  NSERT_LD_PER_AUDITOR (VAUDITORID);
  return 0;
end;
/

and call it function

SELECT F_INSERT_LD_PER_AUDITOR(AUDITORID)
    FROM SOL.GROUPS WHERE STATUS = 1;

2) wirte pl/sql and use LOOP as in previous comment

answered Oct 7, 2016 at 14:50

Michael Piankov

1,9971 gold badge11 silver badges20 bronze badges

Collectives™ on Stack Overflow

oracle call procedure in a cursor loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related