Coolcld
asked on
Return pl/sql table contents through ref cursor / convert pl/sql table into cursor
Hi,
I have a oracle stored procedure whose return type is ref cursor.
The data that need to be returned is obtained from looping through the cursors inside SP. In this case, when I return the result through ref cursor i'll get only the last record in the loop. So I stored the records in pl/sql table.
Question1: In this scenario, Is there any other way to retrieve resultset other than using pl/sql table?
Question 2: Assuming that we use pl/sql table to store records, how to transfer data from pl/sql table to ref cursor?
If this is not possible,
Actually, I am accessing this SP in .net application. Is there any way I can return pl/sql table to .net and access data from pl/sql table in .net?
Please help me.
Thanks in advance,
coolcld
I have a oracle stored procedure whose return type is ref cursor.
The data that need to be returned is obtained from looping through the cursors inside SP. In this case, when I return the result through ref cursor i'll get only the last record in the loop. So I stored the records in pl/sql table.
Question1: In this scenario, Is there any other way to retrieve resultset other than using pl/sql table?
Question 2: Assuming that we use pl/sql table to store records, how to transfer data from pl/sql table to ref cursor?
If this is not possible,
Actually, I am accessing this SP in .net application. Is there any way I can return pl/sql table to .net and access data from pl/sql table in .net?
Please help me.
Thanks in advance,
coolcld
ASKER
Alex: Thanks for the reply. I gone thru the link u have sent. But this did not help me.
I want to use ref - cursor inside loop. Like:
CURSOR curDusData IS SELECT..... FROM....;
OPEN curDusData ;
LOOP
FETCH curDusData INTO objcurData;
EXIT WHEN curDusData%NOTFOUND;
OPEN ref_curs for
select name from emp where emp.id = objcurData.id;
END LOOP;
CLOSE curDusData ;
You can see the above code obviously returns only the last record to .net. Am I wrong somewere?
That's why i used pl/sql table to store the records inside the loop. but got struck as how to return the pl/sql table to .net !
Thanks!
coolcld
I want to use ref - cursor inside loop. Like:
CURSOR curDusData IS SELECT..... FROM....;
OPEN curDusData ;
LOOP
FETCH curDusData INTO objcurData;
EXIT WHEN curDusData%NOTFOUND;
OPEN ref_curs for
select name from emp where emp.id = objcurData.id;
END LOOP;
CLOSE curDusData ;
You can see the above code obviously returns only the last record to .net. Am I wrong somewere?
That's why i used pl/sql table to store the records inside the loop. but got struck as how to return the pl/sql table to .net !
Thanks!
coolcld
The way I understand it, you move the loop construct over to the client side.
The client would do something like:
Call procedure to open the REF CURSOR
LOOP
exit when %notfound
END LOOP
CLOSE the REF CURSOR
Whereas the database would simply have a procedure like
OPEN ref cursor for SELECT whatever from where ever
Maybe this link is more usefull:
http://asktom.oracle.com/pls/ask/f?p=4950:8:13230892179573467629::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:30732069210515
Alex
The client would do something like:
Call procedure to open the REF CURSOR
LOOP
exit when %notfound
END LOOP
CLOSE the REF CURSOR
Whereas the database would simply have a procedure like
OPEN ref cursor for SELECT whatever from where ever
Maybe this link is more usefull:
http://asktom.oracle.com/pls/ask/f?p=4950:8:13230892179573467629::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:30732069210515
Alex
ASKER
But that will increase the number of calls to database. If looping thru thousand's of record we may not prefer this way. What are you saying?
I don't know how to do it in .net, but in an Oracle client I would retrieve records in bulk and page through the resultset as needed.
Multiple calls to the database, you're right. But on the other hand you can present the first records to the end-user fast, while you access the database to retrieve more records. If you make on call to the database, the end-user will have to wait until the last records is retrieved. The network will have to move a lot of data as well...
Think of Google, would you want to wait until Google retrieves the last of all possible links? I don't, just show me the first ten and then I'll decide if I need additional results or not.
Are you going to display the results? Then why would you want to bombard the end user with all records, and make them wait? Most likely they will page through the result set a couple of records at a time.
Are you going to process them one way or the other? Maybe move the processing over to the database, so you don't have a lot of network traffic going back and forth?
(I'm just thinking out loud...)
Alex
Multiple calls to the database, you're right. But on the other hand you can present the first records to the end-user fast, while you access the database to retrieve more records. If you make on call to the database, the end-user will have to wait until the last records is retrieved. The network will have to move a lot of data as well...
Think of Google, would you want to wait until Google retrieves the last of all possible links? I don't, just show me the first ten and then I'll decide if I need additional results or not.
Are you going to display the results? Then why would you want to bombard the end user with all records, and make them wait? Most likely they will page through the result set a couple of records at a time.
Are you going to process them one way or the other? Maybe move the processing over to the database, so you don't have a lot of network traffic going back and forth?
(I'm just thinking out loud...)
Alex
ASKER
Thanks for your suggestion Alex.
But I am not going to display the results in front end. Instead want to process further based on the resultset.
And what do you mean by
'Ma ybe move the processing over to the database, so you don't have a lot of network traffic going back and forth?' I couln't get it.
But I am not going to display the results in front end. Instead want to process further based on the resultset.
And what do you mean by
'Ma ybe move the processing over to the database, so you don't have a lot of network traffic going back and forth?' I couln't get it.
You don't mention what data provider or DB version you are using. I strongly suggest you get ODP.Net. ODP.Net has an associative array that should be able to handle a PL/SQL table.
I may have a code stub that does this. If you are able to use ODP, please post back and I'll see if I can find it.
I may have a code stub that does this. If you are able to use ODP, please post back and I'll see if I can find it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes Alex.
I'll think over again and get back to you. Thanks.
I'll think over again and get back to you. Thanks.
ASKER
slightwv: I'm using oracle 9 and Oracleclient to access it. I don't know what is ODP.Net. Can you let me know what it is?
Thanks.
Thanks.
>>Than why are you retrieving them in the first place?
Great catch!!! I missed that completely.
Oracle Dataprovider for .Net (ODP.Net) is the the only data access provider that Oracle supports for the .Net development environment. I'm far from an expert .Net developer but I'm confused on what you mean by "Oracleclient" when connecting to the database from .Net.
As a DBA, Oracle Client to me means the client software. This supports many data access methods such as, ODBC, OLEDB, JDBC, etc... ODP.Net is just another data access provider.
Great catch!!! I missed that completely.
Oracle Dataprovider for .Net (ODP.Net) is the the only data access provider that Oracle supports for the .Net development environment. I'm far from an expert .Net developer but I'm confused on what you mean by "Oracleclient" when connecting to the database from .Net.
As a DBA, Oracle Client to me means the client software. This supports many data access methods such as, ODBC, OLEDB, JDBC, etc... ODP.Net is just another data access provider.
Based on another thread I think I now know what you mean by OracleClient!!!!!
You are actually ahead of me: I never know that Microsoft had a new data provider for Oracle.
I'm not sure is this new data provider supports PL/SQL tables or not. I would bet against it. I know ODP.Net does.
You are actually ahead of me: I never know that Microsoft had a new data provider for Oracle.
I'm not sure is this new data provider supports PL/SQL tables or not. I would bet against it. I know ODP.Net does.
Rather just open the REF CURSOR in the procedure and fetch records in the application. And Close the REF CURSOR when you're done.
More info on:
http://asktom.oracle.com/pls/ask/f?p=4950:8:14361393852185075956::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4471758621321
Alex