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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, ma.

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

Last updated: January 25, 2024 - 5:48 am UTC

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

  (6 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
Chris Saxon
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




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

Use print_table as an example

Kevin Zhang, January 22, 2024 - 8:53 pm UTC

Hi Connor and Chris

Assuming DB version is of 12.1.0.2 and higher,
In this video titled Finding problem SQL - a deep dive into internals, https://www.youtube.com/watch?v=z44J0RQ5GeU
At time 05:40, there is a statement like the below
-------------------------------------
select c1, c2, ...
from v$sql
where sql_text like '%...%'
@pt

set term off
set serverout on size 10000000 termout off echo off
-------------------------------------
Here which type is @pt? If this is an anonymous procedure, how can we call it inside SELECT?


I assumed that @pt is a procedure. Please correct me if my assumption is wrong.


Sorry I could not find the original version of print_table from Tom Kyte.
The below post is the closet given that if you are allowed to create objects, then Anonymous Blocks is the best option.
https://asktom.oracle.com/ords/f?p=100:11:0::NO::P11_QUESTION_ID:9537323500346032880

If we have to create objects, then Jonathan's post has a reference of Tom Kyte's print_table
https://jonathanlewis.wordpress.com/2020/11/26/print_table/


Connor McDonald
January 23, 2024 - 6:45 am UTC

pt.sql and pr.sql can be found on my git repo

https://github.com/connormcd/misc-scripts/tree/master/sql

Could you kindly share your knowledge on syntax used in pr.sql

Kevin Zhang, January 24, 2024 - 2:43 am UTC

Thanks Connor. I used a default setting of SQL*Plus. I can use your script successfully by updating the value of _pr_tmpfile

Many other pages uses SQL> @pr "select statement"; syntax. (pr can be any valid name).
Your pt.sql script is easy to understand.
But when I googled your syntax used in pr.sql, I hit no result.
For example, certain lines have double semi colon(;) at the end of a line and certain do not have.

On CLOB,
Replace "0 c clob" with "0 c varchar2(32767)", it works.

Remove leading 0 at CLOB assignment line, I got
SP2-0023: String not found

Replace leading 0 with 1 at CLOB assignment line, I got
ERROR:
ORA-01756: quoted string not properly terminated

Replace 999999 with 123456, no errors.
Remove 999999 , got error.

Could you kindly share your knowledge on this or recommend some readings to understand your syntax?
-------------------------------------------------------------------------------
--
-- PLEASE NOTE
--
-- No warranty, no liability, no support.
--
-- This script is 100% at your own risk to use.
--
-------------------------------------------------------------------------------
.

set termout off
def _pr_tmpfile=x:\tmp\pr.out

store set &_pr_tmpfile.set replace
set termout on

set serverout on size 1000000 termout off echo off
save &_pr_tmpfile replace
set termout on

0 c clob := q'\
0 declare

999999 \';;
999999 l_theCursor integer default dbms_sql.open_cursor;;
999999 l_columnValue varchar2(4000);;
999999 l_status integer;;
999999 l_descTbl dbms_sql.desc_tab2;;
999999 l_colCnt number;;
999999 begin
999999 dbms_sql.parse( l_theCursor, c, dbms_sql.native );;
999999 dbms_sql.describe_columns2( l_theCursor, l_colCnt, l_descTbl );;
999999 for i in 1 .. l_colCnt loop
999999 dbms_sql.define_column( l_theCursor, i,
999999 l_columnValue, 4000 );;
999999 end loop;;
999999 l_status := dbms_sql.execute(l_theCursor);;
999999 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
999999 dbms_output.put_line( '==============================' );;
999999 for i in 1 .. l_colCnt loop
999999 dbms_sql.column_value( l_theCursor, i,
999999 l_columnValue );;
999999 dbms_output.put_line
999999 ( rpad( l_descTbl(i).col_name,
999999 30 ) || ': ' || l_columnValue );;
999999 end loop;;
999999 end loop;;
999999 exception
999999 when others then
999999 dbms_output.put_line(dbms_utility.format_error_backtrace);;
999999 raise;;
999999 end;;
/

set termout off
@&_pr_tmpfile.set

get &_pr_tmpfile nolist
host del &_pr_tmpfile
set termout on
--------------------------------------------------



Connor McDonald
January 25, 2024 - 5:48 am UTC

0 c clob := q'\

means "replace line 0 with the following". Since by definition there can not be an existing line 0, this goes at the front of the script and becomes line 1.

0 declare

does the same

999999 \';;

then adds line to the end of the script (assuming your existing script is not 1 million lines long :-)

So we are building an anonymous block with the existing in the buffer.

Thanks Connor

Kevin Zhang, January 26, 2024 - 2:04 am UTC

Thanks Connor.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library