This discussion is archived
6 Replies Latest reply: Jan 30, 2013 3:40 AM by Sudhir_Meru RSS

Dynamic LOV Error

Sudhir_Meru Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Error during rendering of page item P60_EMPLOYEE_AVAILABILITY. ORA-20001: Query must begin with SELECT or WITH
  • 5. Re: Dynamic LOV Error
    fac586 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points