Skip to Main Content
  • Questions
  • Can we call a procedure in select statement with any restriction?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ma.

Asked: March 30, 2016 - 3:59 pm UTC

Answered by: Connor McDonald - Last updated: December 10, 2019 - 10:58 am UTC

Category: Developer - Version: 12c

Viewed 10K+ times! This question is

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

and you rated our response

  (2 ratings)

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

Reviews

can proc /func call in sql

January 10, 2017 - 6:58 am UTC

Reviewer: A reader from india

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

December 10, 2019 - 2:36 am UTC

Reviewer: Alejandro from SON MX

Here it says a procedure can return results...

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

Followup  

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