oracle call stored procedure inside select

Question

I'm working on a query (a SELECT) and I need to insert the result of this one in a table. Before doing the insert I have some checking to do, and if all columns are valid, I will do the insert.

The checking is done in a stored procedure. The same procedure is used somewhere else too. So I'm thinking using the same procedure to do my checks.

The procedure does the checkings and insert the values is all OK.

I tryied to call the procedure inside my SELECT but it does not works.

SELECT field1, field2, myproc(field1, field2)

from MYTABLE.

This kind of code does not works.

I think it can be done using a cursor, but I would like to avoid the cursors. I'm looking for the easiest solution.

Anybody, any idea ?

Answer 1

use a PL/SQL loop:

BEGIN
   FOR c IN (SELECT field1, field2 FROM mytable) LOOP
       my_proc(c.field1, c.field2);
   END LOOP;
END;