Re: ORA-00999

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 23 Feb 2007 13:27:01 -0800
Message-ID: <1172266025.805340@bubbleator.drizzle.com>

Jonathan Lewis wrote:

> "DA Morgan" <damorgan_at_psoug.org> wrote in message 
> news:1172255605.776958_at_bubbleator.drizzle.com...

>> Just found something without a single reference at metalink or that I
>> can find using google.
>>
>> Can anyone explain what is happening here?
>>
>> SQL> CREATE OR REPLACE PACKAGE test AS
>> 2 FUNCTION myfunc( args ...) RETURN VARCHAR2;
>> 3 END test;
>> 4 /
>>
>> Package created.
>>
>> SQL> CREATE OR REPLACE FUNCTION myfunc(args ...) RETURN VARCHAR2 AS
>> 2 BEGIN
>> 3 RETURN 'A';
>> 4 END;
>> 5 /
>>
>> Warning: Function created with compilation errors.
>>
>> SQL> sho err
>> Errors for FUNCTION DUMMY:
>>
>> LINE/COL ERROR
>> -------- -----------------------------------------------------------------
>> 1/16 PLS-00999: implementation restriction (may be temporary)
>> ellipsis not allowed in this context
>>
>> SQL>
>>
>>
>> What is the meaning of (args ...) as an input parameter?
>>
>> And lest you think it invalid syntax ... check out
>> /rdbms/admin/utllms.sql
>>
>> I am trying to figure out what the UTL_LMS package header means.
>>
>> Thanks.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

> 
> 
> Not sure if this is the be-all and end-all, but it
> allows a convenient pl/sql interface to formatting
> error messages:
> 
> SQL> l
>   1  declare
>   2     mv varchar2(4000);
>   3  begin
>   4     mv := utl_lms.format_message(
>   5             'unique constraint (%s.%s) violated',
>   6             'schema',
>   7             'constraint'
>   8     );
>   9     dbms_output.put_line(mv);
>  10* end;
> SQL> /
> unique constraint (schema.constraint) violated
> 
> It's possible that there is an event you can set to make
> the "..." legal.
> 
> Basically, it looks like the Oracle equivalent of printf(),
> viz: a function that isn't a proper function because is can
> have any number of input parameters.

That's what it looks like to me too.

The Oracle documents, I've pointed out the error to the writers already, mistakenly refers to the parameter as a VARCHAR2. But from my testing it appears that in defining a function one can use the elipses (...) to indicate any number of arguments, of different data types, just as long as it is done in a package header. Do it in the body and it blows up.

CREATE OR REPLACE PACKAGE test AS
FUNCTION testf(args ...) RETURN VARCHAR2; END;
/

CREATE OR REPLACE PACKAGE BODY test AS

FUNCTION testf(args ...) RETURN VARCHAR2 IS BEGIN
   dbms_output.put_line(z);
END testf;
END;
/

I can't find anything, yet, that will explain what they are doing. And yes this is purely of academic interest.

Thanks.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Feb 23 2007 - 15:27:01 CST