Skip to main content
Solved

Oracle stored function return value

  • November 1, 2017
  • 6 replies
  • 186 views

Forum|alt.badge.img

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!

Best answer by erik_jan

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).

View original
Did this help you find an answer to your question?

6 replies

david_r
Evangelist
  • November 1, 2017

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.


erik_jan
Contributor
Forum|alt.badge.img+17
  • Contributor
  • Best Answer
  • November 1, 2017

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).


Forum|alt.badge.img
  • Author
  • November 1, 2017
erik_jan wrote:

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;

david_r
Evangelist
  • November 1, 2017
at101 wrote:

 

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

Forum|alt.badge.img
  • Author
  • November 1, 2017
david_r wrote:

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.

 

 


david_r
Evangelist
  • November 1, 2017
at101 wrote:
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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings