CODEWITHSUNDEEP
oracle-databaseplsql
Sid
Sid

Reputation: 341

PLSQL : varchar variable becomes null without any reason

trying to migrate some pgpl/SQL to psql oracle function, i discover a strange behaviour i cant explain at all.

Here is my function :

CREATE OR REPLACE FUNCTION small_distinct(
   tableName IN varchar, 
   cols IN COL_ARRAY,     
   whereCond IN varchar
)
RETURN varchar 
IS
  nbcols PLS_INTEGER;
  currentcolnumber PLS_INTEGER;
  currentcol varchar(100);
  firstcol varchar(100);
  sqlpart varchar(4000);
  firstquery_str VARCHAR2(4000);
  query_str VARCHAR2(4000);
  big_query_str VARCHAR2(10000);
  firstresult varchar(100);
  opt SYS_REFCURSOR;
BEGIN
  firstcol:= cols(1);
  sqlpart := firstcol;
  nbcols := cols.COUNT;
  currentcolnumber :=  2;
  big_query_str := 'SELECT * from ( SELECT '|| sqlpart ||' FROM '||tableName||' WHERE ' ||    whereCond|| ' ORDER BY '|| firstcol || ') WHERE ROWNUM = 1';

  while currentcolnumber <= nbcols loop
     sqlpart := sqlpart || ', ' ||cols(currentcolnumber);
     currentcolnumber := currentcolnumber +1;
  end loop;

  EXECUTE IMMEDIATE 'SELECT * from (SELECT '|| firstcol ||' FROM '||tableName ||' WHERE ' || whereCond|| ' ORDER BY '|| firstcol || ') WHERE ROWNUM = 1 ' INTO firstresult;

  while firstresult is not null 
  loop
    big_query_str := big_query_str || ' UNION ' || 'SELECT * from ( SELECT '|| sqlpart ||' FROM '||tableName||' WHERE '|| firstcol ||' > '''|| firstresult||''' AND ' || whereCond|| ' ORDER BY '|| firstcol || ') WHERE ROWNUM  = 1  ';
    EXECUTE IMMEDIATE 'SELECT * from ( SELECT ' || firstcol ||' FROM '||tableName ||' WHERE '|| firstcol ||' > '''|| firstresult||''' AND '|| whereCond|| ' ORDER BY '|| firstcol || ') WHERE ROWNUM = 1' INTO firstresult;
  end loop;

  return big_query_str;
END small_distinct;

This function compile well, but my varchar big_query_str is null when the function return.

As you can see in my function, there is no possibility this varchar can be null since i initialize it with a value at the line 18

I tried to remove so lines to discover what the problem could be, the return value is not null only if i remove the while loop ( around line 24 )

Do someone at least explain me what sort of event can reset a varchar in PL/SQL ?

Upvotes: 1

Views: 782

Answers (3)

steve godfrey
steve godfrey

Reputation: 1234

Try adding an exception handler round the firstresult line.

BEGIN EXECUTE IMMEDIATE 'SELECT * from ( SELECT ' || firstcol ||' FROM '||tableName ||' WHERE '|| firstcol ||' > '''|| firstresult||''' AND '|| whereCond|| ' ORDER BY '|| firstcol || ') WHERE ROWNUM = 1' INTO firstresult; EXCEPTION WHEN no_data_found THEN EXIT; END;

Looks to me like it will keep going to the last record, then get a no_data_found as there aren't any more records where "firstcol > firstresult".

Upvotes: 1

valex
valex

Reputation: 24144

Try to include this loop into the BEGIN/EXCEPTION/END. Is it possible that in this loop error occurs and return big_query_str is not executed?

BEGIN
  while firstresult is not null 
  loop
    big_query_str := big_query_str ||........
    EXECUTE IMMEDIATE....
  end loop;
  return big_query_str;

EXCEPTION
   WHEN OTHERS THEN 
     return 'ERROR';      
END;

Upvotes: 1

Max
Max

Reputation: 4077

I am not sure about this but your big_query_str is defined as a varchar2 of 10,000 length. The maximum allowable length of varchar2 when returning to SQL is 4000 characters. So, perhaps, that is an issue? Can you try defining big_query_str to a CLOB type and then returning it from the function (after making necessary changes to function)

Upvotes: 0

Related Questions