[MDEV-35965] Oracle: Named and Mixed notations for stored routine call parameters - Jira

We need to support Named and Mixed notations for stored routine parameters, in addition to the SQL Standard Positional notation.

It is an open qoestion if these new notations need to work only when sql_mode=ORACLE, or for any sql_mode.

This script demonstrates all notations:

CREATE OR REPLACE PROCEDURE p1(a IN INT := 1, b OUT INT) AS

BEGIN

  b:= a*100;

END;

CREATE OR REPLACE PROCEDURE p2 AS

  va INT := 1;

  vb INT := 10;

BEGIN

  -- all calls below are equivalent

  p1(va, vb);

  DBMS_OUTPUT.PUT_LINE(vb || ' positional notation');

  p1(a=>va, b=>vb);

  DBMS_OUTPUT.PUT_LINE(vb || ' named notation');

  p1(b=>vb, a=>va);

  DBMS_OUTPUT.PUT_LINE(vb || ' named notation, different order');

  p1(va, b=>vb);

  DBMS_OUTPUT.PUT_LINE(vb || ' mixed notation');

  p1(b=>vb);

  DBMS_OUTPUT.PUT_LINE(vb || ' omit "a" (use its default)  + named notation for "b"');

END;

CALL p2;

It produces the following output:

100 positional notation

100 named notation

100 named notation, different order

100 mixed notation

100 omit "a" (use its default)  + named notation for "b"

Details

In Named notation the order of parameters is not important
In Mixed notation the leftmost arguments can use Positional notation, then some other arguments can be passed using Named notation

is part of

MDEV-35973 Oracle Compatibility Project 1 for 2025

relates to

MDEV-10862 Stored procedures: default values for parameters (optional parameters)

MDEV-34319 Associative arrays: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines

links to

Oracle docs: Passing Parameters to PL/SQL Subprogram

No workflow transitions have been executed yet.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.