Skip to main content
Solved

Oracle stored function return value

  • November 1, 2017
  • 6 replies
  • 282 views
A
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).

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

david_r
Celebrity
  • david_rCelebrity
  • 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+23
  • erik_janContributor
  • 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).

A
Forum|alt.badge.img
  • at101
  • Author
  • 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).

 

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
Celebrity
  • david_rCelebrity
  • November 1, 2017

 

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
A
Forum|alt.badge.img
  • at101
  • Author
  • 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.

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
Celebrity
  • david_rCelebrity
  • November 1, 2017
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.

 

Community Stats

32,154
Posts
122,048
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.