| varchars in functions [message #37829] |
Fri, 01 March 2002 04:30  |
nigel
Messages: 7
Registered: April 2001
|
Junior Member |
|
|
I have a function. The input argument is defined as a varchar. When i pass it a null i get an error
ORA-0602 character string buffer to small. What is not an option is chaging my database schema so that this particular field is not null. How can i get the function to accept NULLS.
the text of the function is
( str IN VARCHAR2, wid IN NUMBER)
return VARCHAR2
AS
BEGIN
RETURN rpad( nvl( substr( str,1, wid),' '), wid);
END;
any suggestions
|
|
|
|
|
|
| Re: varchars in functions [message #37834 is a reply to message #37829] |
Fri, 01 March 2002 07:46  |
Malcolm
Messages: 6
Registered: March 2002
|
Junior Member |
|
|
I'm following up from nigel's question...
The function (called padString) doesn't throw any errors if you directly pass it a null, e.g.
SELECT padString(null,10) FROM DUAL;
But on our 8.1.7 database if you run
SELECT padString(short_display,10) from BFV;
where the short_display column is a varchar2(48) and the table contains a null value, you get an ORA-06502 error.
It seems that the function won't allow you to select a null varchar2 off the database into a varchar2 function parameter. This is true even if the body of the function is 'return null'.
Is there any way to fix this in the function declaration other than to put the function argument inside an NVL() when you call padString?
|
|
|
|
| Re: varchars in functions [message #37848 is a reply to message #37834] |
Sun, 03 March 2002 23:51 |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
Hi,
i could not reproduce the problem. i have tested it against oracle 7 and oracle 8.1.7.
here is how i tested.can u send me a testcase like this, reproducing the problem.
cheers
pratap
create or replace function padstring( str IN VARCHAR2, wid IN NUMBER)
return VARCHAR2
AS
BEGIN
RETURN rpad( nvl( substr( str,1, wid),' '), wid);
END;
/
SELECT padString(null,10) FROM DUAL;
create table testpad(short_display varchar2(48));
insert into testpad values('ddd');
insert into testpad values(null);
insert into testpad values('dfghdnull');
select padString(short_display,10) from testpad;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
