PL/SQL function parameter

Question

In PL/SQL, the code below will fail. It doesn't allow the definition of size for the varchar2 parameter. Do you know why? How do I fix it?

create or replace function logMessage(msg varchar2(2000))
return number as
begin
    null;    
    return 1;
end logMessage;
/

error message is

1/33 PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

score 10 · Accepted Answer · edited May 23 '17 at 12:24

You fix it by removing the size constraint. It's not needed:

create or replace function logMessage (msg in varchar2)
return number is
begin
    null;    
    return 1;
end logMessage;
/

I assume your function is slightly more complicated than this?

The full syntax for the create function statement from the documentation is:

CREATE [OR REPLACE] FUNCTION [Owner.]FunctionName 
     [(arguments [IN|OUT|IN OUT][NOCOPY] DataType [DEFAULT expr][,...])]
     RETURN DataType [InvokerRightsClause] [DETERMINISTIC]
     {IS|AS}

There's a lot of information around the specifics if you're interested but you may find TECH on the Net more useful.

In answer to your first question of why I don't know and can't find an answer. But to quote APC:

This is annoying but it's the way PL/SQL works so we have to live with it.

Put simply, you should know at run-time how long something is going to be and be able, therefore, to deal with it. There are a few options you can consider though:

If you know what length you want message to be you can define a variable, the default value of which is a substr of the parameter:

create or replace function logmessage ( msg in varchar2 ) return number is

   l_msg varchar2(2000) := substr(msg,1,2000);

begin
   return 1;
end;

Alternatively, you can check the length in the function itself:

create or replace function logmessage ( msg in varchar2 ) return number is

begin

   if length(msg) > 2000 then
      return 0;
   end if;

   return 1;
end;

Won't substr() get an out-of-bounds exception if the msg is less than 2000 in length? It may be best to put it in an if statement as well — Brian Akumah, Apr 12 '23 at 12:22
[It won't @Brian](https://dbfiddle.uk/bKq_0yKR). `substr()` will take the first 2000 (in this case) characters (not bytes for which you'd use `substrb()`) of anything that can be cast to a `varchar2` when passed into the function. — Ben, Apr 12 '23 at 15:29

score 3 · Answer 2 · answered Jul 01 '12 at 14:27

3

Parameters are declared with data types but without data type length or precision. That means that a parameter may be declared as VARCHAR2 but it will not be declared with a length component (VARCHAR2(30) would not be valid).

answered Jul 01 '12 at 14:27

anazimok

1,750
2
20
33

PL/SQL function parameter

2 Answers2

Linked