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;