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
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.
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/
January 23, 2024 - 6:45 am UTC
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
--------------------------------------------------
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.