Using OUT parameters in Oracle stored procedures is not supported in Spotfire

Using OUT parameters in Oracle stored procedures is not supported in Spotfire

In Spotfire Analyst, when creating a procedure in Information Designer and connecting it to an existing stored procedure in an Oracle database, it must not contain any OUT parameters as Spotfire do not support these.

If you try to add an Oracle stored procedure containing an OUT parameter you will get a message saying that "OUT parameters are not supported":

Spotfire supports ref_cursors, so in order to return values you need to create a Function returning a ref_cursor. It is not possible to create an Oracle procedure containing 'Return' keyword, since Oracle procedures only work with OUT parameters to return values.

Example of procedure containing OUT parameter that is not working:

create or replace procedure tibco_test (objid_simul in varchar2, dati_cursor out SYS_REFCURSOR) as 

objid_simul_01 number; 
objid_simul_02 number; 
objid_simul_03 number; 

begin 

objid_simul_01 := regexp_substr(objid_simul, '[^;]+', 1, 1); 
objid_simul_02 := regexp_substr(objid_simul, '[^;]+', 1, 2); 
objid_simul_03 := regexp_substr(objid_simul, '[^;]+', 1, 3); 

open dati_cursor for 
select 'S1' as c_simulazione , 
objid_simul_01 as objid_simulazione 
from dual 
union all 
select 'S2' as c_simulazione , 
objid_simul_02 as objid_simulazione 
from dual 
union all 
select 'S3' as c_simulazione , 
objid_simul_03 as objid_simulazione 
from dual; 

end;

Above stored procedure transformed to a working Function below:
 

CREATE OR REPLACE FUNCTION TIBCO_TEST2( objid_simul in varchar2) return SYS_REFCURSOR as 

dati_cursor SYS_REFCURSOR;

objid_simul_01 number; 
objid_simul_02 number; 
objid_simul_03 number; 

begin 

objid_simul_01 := regexp_substr(objid_simul, '[^;]+', 1, 1); 
objid_simul_02 := regexp_substr(objid_simul, '[^;]+', 1, 2); 
objid_simul_03 := regexp_substr(objid_simul, '[^;]+', 1, 3); 

open dati_cursor for 
select 'S1' as c_simulazione , 
objid_simul_01 as objid_simulazione 
from dual 
union all 
select 'S2' as c_simulazione , 
objid_simul_02 as objid_simulazione 
from dual 
union all 
select 'S3' as c_simulazione , 
objid_simul_03 as objid_simulazione 
from dual; 

return dati_cursor;

end;

https://docs.tibco.com/pub/sfire-analyst/7.13.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/id/id_procedures_overview.htm