6 Replies Latest reply: Jan 30, 2013 5:40 AM by Sudhir_Meru RSS

    Dynamic LOV Error

    Sudhir_Meru
      Hi,

      I am adding some condition to return the query dynamically but oracle apex is not allowing. Please suggest me what is the error in this query and how to use the function returning query in list of values


      --BEGIN
      --
      -- IF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN
      --
      -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
      -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ;
      --
      -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN
      --
      -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
      -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ;
      --          
      -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN
      --
      -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
      -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID != 112 AND
      -- MASTER_ID IN (106,108,109,110,103) ';
      --
      -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN
      --
      -- RETURN
      -- 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
      -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID NOT IN (106,108,109,110,103,112,97)
      -- UNION
      -- SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
      -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 97 AND
      -- 1 =
      -- (
      -- SELECT DISTINCT 1 FROM TECH_SOURCING_EMPLOYEE_DETAILS WHERE EMPLOYEE_AVAILABILITY = 97
      -- AND PROSPECT_EMP_ID IN ( SELECT PROSPECT_EMP_ID FROM TECH_SOURCING_CONTRACT_ADMIN WHERE CONTRACT_STATUS = 8 AND PROSPECT_EMP_ID = --:P60_PROSPECT_EMP_ID )
      -- )';

      -- END IF;

      --END;

      When i run the page it returns

      Error Error during rendering of page item P60_EMPLOYEE_AVAILABILITY.
      ORA-20001: Query must begin with SELECT or WITH
      Technical Info (only visible for developers)
      is_internal_error: true
      apex_error_code: WWV_FLOW_FORM.UNHANDLED_ERROR
      ora_sqlcode: -20001
      ora_sqlerrm: ORA-20001: Query must begin with SELECT or WITH
      component.type: APEX_APPLICATION_PAGE_ITEMS
      component.id: 6923348653418778
      component.name: P60_EMPLOYEE_AVAILABILITY
      error_backtrace:
      ORA-06512: at "APEX_040100.WWV_FLOW_ASSERT", line 289
      ORA-06512: at "APEX_040100.WWV_FLOW_META_UTIL", line 229
      ORA-06512: at "APEX_040100.WWV_FLOW_FORMS", line 763



      Need apex experts to suggest me what is the mistake am doing here.

      Thanks
      Sudhri
        • 1. Re: Dynamic LOV Error
          fac586
          Sudhir_Meru wrote:
          Hi,

          I am adding some condition to return the query dynamically but oracle apex is not allowing. Please suggest me what is the error in this query and how to use the function returning query in list of values


          --BEGIN
          --
          -- IF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN
          --
          -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
          -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ;
          --
          -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN
          --
          -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
          -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ;
          --          
          -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN
          --
          -- RETURN 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
          -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID != 112 AND
          -- MASTER_ID IN (106,108,109,110,103) ';
          --
          -- ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN
          --
          -- RETURN
          -- 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
          -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID NOT IN (106,108,109,110,103,112,97)
          -- UNION
          -- SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE
          -- WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 97 AND
          -- 1 =
          -- (
          -- SELECT DISTINCT 1 FROM TECH_SOURCING_EMPLOYEE_DETAILS WHERE EMPLOYEE_AVAILABILITY = 97
          -- AND PROSPECT_EMP_ID IN ( SELECT PROSPECT_EMP_ID FROM TECH_SOURCING_CONTRACT_ADMIN WHERE CONTRACT_STATUS = 8 AND PROSPECT_EMP_ID = --:P60_PROSPECT_EMP_ID )
          -- )';

          -- END IF;

          --END;

          When i run the page it returns

          Error Error during rendering of page item P60_EMPLOYEE_AVAILABILITY.
          ORA-20001: Query must begin with SELECT or WITH
          Technical Info (only visible for developers)
          is_internal_error: true
          apex_error_code: WWV_FLOW_FORM.UNHANDLED_ERROR
          ora_sqlcode: -20001
          ora_sqlerrm: ORA-20001: Query must begin with SELECT or WITH
          component.type: APEX_APPLICATION_PAGE_ITEMS
          component.id: 6923348653418778
          component.name: P60_EMPLOYEE_AVAILABILITY
          error_backtrace:
          ORA-06512: at "APEX_040100.WWV_FLOW_ASSERT", line 289
          ORA-06512: at "APEX_040100.WWV_FLOW_META_UTIL", line 229
          ORA-06512: at "APEX_040100.WWV_FLOW_FORMS", line 763



          Need apex experts to suggest me what is the mistake am doing here.
          Here? Posting code without using <tt>\
          ...\
          </tt> tags. Not specifying the APEX or database versions.

          In the LOV definition? Apparently commenting out the entire block. Why is there a "<tt>--</tt>" at the start of every line?

          This is suspicious:
          ...AND PROSPECT_EMP_ID = --:P60_PROSPECT_EMP_ID )
          That will result in invalid SQL being returned.

          Two things that are regarded as best practice:

          <li>Main function blocks should have only one <tt>return</tt> statement. In multi-branched logic, assign the return value to a variable and return the variable at the end of the function. (Additional <tt>return</tt>s can be used in exception handlers if necessary.)
          <li>When using dynamic queries, always log the generated SQL so it can be inspected in the event of an error. It is simple to do this in APEX using the built-in debug features. (In recent versions, APEX will do this automatically for "function returning SQL query" data sources.)
          DECLARE
          
            lov_sql varchar2(4000);
            
          BEGIN
          
            IF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN 
            
             lov_sql := 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE 
            WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ;
            
            ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (38,39) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN 
            
             lov_sql := 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE 
            WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 112' ; 
            
            ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 105 THEN 
            
             lov_sql := 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE 
            WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID != 112 AND
            MASTER_ID IN (106,108,109,110,103) '; 
            
            ELSIF :P60_TYPE_OF_SERVICE_NEEDED IN (113,36,37) AND :P60_EMPLOYEEMENT_TYPE = 104 THEN
            
             lov_sql := 'SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE 
            WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID NOT IN (106,108,109,110,103,112,97)
            UNION
            SELECT CODE D , MASTER_ID R FROM TECH_SOURCING_MASTER_CODE 
            WHERE CODE_NAME = ''EMPLOYEE_STATUS'' AND MASTER_ID = 97 AND 
            1 = 
            (
            SELECT DISTINCT 1 FROM TECH_SOURCING_EMPLOYEE_DETAILS WHERE EMPLOYEE_AVAILABILITY = 97
            AND PROSPECT_EMP_ID IN ( SELECT PROSPECT_EMP_ID FROM TECH_SOURCING_CONTRACT_ADMIN WHERE CONTRACT_STATUS = 8 AND PROSPECT_EMP_ID = :P60_PROSPECT_EMP_ID )
            )'; 
            
            END IF;
          
            wwv_flow.debug('lov_sql = ' || lov_sql);
            
            RETURN lov_sql;
            
          END;
          Edited by: fac586 on 29-Jan-2013 13:01
          • 2. Re: Dynamic LOV Error
            Sudhir_Meru
            Hi,

            Thanks for your reply, I am using oracle apex 4 and 10g XE as database.

            I tried using your method still getting same error message.

            Error Error during rendering of page item P60_EMPLOYEE_AVAILABILITY.
            ORA-20001: Query must begin with SELECT or WITH

            Please suggest is there any alternative way to modify.

            Thanks
            Sudhir
            • 3. Re: Dynamic LOV Error
              fac586
              Sudhir_Meru wrote:
              Hi,

              Thanks for your reply, I am using oracle apex 4 and 10g XE as database.

              I tried using your method still getting same error message.

              Error Error during rendering of page item P60_EMPLOYEE_AVAILABILITY.
              ORA-20001: Query must begin with SELECT or WITH
              What is shown as the generated LOV SQL in the debug trace?
              • 4. Re: Dynamic LOV Error
                Sudhir_Meru
                Error during rendering of page item P60_EMPLOYEE_AVAILABILITY. ORA-20001: Query must begin with SELECT or WITH
                • 5. Re: Dynamic LOV Error
                  fac586
                  Sudhir_Meru wrote:
                  Error during rendering of page item P60_EMPLOYEE_AVAILABILITY. ORA-20001: Query must begin with SELECT or WITH
                  Did you include a debug message to log the generated query?
                  ...
                  wwv_flow.debug('lov_sql = ' || lov_sql);
                  ...
                  What does this show in the debug trace?

                  Is it possible that none of the logic in the IF statements is incomplete? None of the predicates evaluates to true and the LOV function is therefore returning NULL? Check the values used in the IF predicates in session state. Should a default LOV query be included in an ELSE to be used if none of the predicates are true?
                  • 6. Re: Dynamic LOV Error
                    Sudhir_Meru
                    Thanks for the suggestion it worked now after adding else part in the block also added wwv_flow.debug to log the error message


                    Thanks you so much appreciated your support


                    Thanks
                    Sudhir