oracle call stored procedure inside select

Question

Hi everybody.

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 ?

Thanks alot. C.C.

Answer 1

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.

Answer 2

Hi CC,

use a PL/SQL loop:

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

Answer 3

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 12
ORA-06512: at "APC.VALIDATE_SALARY", line 7


SQL>

... 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.