Executing an Oracle Stored Procedure that returns results
Posted on 2011-04-28
I'm coming from a SQL Server background, and now trying to create a Oracle Stored Procedure that returns results. My ultimate goal is to run this procedure from SQL Server, and get results from Oracle.
I'm a bit stuck here, from what I've gathered, Oracle stored procedures don't automatically return results, you have to pass a reference cursor back. Ahh, lemme just hush and give you what I've been working with. I can't even call this from Oracle yet, much less SQL Server.
Working off examples, I first created a Package,
create or replace
PACKAGE Types AS
TYPE cursor_type IS REF CURSOR;
Then I created my procedure,
create or replace
PROCEDURE LIVEHOLDINGS (
p_recordset OUT Types.cursor_type,
fund IN varchar2
OPEN p_recordset FOR
select h.fundno, c.SEC_ID as cusip, s.SEC_ID as sedol, t.SEC_ID as ticker, h.C_N_PTF_MANAGER, h.NAME,
sum(h.CURR_NOMINAL) as Quantity, max(h.PRICE) as PRICE, sum(h.A_MARKET_VALUE) as MarketValue, sum(h.BOOKCOSTDC) as BookValue
from AMS.QRY_V_NAVEXP_HOLDINGS h
left outer join AMS.QRY_V_LU_CEL_VAL_ID c on h.WKN=c.NOVAL and c.CLANGUE=2 and c.ID_CODE='04'
left outer join AMS.QRY_V_LU_CEL_VAL_ID s on h.WKN=s.NOVAL and s.CLANGUE=2 and s.ID_CODE='03'
left outer join AMS.QRY_V_LU_CEL_VAL_ID t on h.WKN=t.NOVAL and t.CLANGUE=2 and t.ID_CODE='14'
where h.N_LOT=(select max(n_lot) from AMS.QRY_V_NAVEXP_HOLDINGS where h.fundno=fund)
group by h.fundno, c.SEC_ID, s.SEC_ID, t.SEC_ID, h.C_N_PTF_MANAGER, h.NAME;
Now I'm just stuck trying to execute this stored procedure and return the results to the string.
Here's what I tried, but I get the error "Bind Variable "C" is NOT DECLARED"
variable c refcursor
exec LIVEHOLDINGS(:c, '39')
Can someone help me, I think I'm close... Also, if there is a better way to do this, I'm all ears!