Oracle SQL Developer – Getting value from cursor only works with one filter (AND / OR) clause

I am attempting to write a query to retrieve a value from multiple databases and write it to a file. In my function I have a statement which returns results from the cursor:

cname := '(select count(unique(username)) from ' ||v_str_values(INDX)||'.APPLICATIONLOG where 
usertimestamp >= ''01-APR-21'') ';

When I try to modify the filter to add another filter, the results are Null:

cname := '(select count(unique(username)) from ' ||v_str_values(INDX)||'.APPLICATIONLOG where 
usertimestamp >= ''01-APR-21'' AND usertimestamp <= ''01-MAY-21'') ';

Why would the additional filter remove the results?

Here is what the database looks like: Screenshot

Query:

(select FUNCTION2() from DUAL);

Here is the full function:

create or replace FUNCTION FUNCTION2 
RETURN varchar2
IS 
  TYPE cur_type IS REF CURSOR;
  c_cursor cur_type;
  counter number :=0;
  cname varchar2(100) := '';
  out_str_values varchar2(6000);
  TYPE STR_LIST_TYPE IS TABLE OF VARCHAR2(6000);
  v_str_values STR_LIST_TYPE;
BEGIN
  v_str_values := STR_LIST_TYPE('ABC','DEF');
  
  FOR INDX in v_str_values.FIRST..v_str_values.LAST
  LOOP
    BEGIN
    cname := '(select count(unique(username)) from ' ||v_str_values(INDX)||'.APPLICATIONLOG where 
    usertimestamp >= ''01-APR-21'') ';
    
    OPEN c_cursor FOR cname;
    FETCH c_cursor into counter;
    out_str_values := out_str_values || v_str_values(INDX) || ',' || counter || '/';
    close c_cursor;
 
    EXCEPTION
      WHEN OTHERS THEN
      NULL;
    END;
    
  END LOOP;
  
  RETURN out_str_values;
END FUNCTION2;

Answer

While your date handling (among other things) is problematic, and should be addressed, that isn’t why you are getting null back in this case. Neither is having added a second condition, per se.

If you look at the dynamic statement you are generating in the function as you’ve shown it, with your example schema names of ABC and DEF you’ll get a query string that is 97 characters long*.

When you add the second clause, with a three-character schema name the generated query string is now 130 characters. You can see that here.

So… when you try to assign the string to the cname variable which is defined as:

cname varchar2(100)

it will get “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”. But that is happening inside your loop, which has

    EXCEPTION
      WHEN OTHERS THEN
      NULL;

so the error is suppressed, and nothing is ever appended to the out_str_values variable, so null is returned after the loop. (Suppressing exceptions, particularly with when others, is usually a bug.)

If your list of values includes any values that are longer than 6 characters then those won’t appear in the output string for the shorter query either, but that might not happen or you might not have noticed.

So the immediate fix is to change the size of cname to something large enough for any query you will generate.

_{* based on the second line of the query embedded in your code appearing to be indented by four spaces; if it is a tab then adjust the numbers…}