title: Execute immediate in user defined function - called in inner query
body: <p>I am using a stored procedure to fetch records from an oracle database. This procedure returns paginated records building dynamic SQL query based on search inputs provided. Output 'STATUS' column is derived from user defined function 'GET_STATUS_FOR_ME'. Some derived columns are passed to this function based on this actual status of each record is calculated.</p>
  
  <p>Everything works fine while not providing status as input search criteria. when status is provided it gives me the <strong>following error</strong>.</p>
  
  <pre><code>    ORA-06535: statement string in EXECUTE IMMEDIATE is NULL or 0 length
  ORA-06512: at "pkg_search", line 15
  06535. 00000 -  "statement string in %s is NULL or 0 length"
  *Cause:    The program attempted to use a dynamic statement string that
             was either NULL or 0 length.
  *Action:   Check the program logic and ensure that the dynamic statement
             string is properly initialized.
  </code></pre>
  
  <p>My user defined function is as below:</p>
  
  <pre><code>FUNCTION GET_STATUS_FOR_ME(
      RECORDID     IN NUMBER,
      ASSIGNEDTO   IN NUMBER,
      LOCATIONID   IN NUMBER,
      TEMPUSERID   IN NUMBER,
      ACTUALSTATUS IN VARCHAR2)
    RETURN VARCHAR2
  AS
    O_STATUS   VARCHAR2(200 BYTE);
    TEMP_QUERY VARCHAR2(200 BYTE);
    I_COUNT    NUMBER;
  BEGIN
    IF LOCATIONID IS NOT NULL AND TEMPUSERID IS NULL THEN
      TEMP_QUERY  := 'SELECT COUNT(*) FROM MY_TABLE WHERE COUNTRYIF = ' || TO_CHAR(LOCATIONID);
      EXECUTE IMMEDIATE TEMP_QUERY INTO I_COUNT;
    END IF;
  
    . . . Other FUNCTION logic here. . .
  
      RETURN O_STATUS;
    END GET_STATUS_FOR_ME;
  </code></pre>
  
  <p>My procedure from which this function is being called is as below. This function is being called from inner query.</p>
  
  <pre><code>  PROCEDURE SEARCH_RESULT_PROC(
        I_LOGGEDINUSERID  IN NUMBER,
        I_CREATEDFROMDATE IN DATE,
        I_CREATEDTODATE   IN DATE,
        I_STATUS          IN VARCHAR2,
        I_COUNTRYID       IN NUMBER,
        I_LANGUAGEID      IN NUMBER,
        I_OFFSET          IN NUMBER,
        I_LIMIT           IN NUMBER,
        I_ORDRBY          IN VARCHAR2,
        I_SORTBY          IN VARCHAR2,
        O_COUNT OUT NUMBER,
        REF_CUS_RESULTS OUT SYS_REFCURSOR)
    AS
      PAG_END_ROW           NUMBER;
      STC_SQL_PART          VARCHAR2(9999 BYTE);
      DYNMC_SQL_CLAUSE_PART VARCHAR2(9999 BYTE);
    BEGIN
      PAG_END_ROW  := I_OFFSET + I_LIMIT - 1;
      STC_SQL_PART := 'select ID, REOCRDSTATUS, 
  (CASE          
  some logic here       
  END) LOCATIONID, 
  (CASE          
  some logic here       
  END) USERID from MY_TABLE MTABLE';
  
      . . Other Logic TO build dynamic WHERE clause depending ON inputs provided. . .
  
      IF I_DOCSTATUS          IS NOT NULL THEN
        DYNMC_SQL_CLAUSE_PART := DYNMC_SQL_CLAUSE_PART || ' AND UPPER(TEMPDATA.STATUS)  = UPPER(''' || I_STATUS || ''')';
      END IF;
      FINAL_QUERY := 'SELECT ODATA.EID,  
  ODATA.TITLE,  
  ODATA.TYPE,   
  pkg_search.GET_STATUS_FOR_ME(ID,' || I_LOGGEDINUSERID || ',ODATA.LOCATIONID,ODATA.USERID,ODATA.REOCRDSTATUS) STATUS
  FROM (SELECT ROWNUM RNUM , TEMP.* FROM ( ' || STC_SQL_PART || DYNMC_SQL_CLAUSE_PART || ' )TEMP WHERE ROWNUM &lt;= ' || TO_CHAR(PAG_END_ROW) ||' ) ODATA WHERE ODATA.RNUM &gt;= '|| TO_CHAR( I_OFFSET) ;
      OPEN REF_CUS_RESULTS FOR FINAL_QUERY;
    END SEARCH_RESULT_PROC;
  </code></pre>