Hi, I knew the answer to this question but lost my notes. I have an Oracle procedure that I like to execute using openquery and database link from t-sql stored procedure. Currently I used something like Select * from openquery(oradbLink,’select name from aTable’) How can I get the result set of the oracle stored procedure? thanks

you can run an Oracle procedure through a OraOLEDB.Oracle linked server like so: EXEC('CALL myprocedure()') AT link_server_name; However it looks like you wont be able to return a result set this way, because as you may know Oracle procedures dont allow you to just spit out a query at the end like in SQL Server, you will need to use ref cursor which doesnt look to be supported with OLE. But what you can do is have your SP insert its results into a table and then use an openquery statement to get the data from that table. And may I ask are you trying to use this within SSRS? if so you can you have Oracle procedures directly output a ref cursor into a dataset.

you can run an Oracle procedure through a OraOLEDB.Oracle linked server like so: EXEC('CALL myprocedure()') AT link_server_name; However it looks like you wont be able to return a result set this way, because as you may know Oracle procedures dont allow you just spit out a query at the end like in SQL Server, you will need to use ref cursor which doesnt look to be supported with OLE. But what you can do is have your query insert its results into a table and then use an openquery statement to get the data from that table. And may I ask are you trying to use this within SSRS? if so you can you have Oracle procedures directly output a ref cursor into a dataset.

Thank you Nehemiah, You are correct, I could use something like EXEC ('select myPackage.myProcedure(''string parameter'') as MyOutput FROM DUAL') AT Link_server_name and it does works but as you said I like to use it within SSRS. Can you give me an example of how to directly output a ref cursor into a dataset? I do very much appreciated, thanks.

here is my example procedure, I use an out sys_refcursor for my data: CREATE OR REPLACE PROCEDURE NWILLIS.output_proc(p_recordset1 OUT SYS_REFCURSOR) IS BEGIN OPEN p_recordset1 FOR SELECT 1 from dual; END output_proc; and then in SSRS create a dataset that uses a standard Oracle datasource and make the query type be stored procedure. Then simply type the name of the procedure in the Query string textbox: output_proc Your dataset should now return the data from your ref cursor.

Perfect, I will try it out and keep you posted. thanks.

This is the first and last few line of my stored procedure. Create or replace package rwp_return_ce_labels_pk as type cur_type is ref cursor; procedure rwp_get_ce_ordlabels_sp (ordernum IN varchar2, p_result OUT cur_type); procedure rwp_get_ce_wiplabels_sp (wipnum IN varchar2, p_result OUT cur_type); end; -- Program logic goes here -- -- insert a line into the work table -- INSERT into rwp.rwp_ce_labels (category, wh_num, order_num, line_num, order_qty, style, length, pat1, fin1, typ1, pat2, fin2, typ2, trk_date, ship_state, copyn, copyd, dat_tim, ponum, partnum) values (v_category, v_wh_num, v_order_num, v_line_num, v_qtylab, v_style, v_length, v_pat1, v_fin1, v_typ1, v_pat2, v_fin2, v_typ2, v_trk_date, v_ship_state, v_loopcnt, v_numlab, sysdate, v_ponum, v_partnum); COMMIT; EXIT when v_loopcnt >= v_numlab; END LOOP; END IF; -- end: if EOF CLOSE cur_GETWIP; -- Return Results open p_result for select * from rwp.rwp_ce_labels; end; --==================================================================== -- end package END;