I'm using Oracle 11 and trying to use SQL Developer. However, I cannot seem to get the DEBUG to work to test calling a SP that returns a Cursor.
I wrote a small test program to test, but it won't compile and I'm not sure why not. Can you assist?
the first code fragment is a package I have that has a CURTYPE defined. It is used throughout my application when declaring a cursor:
create or replace
PACKAGE "SP_STATE"
Is
type curtype is ref cursor;
...
now, here is the first part of the SP that I want to test. note that I defined PriceCur as using the package CURTYPE.
create or replace
PROCEDURE "USP_GETL_ACCOUNT_PRICES" (
AcctNo in marketer_account.mka_last_full_css_acct_no%type,
MktrNo in marketer_account.mka_mktr_no%type,
Status in varchar2 default null,
PriceCur out sp_state.curtype
)
as
begin
Open Pricecur For
Select
...
Now here is my test procedure:
Declare
Price_Cur Sp_State.Curtype;
price_rec price_cur%rowtype;
Begin
Usp_Getl_Account_Prices('302189139000026', 11813,'A', price_cur);
Open Price_Cur;
Loop
Fetch Price_Cur Into Price_Rec;
Exit When Price_Cur%Notfound;
dbms_output.put_line(price_cur%rowcount || ') ' || price_rec.apr_account_price_id);
End Loop;
close price_cur;
end;
It won't compile. All I'm trying to do here is call the SP - pass in the 3 inbound parameters and get back a cursor,
then I want to loop through the cursor and list out certain fields....
Where am I going wrong? Any help would be greatly appreciated.
Sean