Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

testing a SP that returns a CURSOR

userLynxJul 11 2012 — edited Jul 11 2012
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
This post has been answered by Purvesh K on Jul 11 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 8 2012
Added on Jul 11 2012
13 comments
876 views