Skip to main content
  • EN

Hello FME Experts,

I need some help in figuring out how to use a return value from a stored function in oracle.

Stored function:

create or replace FUNCTION TestReturnFunction(inputval in varchar2)

return NUMBER


IS

countnum number:=0;

BEGIN

 

IF( countnum = 0 ) THEN
select count(*) into countnum from TestTable where MyVal is not null and TestID=inputval;

END IF; IF( countnum >= 1 )
THEN
RETURN (0);
ELSE
RETURN (1);
END IF; END TestReturnFunction;

 

 

How I call it:FME_SQL_DELIMITER /

declare
result1 number; begin
result1 := TestReturnFunction('TestThisValue');

 

end;

 

 

While this runs successfully, no value is returned in the "result" section of sqlexecutor. Any help on how to get the output would be greatly appreciated!

Hi, please look at this previous post, hopefully it can help you along:

https://knowledge.safe.com/questions/4782/results-from-oracle-stored-procedure.html

Let me know if anything is unclear.

You can use the SQLExecutor with:

Select TestReturnFunction('TestThisValue') as _return from Dual;

The result will be stored in the attribute _result (which you need to expose).

You can use the SQLExecutor with:

Select TestReturnFunction('TestThisValue') as _return from Dual;

The result will be stored in the attribute _result (which you need to expose).

 

Thank you! That worked.

 

Here is the exact working SQL for any one who stumbles here:

 

FME_SQL_DELIMITER ;

 

Select TestReturnFunction('TestThisValue') as return1 from Dual;

 

Thank you! That worked. 

 

Here is the exact working SQL for any one who stumbles here:

 

FME_SQL_DELIMITER ;

 

Select TestReturnFunction('TestThisValue') as return1 from Dual;
You can even skip the semicolon stuff:

 

Select TestReturnFunction('TestThisValue') as return1 from dual

Hi, please look at this previous post, hopefully it can help you along:

https://knowledge.safe.com/questions/4782/results-from-oracle-stored-procedure.html

Let me know if anything is unclear.

Thank you for your reply! I had looked at that post previously but mine was a bit different as I couldn't extract the SP into a SQL.

 

 

Thank you for your reply! I had looked at that post previously but mine was a bit different as I couldn't extract the SP into a SQL.

 

 

It's basically the same solution, but just not as reader-friendly as the answer from @erik_jan 🙂 Glad it worked out for you.

 

Reply


Community Stats

30,099
Posts
114,259
Replies
37,731
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.