This query will give you the argument types and result types as arrays for any function:
SELECT f.oid::regproc AS function_name,
f.proargtypes::regtype AS argument_types,
CASE WHEN f.proallargtypes IS NULL
ELSE array_agg(args.type::regtype) FILTER (WHERE args.mode = 'o')
END AS ...
There is built-in type called SYS.ODCINUMBERLIST which is a VARRAY(32767) of NUMBER, and EXECUTE privilege is granted on it to everyone (PUBLIC). You can use that for up to 32767 numbers, example:
with function f1 (p_numbers SYS.ODCINUMBERLIST) return number
select f1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7)) from dual;
how to set the value of @MaxItemSeqNum to the returned result
SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number])
WHERE Batch_Id = 1
Group By Batch_Id );
SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number])
A function may not see the same state of the database depending on whether it's VOLATILE or not, when this state is changing during the execution of the SQL statement it's called from, either because of the statement itself or because of another transaction if the isolation level is
This is documented in https://www.postgresql.org/docs/...
Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.
Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition.
Here's my (failed) attempt to reproduce the issue:
Based on a quick and simple test:
select * from
position = 0
and data_level = 0
and argument_name is null
and data_type = 'REF CURSOR';
position 0 is the first argument of a program unit. If it does not have a name/level (data_level = 0 and argument_name is null), then it is the return argument of the function, and you ...