CODEWITHSUNDEEP
sqloracle-databasestored-procedures
user9892866
user9892866

Reputation:

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.

Upvotes: 2

Views: 1687

Answers (5)

Sameer Bipin Mehta
Sameer Bipin Mehta

Reputation: 1

Basically, a FUNCTION is used to compute and return the result which is ideal to be invoked from a SQL Query statement or from another FUNCTION or Stored-Proc. Unlike a FUNCTION, a Stored-Proc is used to execute logic/process blocks that may require transaction controls and parameter handling that is not suited for a plain SQL but for a client/application.

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 2

krokodilko
krokodilko

Reputation: 36107

The reason is that the SQL ANSII standard specifies that only functions can be used in the SQL query.
ISO committee members did not define the use of procedures in SQL queries.

Upvotes: 4

Hilarion
Hilarion

Reputation: 870

I'm assuming, that you are asking about calling procedures from within other SQL statements (not just call a procedure on its own, which is obviously possible).

Why? That's a matter of opinion, and you would have to ask Oracle DB architects for a real cause.

It would seem, that introducing procedure calls into all possible SQL statements, would bring both syntax and implementation complexity, while not necessarily bringing much more value. Usually there are alternatives, which are not much harder to use, while allowing the same outcome.

In case of a query (a SELECT statement), the result should be a data set, and no changes in the database state (data or structure) should be done. A PL/SQL procedure does not return a data set, and can change the database state.

If you are in a situation, where the procedure call is needed to prepare the data, you'd like to query, then you have the possibility to call the procedure first, and then query the database.

You can also write a procedure, which will have an output parameter of a cursor reference, which will effectively give you a query result. (For an ad hoc case, you could use parameterized anonymous PL/SQL block.)

You can also write a tabular function, where you can do complex data processing, using PL/SQL, and return a data set. Such function can be used in a query.

If you are asking also about other types of SQL statements, then you can always call DML (INSERT / UPDATE / DELETE / MERGE), DDL (CREATE / ALTER / DROP) or DCL (GRANT / REVOKE) from a procedure or an anonymous PL/SQ block, that does those and allows you to mix PL/SQL logic in. No need to do this the other way (introducing PL/SQL into DML / DDL / DCL).

Upvotes: 0

Deepak gupta
Deepak gupta

Reputation: 554

You can call or execute a procedure easily in SQL. Both parametrized or non can be called.

EXEC dbo.procedure_name

Upvotes: 0

Related Questions