We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Executing an Oracle Stored Procedure that returns results

raterus
raterus asked
on
Medium Priority
721 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2005

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
Top Expert 2005

Author

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>,}
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Are you by chance using sqlplus?

Did you set markup on?

From the sql prompt: set markup html off
Top Expert 2005

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Are you using sqlplus or some other tool like Toad?
Top Expert 2005

Author

Commented:
Oracle SQL Developer, and I've seen other table results just fine using this tool.
Top Expert 2005

Author

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...
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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.
Top Expert 2005

Author

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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.