How can I set an oracle procedure's parameter default to the result of a select?
13,502
You can use an otherwise unvalid value for this meaning, for example NULL:
PROCEDURE foo (p_id NUMBER DEFAULT NULL) IS
l_startID NUMBER := p_id;
BEGIN
IF p_id IS NULL THEN
SELECT max(id) INTO l_startID FROM xtable;
END IF;
...
END;
If your function/procedure is in a package, you could also overload it:
PROCEDURE foo (p_id NUMBER)
[...]
PROCEDURE foo IS
l_startID NUMBER;
BEGIN
SELECT max(id) INTO l_startID FROM xtable;
foo(l_startID);
END;
Author by
JerryKur
Updated on June 13, 2022Comments
-
JerryKur almost 2 years
I have an oracle Procedure and I want to set the default parameter to the result of a select statement such as below.
procedure foo( startID number : = max(x.id) from xtable x )
but the code above is illegal syntax.