Forum Stats

  • 3,825,213 Users
  • 2,260,482 Discussions
  • 7,896,445 Comments

Discussions

varargs in PL/SQL

jnicholas330
jnicholas330 Member Posts: 92 Green Ribbon
edited Jul 5, 2016 5:36PM in Database Ideas - Ideas

It's already there, just make it available for user defined units. Look at the definition of this package, and take note of the ellipsis:

CREATE OR REPLACE PACKAGE SYS.utl_lms AS
   /*
    *  FUNCTION:
    *  Format the retrieved LMS message.
    *
    *  Format string special characters
    *    '%s'   - substitute next string argument
    *    '%d'   - substitute next integer argument
    *    '%%'   - special character '%'
    *
    *  PARAMETERS
    *    format - Formatting string.
    *    args   - Subtitution arguments list.
    *  RETURN
    *    Fomatted result    on success.
    *    NULL               on failure.
    * EXCEPTIONS
    *   miscellaneous runtime exceptions.
    */
    FUNCTION format_message(format IN VARCHAR2 CHARACTER SET ANY_CS,
                           args ...)
      RETURN VARCHAR2 CHARACTER SET format%CHARSET;

END utl_lms;
/

This allows code in the form of format_message('%s %s','first string','second string'). This can be useful in many other contexts. Unfortunately, if you try to use the ellipsis in your own code, you will get the following error:

SQL> CREATE OR REPLACE FUNCTION my_parameter_array_func (FORMAT IN VARCHAR2, arg

s ...) RETURN VARCHAR2 AS

  2                             BEGIN

  3                             RETURN '';

  4                             END;

  5  /

Warning: Function created with compilation errors.

SQL> show err

Errors for FUNCTION MY_PARAMETER_ARRAY_FUNC:

LINE/COL   ERROR

---------- ---------------------------------------------------------------------

------------------------------

1/55       PLS-00999: implementation restriction (may be temporary) ellipsis not

allowed in this context

SQL>

jnicholas330ctriebSven W.MathewDBALukas Ederulohmannfac586MarwimWilliam RobertsonNiels HeckerFrank Tollenaar-OracleAlexey MarinRafael PonteApexBinesdstuber
15 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,535 Gold Crown

    I would prefere to add a collection as a parameter, but I see some functions where this is usefull.

    I mean there are several oracle internal functions that show a behaviour that is probably implemented using an ellipsis.

    COALESCE for example.

    One workaround would be to use default NULL parameters or  overload the user defined function with several parameters. The list would not be endless, but you can easily cover cases with 3-10 parameters.

    William Robertson
  • jnicholas330
    jnicholas330 Member Posts: 92 Green Ribbon

    I just noticed the an ellipsis syntax does not include a data type. I'm wondering if Oracle is using ANYTYPE internally?

  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    I wouldn't mind this feature at all, but in order to implement it correctly, I suspect that an anonymous array type for any scalar type would be required, first, e.g. NUMBER(10)[] or VARCHAR2(50)[]. This would generally be useful (i.e. structural array types as opposed to nominal VARRAY / TABLE types). Once that is in place, and all the tools to iterate and manipulate such arrays, then varargs would be simple syntax sugar, just like in Java

    William RobertsonSven W.Danilo PiazzalungaPeter Hraško
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    I wouldn't mind this feature at all, but in order to implement it correctly, I suspect that an anonymous array type for any scalar type would be required, first, e.g. NUMBER(10)[] or VARCHAR2(50)[]. This would generally be useful (i.e. structural array types as opposed to nominal VARRAY / TABLE types). Once that is in place, and all the tools to iterate and manipulate such arrays, then varargs would be simple syntax sugar, just like in Java

    I like the idea of anonymous array types. Perhaps that deserves its own Idea. Something like:

        my_emps emp.empno%table := emp.empno%table(1,2,3);

    which the compiler could expand internally, thus cutting out a line of throwaway type declaration.

    Lukas EderDanilo Piazzalunga
  • Lukas Eder
    Lukas Eder Member Posts: 126 Bronze Badge

    I like the idea of anonymous array types. Perhaps that deserves its own Idea. Something like:

        my_emps emp.empno%table := emp.empno%table(1,2,3);

    which the compiler could expand internally, thus cutting out a line of throwaway type declaration.

    You're right, done:

    William RobertsonDanilo Piazzalunga
  • Niels Hecker
    Niels Hecker Member Posts: 28 Bronze Badge
    edited Oct 2, 2017 3:50PM

    I just noticed the an ellipsis syntax does not include a data type. I'm wondering if Oracle is using ANYTYPE internally?

    Internally Oracle calls a C function in an external library:

    FUNCTION FORMAT_MESSAGE(FORMAT IN VARCHAR2 CHARACTER SET ANY_CS,
                             ARGS  ...)
    RETURN VARCHAR2 CHARACTER SET FORMAT%CHARSET
    AS LANGUAGE C
    NAME "pilmsfm"
    PARAMETERS
    (CONTEXT,
    FORMAT               STRING,
    FORMAT  INDICATOR    SB4,
    FORMAT  LENGTH       SB4,
    FORMAT  CHARSETFORM  UB4,
    ARGS                 VALIST,
    RETURN  INDICATOR    SB4,
    RETURN  LENGTH       SB4,
    RETURN  CHARSETFORM  UB4,
    RETURN               STRING

    LIBRARY UTL_LMS_LIB
    WITH CONTEXT;
  • funky_k0val
    funky_k0val Member Posts: 1 Green Ribbon

    This is so cool.... It would be awesome if this feature will find its way to the public.