Home » Developer & Programmer » Reports & Discoverer » function return different data when called from report and sqlplus (report builder:9.0.4.3.0 database:10g OS windows XP)
function return different data when called from report and sqlplus [message #343562] Wed, 27 August 2008 09:22 Go to next message
tianxing
Messages: 2
Registered: August 2008
Location: Kentucky
Junior Member
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.
Re: function return different data when called from report and sqlplus [message #344429 is a reply to message #343562] Fri, 29 August 2008 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It works fine on my Reports Builder 9.0.4.0.33.

I have created another parameter (which I'm displaying as the result); its name is "p_result_type".

Here's the screenshot (parameter validation trigger and report result (below)):

./fa/4915/0/

If you can't get the result when using a function created in report, use the one which resides in the database (i.e. the one which works correctly in SQL*Plus).

(Just checking: did you connect the same schema when testing a function?)
Re: function return different data when called from report and sqlplus [message #344433 is a reply to message #344429] Fri, 29 August 2008 15:51 Go to previous message
tianxing
Messages: 2
Registered: August 2008
Location: Kentucky
Junior Member
I did connect to the right database with right schema.

If I run it in sqlplus, it returns result.

I am still checking other things.

Thank you.
Previous Topic: forms&reprts
Next Topic: rep - 1219 Error
Goto Forum:
  


Current Time: Sat Dec 03 18:19:23 CST 2016

Total time taken to generate the page: 0.08970 seconds