table and data ctvctyp:
ctvctyp_code ctvctyp_code_pred
CO Z1
FE Z1
FUNCTION get_case_type(case_type VARCHAR2) RETURN VARCHAR2 IS
cursor type_cur is
select ctvctyp_code
from ctvctyp
where ctvctyp_code_pred = case_type;
temp_type VARCHAR2(4);
return_type VARCHAR2(200);
counter NUMBER;
BEGIN
return_type := '';
counter := 0;
srw.message(20,'in case_type is '||case_type);
open type_cur;
LOOP
FETCH type_cur INTO temp_type;
EXIT WHEN type_cur%NOTFOUND;
counter := counter+1;
if counter = 1 then
return_type := ''''||temp_type||'''';
else
return_type :=return_type||','''||temp_type||'''';
end if;
END LOOP;
return return_type;
END;
In my p_casetype validation trigger:
function P_CaseTypeValidTrigger return boolean is
tempCaseType VARCHAR2(200);
return_type VARCHAR2(200);
begin
:P_CASETYPE := 'Z1'
return_type := get_case_type(:P_CASETYPE); -- I got null returned.
return (TRUE);
end;
But in sqlplus:
declare a bind variable :ctype := 'Z1';
select get_case_type(:ctype) from dual;
returns: 'FE','MI'
Anyone knows why call in sqlplus, it returns correct result, but calling from a trigger in the report, it always return NULL?
Thanks.