Instead of this I did:
create a user could see both DB.
In the views of this user create one doing the things done by Cursor.
In java code you only make query over ths view.
Hope this could help you.
Bye, Giant.
Main Topics
Browse All TopicsHi Experts,
Desperately seeking answer to this question. Any help would be, needless to say, highly appreciated:
The following is the scenario we have:
1. We have a database schema dedicated to our app.
2. We have another schema (residing on another box) for the Data Warehouse.
We need to query the Data Warehouse. For this we are:
1. creating a database link from the local database schema to the Data Warehouse schema.
2. Executing the queries on the Data Warehouse using the Data base link.
When I get the result of the queries I am storing it in a Ref cursor that is the out parameter of my Stored Procedure. I read that Ref cursor in Java and write the contents to a flat file.
The issue we are facing are:
1. One of the functionality we have should allow us to select a subset of the records from the refcursor. For this, I am inserting the " and rownum < 11" condition while getting my ref cursor. I expect that this would also be returned to java immediately as I have requested only a subset of the records. However, this is not happening, the Stored Proc runs in 2 mins, however the resulset is obtained in java only after 40 mins.
My question:
1. Is the approach we are using above correct? If no, what is the better approach?
Regards,
Chet
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hello:
Seems, that problem on selecting large queries on remote server and populating them to local server over network. Only after that result set is cut by local select statement.
I think, that best way to do your task:
- Create package or stored procedures on the Data Warehouse level.
- Make them parameterized according your filter criteria and return cursors. You have to transmit only needed results over network.
- Local selects have to get records from remote procedures according criteria.
Best regards,
Siarhei Berdachuk
Hi Siarhei,
Thank you for your reply. Here is my problem in detail:
1. I cannot create packages or stored procedures at the Data Warehouse level. Also, the Data Warehouse is sitting on a different box as compared to my local database schema.
I need to return around 1 million + rows from querying the datawarehouse. I am, as I have described above, using a database link to query the datawarehouse. In addition to the 1 million + records I also have a preview mode in my application in which case only 10 records from the million need to be returned back from the stored procedure.
I am currently using a ref cursor to pass data back from the stored procedure. The query takes around 1/2 hour to run for the million records. However, in the preview mode i put in the additonal conditon:: { and rownum < 11 } , this does return back the 10 records quickly however java still takes the same time to retrieve the ref cursor i.e. 1/2 hour.
I have been exploring other options to send back the rows retrieved.
1. Return using a table type
2. Return XML
Kindly provide your opinion on which option would be best for me. It would be great if you could also give a small example. Revert back in case of any clarifications.
Thanks and Regards,
Chet
Hello Chet,
May be this solution can help you:
http://www.experts-exchang
I mean some job to retrive you query results from external database to internal temp table, and then requery from this table.
You can do same task without job with using session temporary tables and retriving external data to it in your procedure as first step. Script for creating temp table have to be look like:
create global temporary table MY_TEMP_DATA
(
MY_ID NUMBER(10) not null,
MY_NAME VARCHAR2(255)
)
on commit preserve rows;
Your result select have to be look like:
select rnum, my_id, my_name
from (select rownum as rnum, s.*
from (select my_id, my_name
from my_data
union
select my_id, my_name from
my_temp_data) s
where rownum <= :end_row)
where rnum between :first_row and :end_row
Where you retrive data page to page and calculate rows interval by formula:
first_row := (nvl(p_page_num, 1) - 1) * nvl(p_rows_on_page, 10) + 1;
end_row := least(nvl(p_page_num, 1) * nvl(p_rows_on_page, 10),
p_tot_rows);
Business Accounts
Answer for Membership
by: CEHJPosted on 2006-01-24 at 00:00:13ID: 15773745
>>One of the functionality we have should allow us to select a subset of the records from the refcursor
Why do you want to do this btw?