CODEWITHSUNDEEP
oracle-databasestored-proceduresprocedure
JerryKur
JerryKur

Reputation: 7519

How can I set an oracle procedure's parameter default to the result of a select?

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.

Upvotes: 2

Views: 13532

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67752

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;

Upvotes: 8

Related Questions