Executing an Oracle Stored Procedure that returns results
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;
END Types;
Then I created my procedure,
create or replace
PROCEDURE LIVEHOLDINGS (
p_recordset OUT Types.cursor_type,
fund IN varchar2
)
AS
BEGIN
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;
END LiveHoldings;
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')
print c
Can someone help me, I think I'm close... Also, if there is a better way to do this, I'm all ears!
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;
END Types;
Then I created my procedure,
create or replace
PROCEDURE LIVEHOLDINGS (
p_recordset OUT Types.cursor_type,
fund IN varchar2
)
AS
BEGIN
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;
END LiveHoldings;
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')
print c
Can someone help me, I think I'm close... Also, if there is a better way to do this, I'm all ears!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>results are simply one cell/one column with some strange mix of xml/csv.
I would need a test case that I could run on my end to help with that one.
>>I'm sure this is where the cast function you mention comes in
OK, I might have typed a little to quick here. I was thinking I have seen where I could select against a ref_cursor just like a table but cannot find any example of this. I'll keep looking.
>>are there any limitations I should be aware of with oracle functions over stored procedures?
It all depends on what you want/need. A function returns a single value. A procedure can have many OUT values. If you look at the sample code I provided, they are basically the same.
I would need a test case that I could run on my end to help with that one.
>>I'm sure this is where the cast function you mention comes in
OK, I might have typed a little to quick here. I was thinking I have seen where I could select against a ref_cursor just like a table but cannot find any example of this. I'll keep looking.
>>are there any limitations I should be aware of with oracle functions over stored procedures?
It all depends on what you want/need. A function returns a single value. A procedure can have many OUT values. If you look at the sample code I provided, they are basically the same.
ASKER
I've created a small test case, and still get a strange result,
create or replace
function sp_test return sys_refcursor
as
l_cursor sys_refcursor;
begin
open l_cursor for
select 'blah' as Col1 from dual;
return l_cursor;
end;
When I run this,
select sp_test() from dual;
My results are one column "SP_TEST()", and the result is
{<COL1=blah>,}
create or replace
function sp_test return sys_refcursor
as
l_cursor sys_refcursor;
begin
open l_cursor for
select 'blah' as Col1 from dual;
return l_cursor;
end;
When I run this,
select sp_test() from dual;
My results are one column "SP_TEST()", and the result is
{<COL1=blah>,}
Are you by chance using sqlplus?
Did you set markup on?
From the sql prompt: set markup html off
Did you set markup on?
From the sql prompt: set markup html off
ASKER
I'm pretty oracle stupid at this point, so I don't even know.
When I run that line though, I get this result
line 1: SQLPLUS Command Skipped: set markup html off
When I run that line though, I get this result
line 1: SQLPLUS Command Skipped: set markup html off
Are you using sqlplus or some other tool like Toad?
ASKER
Oracle SQL Developer, and I've seen other table results just fine using this tool.
ASKER
I was able to pull results under SQLPlus from a procedure and function with no issues, so it appears to be a SQL Developer issue. It's not a huge deal, as long as I know what command string I can pass through an OLEDB provider to get the results, at this point I can't find anything that works there...
Cool. Never used sql developer.
Now I guess all that's left is to decide what sql server can handle best, procedure or function. That I can't help with.
Now I guess all that's left is to decide what sql server can handle best, procedure or function. That I can't help with.
ASKER
Thanks, I just posted another question to figure out how to call this from SQL Server. I did get this to a point where it worked under Oracle, but I may end up having to trash all of this if there is no way to get these results to SQL Server.
ASKER
I'm sure this is where the cast function you mention comes in, but I'm not quite sure of that syntax.
I'm also playing with using functions here, are there any limitations I should be aware of with oracle functions over stored procedures?