Solved

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

Posted on 2006-06-19
9
1,667 Views
Last Modified: 2010-05-18
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
Comment
Question by:mmoble
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
For starters, for all of your CustomOutput_Type parameters INOUT instead of OUT.
0
 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
>> close OutputCursor;
In PROCEDURE ProcessMYAccounts you are closing the cursor

Do not close the cursor.  Also remember you cannot scroll backwards.
0
 
LVL 1

Author Comment

by:mmoble
Comment Utility
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
 
LVL 1

Author Comment

by:mmoble
Comment Utility
Changing to In Out parameters didn't make a difference.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Expert Comment

by:MohanKNair
Comment Utility
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
 
LVL 1

Author Comment

by:mmoble
Comment Utility
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
 
LVL 25

Expert Comment

by:jrb1
Comment Utility
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
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:mmoble
Comment Utility
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now