Why is this Oracle function returning 'Memo' datatype. 1

[qu1ncy]

Hi,

I'm stumped here! Hope someone can shed some light on this.

The following function retrieves the concatenated supervisor name when passed the supervisor's empid. Each of the First_Name & Last_Name fields is a Varchar2(50) so joined they should not exceed 102 characters (including comma & a space).

When I call this function in a View, it has been returned as a memo field Varchar2(4000). On the Oracle side this doesn't matter, but when I pull this view into a Crystal Report it comes through as a memo datatype and won't allow users to group on that field.

Does any one have any suggestions?
-----------------------------------------------------------
FUNCTION GET_SUPERVISOR
(Empid_In IN Varchar2)
RETURN VARCHAR2 IS
R_SUPERVISOR_NAME VARCHAR2(102);

BEGIN
Select LAST_NAME||', '||FIRST_NAME
into R_SUPERVISOR_NAME
from hris_employee_info
where empid = Empid_In;

RETURN R_SUPERVISOR_NAME;
END;
-------------------------------------------------------
Thanks in advance,
Q

 

[Turkbear]

Hi,
There is no such object as a MEMO field in Oracle..It is, by your own definition, a VARCHAR2(4000) field..
Something is causing Crystal to misread the result.
Are you connnecting using ODBC or the Oracle native driver?
Can you create a formula in Crystal to do the concatenation and does it work?

 

[qu1ncy]

Hi Turkbear,

I'm using Oracle native driver.
To do the concatenation in Crystal, I will have to use 2 functions, retrieving the Supervisor's First_Name in one, Last_Name in the other, pull those into my View, then concatenate in Crystal.
I didn't think of that and will give it a try. I'm away from the office until tomorrow am. Will update you as soon as I get in.

Thanks for the help,
Q

 

[sem]

Try to use substr(GET_SUPERVISOR(..), 1, 102) instead of GET_SUPERVISOR in view definition. Thus you may let Oracle know that this field length doesn't exceed 102.

Regards, Dima

 

[qu1ncy]

Hey Dima,

Thanks for the pointer! Works very nicely in the view and now allows me to group on this field in Crystal.
Appreciated!

Q