Skip to main content
Solved

Oracle stored function return value

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

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

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+17
  • 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
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
Celebrity
  • david_rCelebrity
  • 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
A
Forum|alt.badge.img
  • at101
  • 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
Celebrity
  • david_rCelebrity
  • 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


Helpful Members This Week

Community Stats

31,223
Posts
118,404
Replies
38,824
Members

Latest FME

Terms & ConditionsCookie settings

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