[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

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!
0
raterus
Asked:
raterus
  • 6
  • 5
1 Solution
 
slightwv (䄆 Netminder) Commented:
>> and return the results to the string

There is a new generic sys_recforsor that I would suggest using.  This way you don't need to create a type.

Have you thought about a function that returns a cursor?  You can then use CAST to treat it like a regular table.
create or replace procedure myproc(outCur out sys_refcursor)
is
begin
	open outCur for 'select ''hello'' col1, ''world'' col2, sysdate col3 from dual';
end;
/

show errors

var c refcursor
exec myproc(:c);
print c



create or replace function myfunc return sys_refcursor
is
	myCur sys_refcursor;
begin
	open myCur for 'select ''hello'' col1, ''world'' col2, sysdate col3 from dual';
	return myCur;
end;
/

show errors

select myfunc() from dual;

Open in new window

0
 
raterusAuthor Commented:
Thanks for responding, I actually got something to return using just sys_refcursor.  However the results are simply one cell/one column with some strange mix of xml/csv.

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?
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
raterusAuthor Commented:
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>,}
0
 
slightwv (䄆 Netminder) Commented:
Are you by chance using sqlplus?

Did you set markup on?

From the sql prompt: set markup html off
0
 
raterusAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
Are you using sqlplus or some other tool like Toad?
0
 
raterusAuthor Commented:
Oracle SQL Developer, and I've seen other table results just fine using this tool.
0
 
raterusAuthor Commented:
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...
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
raterusAuthor Commented:
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now