11 Replies Latest reply: Jul 3, 2012 5:10 PM by damorgan RSS

    Unable to fetch value using cursor

    947242
      Hi,

      when i run the following sql, it gives me the output.
      SELECT TA_PERIOD,RECEIVED_TS,DSI_KEY_ID FROM r_vmguest_stsat,k_rp_vmguest_stsat WHERE DSI_KEY_ID = DSI_KEY_ID_ and ta_period >= (trunc(sysdate) - 1) and dsi_key_id in (14560);
      The same sql query when i use in plsql to use a cursor, it is not returning any value.
      DECLARE
      
      report_date CHAR(30);
      currdate TIMESTAMP;
      CURSOR vm_cur IS
      SELECT TA_PERIOD,RECEIVED_TS,DSI_KEY_ID FROM r_vmguest_stsat,k_rp_vmguest_stsat WHERE DSI_KEY_ID = DSI_KEY_ID_ and ta_period >= (trunc(sysdate) - 1) and dsi_key_id in (14560);
      vm_rec vm_cur%ROWTYPE;
      
      BEGIN
      
      DBMS_OUTPUT.ENABLE(1000);
      
      open vm_cur;
      
      FETCH vm_cur INTO vm_rec;
      
      DBMS_OUTPUT.PUT_LINE('Record:'||vm_rec.TA_PERIOD||vm_cur%ROWCOUNT);
      
      close vm_cur;
      
      END;
      
      /
      When running this pl sql script, it gives the following output. It shows clearly there is no data in cursor
      Record:  0
      
      PL/SQL procedure successfully completed.
      I tried lot, but could able to find the issue here. Please help me out to find why this is not fetching any data into cursor.

      Edited by: user13644817 on Jul 3, 2012 9:57 AM

      Edited by: user13644817 on Jul 3, 2012 10:12 AM
        • 1. Re: Unable to fetch value using cursor
          sybrand_b
          There is no variable dsi_key_id_ in your code. Consequently this will likely be treated as NULL.
          Personally I would use v_dsi_key_id, as that distinguishes better from the column name.

          Also you always need to post a four-digit version number (from v$version) and a platform: most issues are version dependent.

          ---------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Unable to fetch value using cursor
            sb92075
            user13644817 wrote:
            Hi,

            when i run the following sql, it gives me the output.

            SELECT DSI_KEY_ID FROM r_vmguest_stsat,k_rp_vmguest_stsat WHERE DSI_KEY_ID = DSI_KEY_ID_ and ta_period >= (trunc(sysdate) - 1) and dsi_key_id in (14560);

            The same sql query when i use in plsql to use a cursor, it is not returning any value.

            DECLARE

            report_date CHAR(30);
            currdate TIMESTAMP;
            CURSOR vm_cur IS
            SELECT TA_PERIOD,RECEIVED_TS,DSI_KEY_ID FROM r_vmguest_stsat,k_rp_vmguest_stsat WHERE DSI_KEY_ID = DSI_KEY_ID_ and ta_period >= (trunc(sysdate) - 1) and dsi_key_id in (14560);
            vm_rec vm_cur%ROWTYPE;

            BEGIN

            DBMS_OUTPUT.ENABLE(1000);

            open vm_cur;

            FETCH vm_cur INTO vm_rec;

            DBMS_OUTPUT.PUT_LINE('Record:'||vm_rec.TA_PERIOD||vm_cur%ROWCOUNT);

            close vm_cur;

            END;

            */*

            When running this pl sql script, it gives the following output. It shows clearly there is no data in cursor

            Record:  0

            PL/SQL procedure successfully completed.


            I tried lot, but could able to find the issue here. Please help me out to find why this is not fetching any data into cursor.
            I do not see any LOOP
            • 3. Re: Unable to fetch value using cursor
              947242
              Hi Sybrand,

              dsi_key_id is a column in k_rp_vmguest_stats table
              dsi_key_id_ is a column in r_vmguest_stats table

              I am using both these column to join and fetch the data. The same sql query is working fine when i am running separately. One more this this query will fetch some what huge amount of data. whether any constraint is there for the cursor on the number of rows returned by the select statement.

              SQL> select * from v$version;
              
              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
              PL/SQL Release 10.2.0.4.0 - Production
              CORE    10.2.0.4.0      Production
              TNS for Linux: Version 10.2.0.4.0 - Production
              NLSRTL Version 10.2.0.4.0 - Production
              • 4. Re: Unable to fetch value using cursor
                sb92075
                user13644817 wrote:

                One more this this query will fetch some what huge amount of data. whether any constraint is there for the cursor on the number of rows returned by the select statement.
                not as coded it won't.

                how does code know/decide when last row has been fetched?
                • 5. Re: Unable to fetch value using cursor
                  947242
                  Hi,

                  Even if i din have a loop, atleast first row returned from the select statement should be fetched. But it is showing rowcount as 0 and not fetching any values
                  • 6. Re: Unable to fetch value using cursor
                    947242
                    Then why not even a single row is fetched from the cursor?

                    any problems in using
                     ta_period >= trunc(sysdate) 
                    • 7. Re: Unable to fetch value using cursor
                      SKavi
                      how many rows are you getting with your select statement ?

                      and I don't see loop in case if you looking at printing more than one row!

                      Also try doing to_char for TA_Period in output.line

                      Edited by: SKavi on Jul 3, 2012 11:20 PM
                      • 8. Re: Unable to fetch value using cursor
                        damorgan
                        What you have here is a construct that should never have been written. There is zero value in using an explicit PL/SQL cursor and fetch or even an anonymous block.

                        Consider instead:
                        SELECT ...
                        FROM ...;
                        Also ...
                        report_date CHAR(30); is not used and should undoubtedly NOT be of data type CHAR(30) if ever it is used.
                        currdate TIMESTAMP is not used.
                        Unless you are in an antique version of Oracle DBMS_OUTPUT.ENABLE(1000); is of zero value.
                        If your fetch ever returns two or more rows your code will fail. Which means that if the value here:
                        DBMS_OUTPUT.PUT_LINE('Record:'||vm_rec.TA_PERIOD||vm_cur%ROWCOUNT);
                        isn't one you will never see this output.
                        Your tables and columns are not aliased so no one will ever know which columns come from which tables without doing extra work: And that includes the CBO.
                        The dsi_key_id should be a bind variable ... not hard coded.
                        • 9. Re: Unable to fetch value using cursor
                          Ashish_Apps
                          try something like
                          for i IN CURSOR.ROWCOUNT LOOP
                            dbms_output.put_line('Current index is : '|| i);
                          end loop;
                          Edited by: 936671 on Jul 3, 2012 11:15 AM
                          • 10. Re: Unable to fetch value using cursor
                            Braam
                            Hi try this
                            DECLARE
                             
                            report_date CHAR(30);
                            currdate TIMESTAMP;
                            CURSOR vm_cur IS
                            SELECT TA_PERIOD,RECEIVED_TS,DSI_KEY_ID FROM r_vmguest_stsat,k_rp_vmguest_stsat WHERE DSI_KEY_ID = DSI_KEY_ID_ and ta_period >= (trunc(sysdate) - 1) and dsi_key_id in (14560);
                            vm_rec vm_cur%ROWTYPE;
                             
                            BEGIN
                             
                            DBMS_OUTPUT.ENABLE(1000);
                             
                            open vm_cur;
                            loop 
                            FETCH vm_cur INTO vm_rec;
                            
                            exit when  vm_cur%notfound;
                            DBMS_OUTPUT.PUT_LINE('Record:'||vm_rec.TA_PERIOD||vm_cur%ROWCOUNT);
                            end loop; 
                            close vm_cur;
                             
                            END;
                            Not tested.
                            • 11. Re: Unable to fetch value using cursor
                              damorgan
                              And the advantage over simply writing SELECT ... FROM ... is what?

                              That you are wasting more resources and running more slowly?

                              Throw away the PL/SQL.