oracle call stored procedure inside select

use a PL/SQL loop:

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

SQL can only use functions in the projection: it needs something which returns a value. So you are going to have to write some functions. That's the bad news. The good news is, you can re-use all the investement in your stored procedures.

Here is a procedure which enforces a completely just business rule: only managers can have a high salary.

SQL> create or replace procedure salary_rule  2      ( p_sal in emp.sal%type  3        , p_job in emp.job%type)  4  is  5      x_sal exception;  6  begin  7      if p_sal > 4999 and p_job != 'MANAGER' then  8          raise x_sal;  9      end if; 10  exception 11      when x_sal then 12          raise_application_error(-20000, 'Only managers can earn that much!'); 13  end salary_rule; 14  /Procedure created.SQL>

Because it is a procedure we cannot use it in a SELECT statement; we need to wrap it in a function. This function just calls the stored procedure. It returns the input parameter P_SAL. In other words, if the salary is valid (according to the rules) it will be returned. Otherwise the function will re-hurl the stored procedure's exception.

SQL> create or replace function validate_salary  2      ( p_sal in emp.sal%type  3        , p_job in emp.job%type)  4      return emp.sal%type  5  is  6  begin  7      salary_rule(p_sal, p_job);  8      return p_sal;  9  end validate_salary; 10  /Function created.SQL>

The function has to return a value which we want to insert into our table. It cannot return some meaningless phrase like "salary okay". Also, if we want to validate two columns we need a separate function for each, even if there is a relationship between them and we use the same stored procedure to validate them both. Good use for the DETERMINISTIC keyword.

Here's the test: plumbers cannot earn 5000 spondulicks ....

SQL> insert into emp  2      (empno  3      , ename  4      , job  5      , deptno  6      , sal )  7  select  8      emp_seq.nextval  9      , 'HALL' 10      , 'PLUMBER' 11      , 60 12      , validate_salary(5000, 'PLUMBER') 13  from dual 14  /    , validate_salary(5000, 'PLUMBER')      *ERROR at line 12:ORA-20000: Only managers can earn that much!ORA-06512: at "APC.SALARY_RULE", line 12ORA-06512: at "APC.VALIDATE_SALARY", line 7SQL>

... but managers can (because they deserve it):

SQL> insert into emp  2      (empno  3      , ename  4      , job  5      , deptno  6      , sal )  7  select  8      emp_seq.nextval  9      , 'HALL' 10      , 'MANAGER' 11      , 60 12      , validate_salary(5000, 'MANAGER') 13  from dual 14  /1 row created.SQL>

Note that the hurled exception is crucial to this working. We cannot write some bizarre IF SALARY IS VALID THEN INSERT logic in our SQL statement. So, if the stored procedure doesn't raise an exception but instead returns some wimpy error status the wrapping function will have to interpret the output and hurl its own exception.

You can't use stored procedures in SELECT statement. You can use functions for that.

As I understand you are calling insert in your SP, so take into consideration that you can's use INSERT/UPDATE in function body. But if you need to do some checks you can use function which will do that checks and use that function in your select statement.