• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1716
  • Last Modified:

PL/SQL Cursors, looping through and passing as ref cursor

Hi Experts,

I have a Pl/SQL procedure in a package whose purpose is to run a select statement to a cursor, loop though that cursor and a delete for each row it returns and then send that whole cursor back to my ASP.NET application.

I can't get this to work.  I have no problem with creating a cursor from a select statement and sending it back to the ASP.NET app.  But I can't figure out how to do processing on the cursor before I send it back.  I want to do both with one cursor for performance reasons.

I get the error: ORA-01001: invalid cursor

Here's my code:
--***************************

CREATE OR REPLACE
PACKAGE  STANDARDREPORTS as

  TYPE CustomOutput_Type IS REF CURSOR;
 
   PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  
  PROCEDURE GetMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  
 end STANDARDREPORTS;

CREATE OR REPLACE
PACKAGE BODY STANDARDREPORTS as

   PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
   
  accounts_row financial.chosen_accounts%rowtype;
   
   begin
   
   --Get multi year accounts
   GetMYAccounts(OutputCursor, pUserID, pAccountKey, pFY);
 
    --Remove multi year accounts from chosen accounts table
   loop  
   
      fetch OutputCursor into accounts_row;
        exit when OutputCursor%notfound;
       
        delete from financial.chosen_accounts where trim(userid) = pUserID and rand_num = pAccountKey and passed_accounts = accounts_row.passed_accounts;
               
   end loop;
   
  close OutputCursor;
 
  end;
   

 PROCEDURE GetMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
 
  begin
 
  open OutputCursor for
   SELECT rand_num, passed_accounts, userid
     FROM FINANCIAL.CHOSEN_ACCOUNTS
     WHERE RAND_NUM = pAccountKey AND
     trim(USERID) = Upper(pUserID) and
     Trim(passed_accounts) in(select fund||agency||org as passed_accounts FROM FINANCIAL.org2 WHERE fy = pFY and Upper(Trim(myind)) = 'Y');
   
  end;
 
 end STANDARDREPORTS;

--**********************

Here's an anonymous block I use to test:

DECLARE
c cais.standardreports.CustomOutput_Type;

test1 varchar2(30);
test2 varchar2(30);


BEGIN
cais.standardreports.processMyACCOUNTS(c,'RADIPU1', '72904','2006');
loop
      fetch c into  test1, test2;
      DBMS_OUTPUT.PUT_LINE(test1 ||' '||test2);
      exit when c%notfound;
end loop;
close c;
end;


The goal is to delete the multi year accounts from the chosen accounts table and also return a cursor with the multi year accounts.  Again, I can easily do both of these with two separate cursors but I want to use one cursor for performance reasons.
0
mmoble
Asked:
mmoble
  • 4
  • 3
  • 2
1 Solution
 
jrb1Commented:
For starters, for all of your CustomOutput_Type parameters INOUT instead of OUT.
0
 
MohanKNairCommented:
>> close OutputCursor;
In PROCEDURE ProcessMYAccounts you are closing the cursor

Do not close the cursor.  Also remember you cannot scroll backwards.
0
 
mmobleAuthor Commented:
removing the close OutputCursor got rid of that error, however I'm having more problems.

When I run it again, the delete works, but it returns an empty cursor to the anonymous block.

If I comment out the loop with the fetch and delete, the cursor returns the data I need to the anonymous block.

It seems like I can't do both.  I tried creating another cursor variable and assigning the OutputCursor to that and looping through the new variable, but that didn't work either.  

Added new cursor variable:
--*****
curvar1   CustomOutput_Type;
....
begin
...
 curvar1 := OutputCursor;
...
--******
then loop through the curvar1 instead of the OutputCursor.

The result of OutputCursor%rowcount is 1 after I do the loop/fetch.  Before I loop through and fetch from curvar1 the result of OutputCursor%rowcount is 0.  Apparently the variables are pointing to the same thing and when I loop through one of them it does it for the other.  How can I separate them so I can perform a fetch on one and not the other?

Doing a loop/fetch on OutputCursor screws it up so I can't pass it to the anonymous block.

"Also remember you cannot scroll backwards."

Not sure what you mean by this.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mmobleAuthor Commented:
Changing to In Out parameters didn't make a difference.
0
 
MohanKNairCommented:
While using cursors it is not possible to get the previous record unless it is stored in local variables. One solution is to BULK COLLECT into a PL/SQL table. This is advantageous if the number of records is less than 10000 and there is sufficient memory for shared pool.

CREATE OR REPLACE PACKAGE BODY STANDARDREPORTS as
v_UserID varchar2(4000);
v_AccountKey varchar2(4000);
v_FY varchar2(4000);
Cursor c1 is SELECT rand_num, passed_accounts, userid
FROM FINANCIAL.CHOSEN_ACCOUNTS
WHERE RAND_NUM = v_AccountKey AND
trim(USERID) = Upper(v_UserID) and
Trim(passed_accounts) in(select fund||agency||org as passed_accounts FROM FINANCIAL.org2 WHERE fy = v_FY and Upper(Trim(myind)) = 'Y');

TYPE c1tab is table of c1%rowtype;
c1t c1tab;

PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
accounts_row financial.chosen_accounts%rowtype;
n1 number;
begin
v_UserID := pUserID;
v_AccountKey := pAccountKey;
v_FY := pFY;
--Get multi year accounts
GetMYAccounts;
for n1 IN c1t.first..c1t.last
loop
delete from financial.chosen_accounts where trim(userid) = pUserID and rand_num = pAccountKey and passed_accounts = c1t(n1).passed_accounts;
end loop;
end;
 
PROCEDURE GetMYAccounts is
begin
open c1;
fetch c1 BULK COLLECT INTO c1t;
close c1;
end;
 
end STANDARDREPORTS;
/

0
 
mmobleAuthor Commented:
I don't see where you're setting the value of OutputCursor.

How do I send the pl/sql table c1t back as a ref cursor (OutputCursor) to the anonymous block?
0
 
jrb1Commented:
I think this is what you want:

http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/c0048da943b67559/b00295373499d20d%23b00295373499d20d

Instead of passing your REF CURSOR to the procedure to do the delete, you:

1) open the REF CURSOR
2) do your delete directly on the record
3) pass back the REF CURSOR

When you read through the REF CURSOR and close it, the data is gone.

I guess another way that will work, though is:

CREATE OR REPLACE
PACKAGE  STANDARDREPORTS as

  TYPE CustomOutput_Type IS REF CURSOR;
 
   PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  
  PROCEDURE GetMYAccounts(OutputCursor OUT CustomOutput_Type, OutputCursor2 OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  
 end STANDARDREPORTS;

CREATE OR REPLACE
PACKAGE BODY STANDARDREPORTS as

   PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
   
  accounts_row financial.chosen_accounts%rowtype;
   
   begin
   
   --Get multi year accounts
   GetMYAccounts(OutputCursor, OutputCursor2, pUserID, pAccountKey, pFY);
 
    --Remove multi year accounts from chosen accounts table
   loop  
   
      fetch OutputCursor2 into accounts_row;
        exit when OutputCursor2%notfound;
       
        delete from financial.chosen_accounts where trim(userid) = pUserID and rand_num = pAccountKey and passed_accounts = accounts_row.passed_accounts;
               
   end loop;
   
  close OutputCursor;
 
  end;
   

 PROCEDURE GetMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
 
  begin
 
  open OutputCursor for
   SELECT rand_num, passed_accounts, userid
     FROM FINANCIAL.CHOSEN_ACCOUNTS
     WHERE RAND_NUM = pAccountKey AND
     trim(USERID) = Upper(pUserID) and
     Trim(passed_accounts) in(select fund||agency||org as passed_accounts FROM FINANCIAL.org2 WHERE fy = pFY and Upper(Trim(myind)) = 'Y');
   
  end;
 
 end STANDARDREPORTS;
0
 
MohanKNairCommented:
1) Declare cursor and pl/sql table in package spec
2) Declare pl/sql table type variable in anonymous block and pass it to the procedure
3) The records are BULK COLLECT into this pl/sql table
4) Now this table can be referenced any number of times until it is deleted.

CREATE OR REPLACE PACKAGE  STANDARDREPORTS as
TYPE CustomOutput_Type IS REF CURSOR;
PROCEDURE ProcessMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  
PROCEDURE GetMYAccounts(OutputCursor OUT CustomOutput_Type, pUserID varchar2, pAccountKey varchar2, pFY varchar2);  

v_UserID varchar2(4000);
v_AccountKey varchar2(4000);
v_FY varchar2(4000);
Cursor c1 is SELECT rand_num, passed_accounts, userid
FROM FINANCIAL.CHOSEN_ACCOUNTS
WHERE RAND_NUM = v_AccountKey AND
trim(USERID) = Upper(v_UserID) and
Trim(passed_accounts) in(select fund||agency||org as passed_accounts FROM FINANCIAL.org2 WHERE fy = v_FY and Upper(Trim(myind)) = 'Y');

TYPE c1tab is table of c1%rowtype;
end STANDARDREPORTS;
/

CREATE OR REPLACE PACKAGE BODY STANDARDREPORTS as

PROCEDURE ProcessMYAccounts(c1t OUT STANDARDREPORTS.c1tab, pUserID varchar2, pAccountKey varchar2, pFY varchar2) is
accounts_row financial.chosen_accounts%rowtype;
n1 number;
begin
v_UserID := pUserID;
v_AccountKey := pAccountKey;
v_FY := pFY;
--Get multi year accounts
GetMYAccounts(c1t);
for n1 IN c1t.first..c1t.last
loop
delete from financial.chosen_accounts where trim(userid) = pUserID and rand_num = pAccountKey and passed_accounts = c1t(n1).passed_accounts;
end loop;
end;
 
PROCEDURE GetMYAccounts(c1t OUT STANDARDREPORTS.c1tab) is
begin
open c1;
fetch c1 BULK COLLECT INTO c1t;
close c1;
end;
 
end STANDARDREPORTS;
/

Declare the PL/SQL table in the anonymous block and pass to the procedure processMyACCOUNTS

DECLARE
c1t c1tab := c1tab();
test1 varchar2(30);
test2 varchar2(30);
BEGIN
cais.standardreports.processMyACCOUNTS(c1t,'RADIPU1', '72904','2006');
for n1 IN c1t.first..c1t.last
loop
     DBMS_OUTPUT.PUT_LINE(c1t(n1).rand_num||'   '||c1t(n1).passed_accounts||'  '||);
end loop;

---  Delete pl/sql table data
c1t.delete;
end;
/
0
 
mmobleAuthor Commented:
I changed my ASP.NET dataaccess function to use the Associative Array functionality of ODP.NET so I can read the pl/sql table, instead of using the Ref Cursor.  I also had to change the cursor so it only gets the passed_accounts field (can only pass single dimension array), I didn't really need the other fields anyway.  Thanks MohanKNair.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now