You Asked

hi tom plz tell me in simple example explanation

Can we restrict the function invoke in select statement.

Can we call a procedure in select statement with any restriction?

and Connor said...

The execution of a function is controlled by execution privileges at schema level, and by whitelists ( http://asktom.oracle.com/Misc/12c-whitelists.html ).

You cannot call a procedure in a select statement, because it does not return anything.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

can proc /func call in sql

A reader, January 10, 2017 - 6:58 am UTC

function can be called but not procedure
as u said in above statement, procedure cannot return a value
proc may /may not return value

Functions are allowed because they calculate the value and return a value.
procedure fallow set of instructions.

version 12c...

Alejandro, December 10, 2019 - 2:36 am UTC

Here it says a procedure can return results...

https://oracle-base.com/articles/12c/implicit-statement-results-12cr1

December 10, 2019 - 10:58 am UTC

Neat, I didn't know that!

But Connor's point still stands - even with this you can't call procedures in SQL.

Using Tim's example:

EXEC get_my_results(1);

ResultSet #1


DESCRIPTION                    CREATED_DATE        
------------------------------ --------------------
The value 1                    08-DEC-2019 10:55:49

ResultSet #2


  COUNT(*)
----------
         3

select get_my_results(1) from dual;

select get_my_results(1) from dual
Error at Command Line : 38 Column : 8
Error report -
SQL Error: ORA-00904: "GET_MY_RESULTS": invalid identifier

How to call a procedure from a select statement

Jean-Pierre, March 10, 2023 - 4:48 pm UTC

Hi there,

I don't agree, there is a way to call a procedure in a select statement: you can use a "with function" clause for that:
with function myfunction
return varchar2
is
begin
dbms_application_info.set_client_info('aClientInfo');
return 'aText';
end;
select myfunction
, sys_context('userenv', 'client_info')
from dual

March 10, 2023 - 5:27 pm UTC

Well, yes - you could also call a regular function which calls a procedure.

I think that misses the point though: you can't call a procedure directly from SQL. Using WITH FUNCTION is defining PL/SQL local to the statement.

Breadcrumb

Question and Answer