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.
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;
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.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).
