Solved

Oracle stored function return value

  • 1 November 2017
  • 6 replies
  • 18 views
A
Badge

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!
icon

Best answer by erik_jan 1 November 2017, 14:29

View original

6 replies

david_r
Rank
Userlevel 4

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
Rank
Userlevel 2
Badge +16

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
Badge

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
Rank
Userlevel 4

 

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
Badge

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
Rank
Userlevel 4
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

28,731
Posts
109,530
Replies
36,565
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.