Search code examples
sqloraclestored-procedures

Why can't we call procedure from sql

I know we can call the function from SQL if it doesn't contain out parameter or DML(except autonomous). But we can't call the procedure from SQL in any condition. What is the reason for it? Why can't we call the procedure from SQL? Any specific reason.

Solution

  • You can:

    call dbms_output.put_line('Hello')

    CALL is part of the SQL language.

    Or we can embed a procedure in an inline function:

    with function f (p varchar2)
        return varchar2
    as
    begin
        dbms_output.put_line('Hello');
        return p;
    end f;
select f('Demo')
from   dual

    If you mean a SELECT statement specifically, I can't see how you expect that to work. What result set would you expect a query like this to return?

    select dbms_output.put_line('Hello')
from   dual

    or

    select dbms_stats.gather_table_stats(user, table_name)
from   user_tables

    This isn't an arbitrary restriction by some standards committee. It just doesn't make any sense semantically.