3 Replies Latest reply: Aug 28, 2013 9:47 AM by BluShadow RSS

    Select on Cursor in oracle 10g


      Hi Experts ,


      I have a cursor which selects the data from another source.


      Now I am having a problem with this cursor because i cannot put any where condition as it takes huge time to retrive the data (data's are retrived from views)


      The "select *  from Source" works fine as it takes less  time.


      Now how can I filter the cursor  (select *  from Source ) and load the data into table ?


      can I use "select col from curosr where  cond= 'something' ?"

        • 1. Re: Select on Cursor in oracle 10g

          It's not clear what you're trying to do as you haven't posted any code for us to look at.


          Please read:Re: 2. How do I ask a question on the forums?


          And briefly... no you cannot select * from cursors.  A cursor is already a parsed select statement just waiting for the data to be fetched, so it cannot be embedded as a sub-query inside a new query.

          • 2. Re: Select on Cursor in oracle 10g

            Sorry for the inconvience!!!!


            What I am try to say is.


            I have a cursor just like below


            CURSOR TEMP IS

                  SELECT col1, col2

                    FROM T1@source1_v


            In this cursor I am not able to put any filter as its retriveing huge data from different source and taking lot of time to retieve it.


            If I select without filter, data are retirved faster.


            Now after getting the complete data into cursor how can I filter the data and load into local table?

            • 3. Re: Select on Cursor in oracle 10g

              You don't understand what a cursor is.


              You do not 'get data into a cursor'.  A cursor is not a result set of data.  A cursor is just a pointer to a query that data is fetched through.

              What you have shown there is a PL/SQL cursor declaration.  At that point is has not done anything except declare the definition of the cursor.  It hasn't even been opened or executed against the database, let alone any data fetched.


              How are you measuring that the query is 'fast' when you query it without conditions compared to 'slow' when you query it with conditions.


              It's possible that you don't have the correct indexes for your query filtering conditions etc. but as we don't have your database version, tables, data etc. we cannot see to help.


              I see you obviously didn't read the first FAQ about how to post your information.

              Maybe you'll bother to read this FAQ instead:  Re: 3. How to improve the performance of my query? / My query is running slow.


              which links to two threads describing what information you need to provide for tuning help.


              I can't see how you would improve performance by trying to filter your data after querying more than you need, so I think you're looking for a solution before you even know what the problem is.