Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2006-06-19
9
1,680 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
ID: 16939801
For starters, for all of your CustomOutput_Type parameters INOUT instead of OUT.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16939822
>> 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
ID: 16944245
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
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 1

Author Comment

by:mmoble
ID: 16944267
Changing to In Out parameters didn't make a difference.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16944491
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
ID: 16945608
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
ID: 16947157
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
ID: 16948355
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
ID: 16952992
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

839 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