1 2 Previous Next 21 Replies Latest reply: May 9, 2014 2:23 PM by Mike Kutz RSS

    Select into and No data found error

    inka

      Hello All,

       

      I am using Apex 4.2 and Oracle 11g. I have the select into statement that returns no data found error:

       

      Select statement:

       

      select a.agency_name, s.action_ind, f.function_type

      into :P3_AGENCY_NAME, :P3_ACTION, :P3_FUNCTION

      from euser.v_agency a, agency_component_status s, application_teams_int i, function f

      where agency_comp_status_id = :P3_AGENCY_COMP_STATUS_ID and a.agency_id = s.agency_id and i.application_team_id = s.application_team_id and f.function_id = i.function_id;

       

      When I run this select statement in PL/SQL developer I get one record back. This select is in the After Header portion in the Processes section.

       

      What am I doing wrong?

        • 1. Re: Select into and No data found error
          TexasApexDeveloper

          Possibly the fact :P3_AGENCY_COMP_STATUS_ID DOES not have a value in session state??

           

          Thank you,

           

          Tony Miller
          LuvMuffin Software
          Ruckersville, VA

          • 2. Re: Select into and No data found error
            inka

            How do I check if it does or doesn't have a value?  It is a primary key in the table.

            • 3. Re: Select into and No data found error
              TexasApexDeveloper

              Run your page with query, click the session link in developer toolbar at bottom of page, it will show all the page items and their value in session state...  If your P3_AGENCY_COMP_STATUS_ID has a value in session state, it will appear there...

               

              Thank you,

               

              Tony Miller
              LuvMuffin Software
              Ruckersville, VA

              • 4. Re: Select into and No data found error
                inka

                It is a hidden field so when I clicked on Session it didnt show in the list of page items

                • 5. Re: Select into and No data found error
                  Mike Kutz

                  add a debug line above your SELECT INTO line

                  eg

                  apex_debug.message('YO! the value is (%s)', :P3_AGENCY_COMP_STATUS_ID);

                   

                  Remember:  null = null always returns FALSE.

                   

                  MK

                  • 6. Re: Select into and No data found error
                    inka

                    I have added a debug line. :P3_AGENCY_COMP_STATUS_ID returns blank. What does it mean?

                    • 7. Re: Select into and No data found error
                      Mike Kutz

                      It means that the value of P3_AGENCY_COMP_STATUS_ID is blank (most likely, NULL)

                       

                      Your SQL statement is matching NULL = column, which is always FALSE ==> no rows will return... ever.

                       

                      Why is it NULL?

                      You have a bug in your code that you have to hunt down and fix.

                       

                      For me, most of the time, it is due the Item name being misspelled somewhere in the app.

                      (check PL/SQL, SQL in current page and the Branching code from the prior pages.)

                       

                      .. or.. being left out entirely in the Branch.

                       

                      If you're doing a Calculation for P3_AGENCY_COMP_STATUS_ID, make sure the Calculation is being done before the Process.

                      (eg set Calculation to OnLoad BEFORE header and Process to OnLoad AFTER header)

                       

                      MK

                      • 8. Re: Select into and No data found error
                        inka

                        I changed the Fetch Row statement to this:

                         

                        for c1 in (
                        select a.agency_name, s.action_ind, f.function_type
                        from euser.v_agency a, agency_component_status s, application_teams_int i, function f
                        where agency_comp_status_id = :P3_AGENCY_COMP_STATUS_ID and a.AGENCY_ID = s.agency_id and i.application_team_id = s.application_team_id and f.function_id = i.function_id)

                        LOOP
                            :P3_AGENCY_NAME := c1.agency_name;
                            :P3_ACTION := c1.action_ind;
                            :P3_FUNCTION := c1.function_type;
                        END LOOP;

                         

                        Session state for P3_AGENCY_COMP_STATUS_ID is RESET_TO_NULL.

                         

                        What does it mean and how can I fix it?

                        • 9. Re: Select into and No data found error
                          TexasApexDeveloper

                          Where is P3_AGENCY_COMP_STATUS_ID getting a value from?? If you are trying to use it to get a value for a page item, in a select statement IT MUST HAVE A VALUE, else it's null and your select is going to return a null value...  Changing the select that you USE the P3_AGENCY_COMP_STATUS_ID item in is NOT going to solve your problem...

                           

                          By the time they had diminished from 50 to eight, the other dwarves began to suspect "Hungry".

                           

                          Thank you,

                           

                          Tony Miller
                          LuvMuffin Software
                          Ruckersville, VA

                          • 10. Re: Select into and No data found error
                            Mike Kutz

                            inka wrote:


                            where agency_comp_status_id = :P3_AGENCY_COMP_STATUS_ID

                             

                            When P3_AGENCY_COMP_STATUS_ID is NULL...

                            Your WHERE clause will always evaluate as FALSE.

                            You will always get 0 rows.

                             

                            Remember:  NULL compared to anything (even another NULL) will always evaluate as FALSE.

                             

                            The "fix" depends on the business logic.

                            When P3_AGENCY_COMP_STATUS_ID is null, what information do you want to pull back?

                            Start by giving a simple CREATE TABLE and some INSERT statements to populate it.

                            Then describe what data you want to extract out when P3_AGENCY_COMP_STATUS_ID is NULL and when it is NOT NULL.

                             

                            MK

                            • 11. Re: Select into and No data found error
                              inka

                              P3_AGENCY_COMP_STATUS_ID can't be null. It is a primary key.

                              • 12. Re: Select into and No data found error
                                Mike Kutz

                                Then why are you setting it to NULL?

                                inka wrote:

                                Session state for P3_AGENCY_COMP_STATUS_ID is RESET_TO_NULL.

                                 

                                You need to figure out why P3_AGENCY_COMP_STATUS_ID is NULL and fix that.

                                 

                                MK

                                • 13. Re: Select into and No data found error
                                  TexasApexDeveloper

                                  Lets start again.. When you run your page, HOW DOES P3_AGENCY_COMP_STATUS_ID get populated?  Do you do this with an on page load process?  Do you pass it in from another page?  Does it get set by the APEX Session State Fairy?

                                   

                                  Thank you,

                                   

                                  Tony Miller
                                  LuvMuffin Software
                                  Ruckersville, VA

                                  • 14. Re: Select into and No data found error
                                    inka

                                    I am not setting it to null. I wrote my own insert statement:

                                     

                                    declare

                                    l_application_team_id number := :P3_APPLICATION_TEAM_ID;
                                    l_agency_comp_status_id number := :P3_AGENCY_COMP_STATUS_ID;
                                       
                                    begin
                                    insert into application_teams_int (
                                                function_id,
                                    team_id,
                                    document_id,
                                    update_date,
                                    update_userid)
                                        values (
                                                :P3_FUNCTION,
                                    1025,
                                    3,
                                    :P3_UPDATE_DATE,
                                    :P3_UPDATE_USERID)
                                       returning APPLICATION_TEAM_ID into l_application_team_id;

                                      :P3_APPLICATION_TEAM_ID := l_application_team_id;

                                    insert into agency_component_status (
                                    method_type_id,
                                    application_team_id,
                                    agency_id,
                                    action_ind,
                                    update_date,
                                    update_userid)
                                        values (
                                    3,
                                    :P3_APPLICATION_TEAM_ID,
                                    :P3_AGENCY_NAME,
                                    :P3_ACTION,
                                    :P3_UPDATE_DATE,
                                    :P3_UPDATE_USERID)
                                       returning agency_comp_status_id into l_agency_comp_status_id;
                                     
                                      :P3_AGENCY_COMP_STATUS_ID := l_agency_comp_status_id; 

                                    commit;
                                    end;

                                     

                                    Is there anything wrong with it?

                                    1 2 Previous Next